RecordSet confusion

[David Anderson]

Shown below is a simplified version of some code I have attached to an Access
2000 form. The purpose of the code is to create a recordset for subsequent
manipulation. My problem is that the code always creates a recordset with
only one record, even though visual inspection of the underlying table
clearly shows that there should be 32 records.

Dim MyDB As DAO.Database
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Dim strSQL As String
Dim rst As DAO.Recordset

'Assemble SQL statement for use in Set command.
strSQL = "SELECT Distinctions.EntrantID, Distinctions.DistinctionID "
strSQL = strSQL & "FROM Distinctions "
strSQL = strSQL & "WHERE (Distinctions.EntrantID = 368)"

Set rst = MyDB.OpenRecordset(strSQL)

MsgBox "RecordCount = " & rst.RecordCount

To add to my confusion, if I place a breakpoint just after the Set command
and then paste the current value of the strSQL string into a new Query, then
the result correctly shows all 32 records.

Can anyone please explain where I am going wrong?

David

 

[Allen Browne]

Immediately after opening the recordset, Access has not loaded all the
records into memory yet, so the RecordCount property returns just 1 (if
there are any) or 0 (if there are none.)

To force Access to load all the records and give you the true count, move to
the last record. Details and example in:
Traps: Working with Recordsets - 10 common mistakes
at:
http://allenbrowne.com/ser-29.html#RecordCount_without_MoveLast

 

[Ofer Cohen]

You need to move to the last record

Set rst = MyDB.OpenRecordset(strSQL)
rst.MoveLast
MsgBox "RecordCount = " & rst.RecordCount
rst.MoveFirst

 

[David Anderson]

Thanks guys. Problem solved. I've a feeling that I used to know about the
MoveLast requirement, but old age is affecting the memory...

David