VBA Access SQL SELECT Query only returning one record

Question

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.

Ben Clark-Robinson · Accepted Answer · 2012-08-12 05:08:51Z

4

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.

answered Aug 12, 2012 at 5:08

Ben Clark-Robinson

1,44917 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Big Tam Over a year ago

Thank you! Adding the rst.MoveLast fixed the problem!

SeanC · Accepted Answer · 2012-08-13 18:11:22Z

2

Another method is DCount.

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

no playing around with recordsets until you really need to

answered Aug 13, 2012 at 18:11

SeanC

16k5 gold badges48 silver badges67 bronze badges

Collectives™ on Stack Overflow

VBA Access SQL SELECT Query only returning one record

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related