ACC2000: RecordCount Property Returns Incorrect Number of Records

• Microsoft Access 2000

SYMPTOMS

The RecordCount property, when used with a recordset or snapshot, returns a recordset that has an incorrect number of records.

CAUSE

For recordsets and snapshots, Microsoft Access does not automatically return the number of records that exist in the recordset. Rather, it returns the number of records accessed.

RESOLUTION

To determine the exact number of records in a recordset or snapshot, use the MoveLast method before checking the RecordCount property.

MORE INFORMATION

The following Visual Basic function, MyWrongRecordCount(), returns the number 1 for the Customers table in the sample database Northwind.mdb because only one record has been accessed. The MyRightRecordCount() function uses the MoveLast method first to access all records in the recordset and then to return the RecordCount value.

The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.



2. Create a module and type the following line in the Declarations section if it is not already there:




Option Explicit 

3. Type the following procedures:




'===========================================================
' The following function, MyWrongRecordCount(), demonstrates
' the incorrect way to use the RecordCount property to count
' records in a dynaset.
'===========================================================

Function MyWrongRecordCount ()
   Dim MyDB As DAO.Database
   Dim MyRS as DAO.Recordset
   Set MyDB = CurrentDB()
   Set MyRS = MyDb.OpenRecordset("Customers", dbOpenDynaset)
   MyWrongRecordCount = MyRS.RecordCount
   MyRS.Close
End Function

'===========================================================
' The following function, MyRightRecordCount(), demonstrates
' the correct way to use the RecordCount property to count
' records in a dynaset.
'===========================================================
Function MyRightRecordCount ()
   Dim MyDB As DAO.Database
   Dim MyRS as DAO.Recordset
   Set MyDB = CurrentDB()
   Set MyRS = MyDb.OpenRecordset("Customers", dbOpenDynaset)
   MyRS.MoveLast
   MyRightRecordCount = MyRS.RecordCount
   MyRS.Close
End Function 

4. To test these functions, type the following lines in the Immediate Window, and then press ENTER after you've entered each one:

   
   ?MyWrongRecordCount() 

   Note that the function returns 1.

   
   ?MyRightRecordCount() 

   Note that the function returns the correct number of records in the Customers table.

REFERENCES

For more information about the RecordCount property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type RecordCount property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: record count move last

Keywords : kbprg kbdta AccCon PgmObj KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb