You may want to know the number of records in a Recordset object. For example, you may want to create a form that shows how many records are in each of the tables in a database. Or you may want to change the appearance of a form or report based on the number of records it includes.
The RecordCount property for a table-type Recordset object contains the number of records in the table. The RecordCount property for any other type of Recordset object contains the number of records that have been actually been visited. A Recordset object with no records has a RecordCount property value of 0.
The RecordCount property value equals the number of records actually visited. For example, when you first create a non-table-type Recordset object, you have accessed (or “visited”) only the first record. If you check the RecordCount property immediately after creating this Recordset object (assuming it has at least one record), the value is 1. To visit all the records, use the MoveLast method immediately after opening the Recordset object, then use MoveFirst to return to the first record. This is not done automatically, because it may be slow, especially for large result sets. Opening a table-type Recordset object effectively visits all of the records in the underlying table, and RecordCount totals the number of records in the table as soon as the Recordset object is opened. Aborted transactions may make the RecordCount property value out of date in some multiuser situations. Compacting the database restores the table’s record count to the correct value.
The following example creates a dynaset-type Recordset object (because it’s based on an SQL statement) from the Employees table, and then determines the number of records in the Recordset object. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("SELECT LastName, " _
& "FirstName FROM Employees")
With rst
.MoveLast
MsgBox "There are " & .RecordCount & " records in this recordset."
End With
As records in a dynaset-type Recordset object are deleted by your application, the value of the RecordCount property decreases. However, in a multiuser environment, records deleted by other users aren’t reflected in the value of the RecordCount property until the current record position is a deleted record. At that time, the setting of the RecordCount property decreases by 1. Using the Requery method on a Recordset object, followed by the MoveLast method, sets the RecordCount property to the current total number of records in the Recordset object.
A snapshot-type or forward-only-type Recordset object is static and its RecordCount property value doesn’t change when other users add or delete records in the underlying table.