• Back to the Mailbag

How Do I Display the Number of Records in a Form's Recordset?

There are a number of different ways to do this. Choose the one which best suits your situation. Before diving into VBA coding remember that the form's own Navigation Buttons display a count of the current recordset and indicate whether or not the recordset is the result of a filter:

But if I am displaying records in datasheet or tabular format I usually hide the Navigation Buttons because they aren't always useful. If you do this you have to find another way to count the records, then display the result in an unbound textbox or in the caption of a label.

Using the Count Function

This method is the simplest to use and requires no programming. Create an unbound textbox on your form and enter a Count expression into its Control Source property:

The Count function takes just one argument. This should be the name of a field which will contain a value for every record. Normally the only field to rely on for this is the Primary Key field (in my example the primary key field is called StaffID).

The textbox uses this expression to count the number of records and displays the result. It should display an accurate result whether the form is displaying a complete recordset or a filtered one.

The Count function should be suitable for most circumstances but if you find that is doesn't satisfy your requirements, there are some other methods you can try...

Using the DCount Function

How the DCount Function Works

DCount is one of Access' collection of Domain Aggregate functions (others include DSum, DMax, DMin, DLookup etc.) that are used to extract summary information from a recordset. They all work more-or-less the same way and take two or three arguments, for example: =DCount (Expression, Domain, [Criteria])

The Expression is usually the name of the field whose entries you are interested in. If you are counting records you must choose a field which can be guaranteed to contain data (ideally the primary key field). Alternatively you can use the asterisk (*) to represent all fields. The value must be provided as a string so put the field name in quotes (e.g. "StaffID").

The Domain is the table or stored query containing the field referred to in the Expression. Its name should be supplied as a string, enclosed in quotes (e.g. "tblStaff").

The Criteria argument is optional. If no criteria are supplied the function summarises the entire recordset. Otherwise, supply the criteria string in the form of an SQL WHERE clause (e.g. "[Office]='London' AND [Department]='Admin'").

Using DCount On a Form

DCount can be used without resorting to VBA by entering the function into the Control Source property of an unbound textbox control (note the equals sign preceding the function). For a simple count of the recordset the Criteria argument can be omitted. This example counts the number of values in the StaffID field of the tblStaff table using the expression:
=DCount("[StaffID]", "tblStaff")

The textbox control then displays the result of the DCount function:

If you have provided combo boxes so that your user can filter the displayed recordset, you can use their values to supply criteria for the DCount function. In this example the Control Source of the textbox uses the values in two combo boxes (named cboOffice and cboDepartment) included in the expression:
=DCount("[StaffID]","tblStaff","[Office]='" & [cboOffice] & "' AND [Department]='" & [cboDepartment] & "'")

Disadvantages of this Method

Using DCount this way is easy but it has its disadvantages. If the number of records in the recordset changes, perhaps because the user adds or deletes records, the value shown in the textbox is not automatically recalculated.

If the user changes the values in the combo boxes so that an incorrect WHERE clause is created (for example, by leaving one of them empty) the DCount function will return zero regardless of the number of records returned.

Like all the Domain Aggregate functions, DCount can be slow. Each time it is run it has to query the original recordsource which can take time when there are a large number of records or when data has to be transferred across a network. You might notice a delay whilst the function retrieves the required information.

Combining DCount with VBA

If you find that DCount works at an acceptable speed you can combine it with some VBA code to ensure that it always shows an up-to-date value. Clear the entry from the Control Source property of the unbound text box and instead have VBA write a value into the box for you.

Returning a Simple Record Count

For an unfiltered recordset all you need to do is create a VBA statement which uses DCount to calculate the number of records and write it into the textbox. If you attach this statement to the form's On Current event the value will be updated when the form is opened, and when each new record is displayed. Here's an example:

Private Sub Form_Current()
    Me.txtRecordCount.Value = DCount("StaffID", "tblStaff")
End Sub

Counting a Filtered Recordset

In the illustration above, I have provided combo boxes to allow the user to easily filter the records. This process requires some VBA code to read the values chosen by the user and construct an SQL WHERE clause. This is then applied to the form's Filter property. When the code then sets the form's FilterOn property to True the form displays a filtered recordset. The same SQL string can be used for the Criteria argument of the DCount function. Here's the code:

Private Sub FilterRecordset()
    Dim strOffice As String
    Dim strDepartment As String
    Dim strFilter As String
    If IsNull(Me.cboOffice.Value) Then
        strOffice = "Like '*'"
    Else
        strOffice = "='" & Me.cboOffice.Value & "'"
    End If
    If IsNull(Me.cboDepartment.Value) Then
        strDepartment = "Like '*'"
    Else
        strDepartment = "='" & Me.cboDepartment.Value & "'"
    End If
    strFilter = "[Office]" & strOffice & " AND [Department]" & strDepartment
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.txtStaffCount.Value = DCount("StaffID", "tblStaff", strFilter)
End Sub

The code contains one If Statement for each combo box. Its purpose is to determine whether the user has left the combo box empty or chosen an item from the list (even if you set the Limit To List property of a combo box to True the user can opt to leave it empty) and constructs an appropriate string (strOffice and strDepartment).

The strings created by the If Statements are then combined into a filter string (strFilter) which is used both to filter the recordset and to provide criteria for the DCount function.

Note that I have created a separate procedure for this code. This is because it needs to be run on the After Update event of each combo box as well as on the form's On Open event. Placing the code in its own procedure saves having to write it out several times. The procedure can be "called" from each event procedure that needs to use its code, for example:

Private Sub cboOffice_AfterUpdate()
    Call FilterRecordset
End Sub

Using the RecordCount Property

In Access VBA a recordset has a RecordCount property which returns the number of records in the recordset. You might be wondering why I haven't mentioned this before! On discovering this fact many novice programmers try to use it and discover that they get unpredicatable (i.e. wrong) results, and then abandon it wondering why it just doesn't seem to work. But there isn't anything wrong with it - you just need to know how it works and use it accordingly.

When the form opens a new recordset the number of records is stored in the RecordCount property. If the recordset changes the value has to be read again, but Access does not do this automatically. If you change a form's recordset, either by changing its RecordSource property or by applying a filter, the RecordCount will display a value for only those records it has "seen". If you take the form to the last record so that it has "seen" the entire recordset the RecordCount  property will display an accurate result.

Naturally, you don't want to have to scroll to the end of the recordset so instead of using the form's displayed recordset the code uses the RecordsetClone property. This provides a copy of the form's underlying recordset which can be manipulated without interfering with the original. The code required is quite simple:

Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
Me.txtStaffCount.Value = rst.RecordCount

The code declares an object variable to which the form's RecordsetClone property is applied. It then moves to the end of the recordset before using the RecordCount property to determine how many records there are and writing the value into the unbound textbox on the form.

The rst.MoveLast statement is bracketed by an error handler so that the error which would occur if the recordset were empty (i.e. zero records) is ignored. In this case the RecordCount property correctly returns the value 0.

Why RecordCount is Better than DCount

This method is superior to using DCount. It is much faster and will supply an accurate result for both filtered and unfiltered recordsets. If you are including it in a procedure that filters the form's recordset, or defines a new one, be sure to add it to the end of the procedure so that the records are counted after the modified or new recordset is in place.

©2005 Martin Green. All rights reserved. martin@fontstuff.com