Missing the obvious

[PerkinsSlave]

Good morning,

I am attempting to open a recordset in VBA. I have used the same procedure countless times in the past, but it doesn't seem to be working today, and it's driving me nuts! Here's the code:



Public Sub Test1()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim iCount As Integer

sSQL = "SELECT * FROM tblCSAInfo"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sSQL)

iCount = rs.RecordCount
MsgBox "There are " & iCount & " records."

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

The table in question, tblCSAInfo, has 368 records. The code listed above returns a value of 1 to iCount. Now, if I replace the sSQL statement with just "tblCSAInfo" I receive an accurate count.

I know I'm missing something obvious, I just can't seem to figure out what it is. Adding to the frustration is that this method works in other modules.

Thanks,
Paul

 

[PerkinsSlave]

Something's got to be goofy. I just wrote a quick query called "qryCSAInfo" and set it to retrieve all records. The query works fine.

Then, in my code, I dimmed "qd" as "DAO.QueryDef" and changed my code to this:

Set db = CurrentDb()
Set qd = db.QueryDefs("qryCSAInfo")
Set rs = qd.OpenRecordset

Again, the iCount value became 1.

Could there be some kind of SQL/VBA conflict?

 

[PerkinsSlave]

Ok, here's what I inserted prior to the integer value assignment:

If rs.REcordCount > 0 Then
rs.movelast
rs.movefirst
End If

Definitely a lesson learned. I did not realize Access had to move THROUGH the recordset to count it. Hopefully this will help someone else, too!