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()
?MyRightRecordCount()
|