CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Big Tam
Big Tam

Reputation: 23

VBA Access SQL SELECT Query only returning one record

I'm working with VBA in Access 2010 and I have an odd problem. I'm trying to pull records from a table, but my SELECT query is only returning a single record.

There are three records in the table, but the recordset is only getting the first one.

Here's my code.

Dim cc As String 
Dim DB As Database 
Dim rst As recordset 
Dim sqlstr As String 
Dim e As Integer 

cc = CmbClass.Text
If cc = "" Then Exit Sub 

sqlstr = "SELECT * FROM Students" 'WHERE CCode ='" & cc & "'"

Set DB = CurrentDb 
Set rst = DB.OpenRecordset(sqlstr) 
'Debug.Print rst.Fields(0)
e = rst.RecordCount 
Debug.Print e 
If e = 0 Then Exit Sub

The value of e is continually 1, not 3. As you can see I originally had a more complex SQL string, but I've cut it down to the most basic while trying to troubleshoot, yet the problem persists. Does anyone know why this is happening?

Thanks,

Tam.

Upvotes: 2

Views: 29003

Answers (2)

SeanC
SeanC

Reputation: 15923

Another method is DCount.

e = DCount("*","Students","CCode ='" & cc & "'")

no playing around with recordsets until you really need to

Upvotes: 2

Ben Clark-Robinson
Ben Clark-Robinson

Reputation: 1459

From memory you need to issue a rst.MoveLast before you can reliably get the record count like this:

sqlstr = "SELECT * FROM Students" 'WHERE CCode ='" & cc & "'"

Set DB = CurrentDb 
Set rst = DB.OpenRecordset(sqlstr) 
rst.MoveLast
e = rst.RecordCount

Also you might want to consider using SELECT COUNT(*) FROM Students and reading the value from the recordset instead of moving through the records to get the count. Using this query should be more efficient.

Upvotes: 4

Related Questions