Record Counts

How can I get the record count of a recordset?

Often times, you want to know something about the number of records you are dealing with. Usually, you either want to know if there are any records at all, or you want to know the actual number of records.

There is a general method in VBA, using the RecordCount property of the DAO.RecordSet¹ object that you can use for any recordset. This would include tables, and bound forms or subforms.

Before we get into the examples, you need to understand how RecordCount works. It does not tell you the number of records in a recordset. Rather, it tells you the count of records accessed in the Recordset. They may or may not be the same thing.

To make sure that they are the same thing, you need to access all the records. The easiest way to do this is to use MoveLast to go to the last record. After doing that, RecordCount will reflect the actual number of records in the Recordset.

One more related point: a MoveLast can be expensive, and you only need it if you care about the actual number of records. If you only want to know if a RecordSet has records, check the EOF property instead.

On to the examples. We will examine tables first; since we are going to be looking at Recordsets, though, all samples will be similar, so the first will get the most detailed consideration.

A TABLE

Consider this code example:

Code:

Public Function Table_RecordCount(sTable As String) As Long
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset(sTable)
    
    If Not (rs.EOF) Then
        rs.MoveLast
        Table_RecordCount = rs.RecordCount
    End If
    
    Set rs = Nothing
End Function

?Table_RecordCount("tblEmpty")
0
?Table_RecordCount("tblHasRecords")
6

A couple of points of interest here:
1. Note that we check EOF before attempting a MoveLast. Without this, we would get an error on an empty recordset.
2. What if we checked RecordCount, without the MoveLast? Well, as an example, on a local table with 6 records,

I get 6 (correct). On a linked table with 7832 records, I get 1 (incorrect). Now, you should always get back a non-zero result for RecordCount on a recordset that has records, but as you can see, without the MoveLast you can't be sure you are getting the actual count of records.

Let's expand on that second point just a bit. Sometimes you will see (or want to try) something like this:

Code:

    If (rs.RecordCount > 0) Then
        Debug.Print "Has Records"
    Else
        Debug.Print "No Records"
    End If

This will not always give the expected results. There are circumstances where RecordCount returns -1 when there are records². Again, if you want to check if there are records, use EOF. The only thing you can be sure about when checking RecordCount without performing a MoveLast first is that it will be non-zero if there are records, and 0 if there are no records.

There are other methods of getting a record count for tables, but they are SQL, and do not use the Recordset object, so we do not cover them here.

A FORM

Now that you know how to obtain the record count for a Recordset, you just need to be able to get the Recordset you want.

For a form, it's quite simple:

Code:

Private Sub cmdShowRecordCount_Click()
    Dim rs           As DAO.Recordset
    Dim lRecordCount As Long

    Set rs = Me.RecordsetClone
    
    If Not (rs.EOF) Then	
       rs.MoveLast
    End If	

    lRecordCount = rs.RecordCount

    MsgBox lRecordCount & " record(s)"
    
    Set rs = Nothing
End Sub

Note that we are using the form's RecordsetClone property. We don't want to do a MoveLast on the form's actual RecordSet (since that would reposition us in the form to the last record), so we instead use the RecordsetClone.

However, if we are just checking if there are records, we can use the form Recordset, since we aren't going to move anywhere:

Code:

Private Sub Form_Open(Cancel As Integer)
    If (Me.Recordset.EOF) Then
        MsgBox "No records in the form"
    End If
End Sub

You could use RecordsetClone in the above if you liked.

For forms, the main thing to keep in mind is the difference between Recordset and RecordsetClone.

A SUBFORM

Subforms trip a lot of people up, and one reason is because you need to understand how the reference them (see: How do I reference a form? if you want to read more about this.)

To get the record count of a subform's Recordset, you need to refer to the Form property of the subform control.

Let's look at a example that shows a MsgBox with the main form and subform record counts:

Code:

Private Sub cmdRecordCount_Click()
    Dim rs              As DAO.Recordset
    Dim sMsg            As String
    Dim lRecordCount    As Long
    
    ' Main form
    Set rs = Me.RecordsetClone
    If Not (rs.EOF) Then
        rs.MoveLast
    End If

    lRecordCount = rs.RecordCount

    sMsg = "Main form: " & lRecordCount & " record(s)"
    
    ' Subform
    Set rs = Me.fsub.Form.RecordsetClone
    If Not (rs.EOF) Then
        rs.MoveLast
    End If

    lRecordCount = rs.RecordCount
    sMsg = sMsg & vbCrLf & "Sub form: " & lRecordCount & " records(s)"
    
    MsgBox sMsg
    
    Set rs = Nothing
End Sub

In summary:
1. If you want to know if there are records, you can use Recordset and check EOF.
2. If you want to know how many records there are, use RecordsetClone, MoveLast, and then RecordCount.

A note of possible interest:
When first opening a record set you can also check BOF to determine if it contains records. That is, if a Recordset contains no records, both EOF and BOF will be true.

The reason I prefer to check EOF, is because you can do loops like:

Code:

    Do While Not (rs.EOF)
        ' Do Something for each record
    Loop

This combines the checking for an empty Recordset and the looping structure. You can't do the same thing with BOF.

You should still be aware of BOF, in case you see something like:

Code:

    If (rs.EOF) Or (rs.BOF) Then
        'No records
    End If

¹I have focused here on DAO Recordsets, since a form's Recordset is a DAO Recordset, even in A2K and later.
²Usually with a linked TableDef object, not a Recordset, but its easier to just be consistent.