Microsoft KB Archive/207652

= ACC2000: RecordCount Property Returns Incorrect Number of Records =

Article ID: 207652

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207652



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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.

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
 Open the sample database Northwind.mdb.  Create a module and type the following line in the Declarations section if it is not already there: Option Explicit   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   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. 

<div class="references_section">