ACC2000: RecordCount Property Returns Incorrect Number of Records

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.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

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.