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
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
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:
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
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.
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.
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
Me.txtRecordCount.Value = DCount("StaffID", "tblStaff")
Counting a Filtered
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:
Dim strOffice As String
Dim strDepartment As String
Dim strFilter As String
If IsNull(Me.cboOffice.Value) Then
strOffice = "Like '*'"
strOffice = "='" & Me.cboOffice.Value
If IsNull(Me.cboDepartment.Value) Then
strDepartment = "Like '*'"
strDepartment = "='" &
Me.cboDepartment.Value & "'"
strFilter = "[Office]" & strOffice & " AND [Department]" &
Me.Filter = strFilter
Me.FilterOn = True
Me.txtStaffCount.Value = DCount("StaffID", "tblStaff",
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
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:
Using the RecordCount
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
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:
rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
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
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.