When using MS Access, there are a number of ways of displaying the number of records in a form’s Recordset. A very simple way is to look at the display by the navigation buttons that gives you a count and indicates whether or not the Recordset is the result of a filter.
However, displaying the navigation buttons is not always useful, so here are some alternate ways.
Count Function
This method requires no programming and is fairly fast.
-
Create an unbound textbox on your form
-
Click the Data tab
-
In the Control Source field, enter a Count expression using the following syntax:
=Count([primary key])
The Count function will only accept one argument to base its count on so in the place of primary key above, ensure that you enter a field name that contains a value for every record.
This textbox will display an accurate result according to your argument regardless of whether your form is displaying a complete Recordset or a filtered one.
DCount Function
The DCount function, unlike the Count function, accepts more than one argument. However, like most of the Domain Aggregate functions, it can be quite slow to run.
-
Create an unbound textbox on your form
-
Click the Data tab
-
In the Control Source field, enter the DCount expression
The syntax:
=DCount (“Expression”, “Domain”, [Criteria])
The Expression is the name of the field whose entries contain the data to be counted, ideally the primary key field. However you can use the asterisk to represent all fields.
The Domain is the table or stored query that contains the field that is used in the Expression. If you wish to summarize the entire Recordset then you can ignore the Criteria part of the function.
If you wish to determine a condition on which to base the results, you can supply a string argument in the form of an SQL WHERE clause in place of the Criteria. This is useful if you have provided a means for the user to filter the displayed Recordset.
In addition DCount often running slowly, another pitfall is that if the number of records in the Recordset changes, the value displayed as a result of this function is not automatically updated. This can however be rectified using VBA.
RecordCount Property
Using Access VBA, the RecordCount property returns the number of records in a Recordset. It seems a simple approach but inexperienced programmers must ensure they use it correctly in order to display accurate results.
When a form opens a new Recordset, the number of records is stored in the RecordCount property and it displays a value according to the number of records it has ‘seen’ hence why some find it to return inaccurate results. The important part of ensuring this does not happen is to take the form to the last record so that the RecordCount property has ‘seen’ all of the records.
The RecordCount property, when used correctly, is far better than DCount as it is faster and, returns an accurate result regardless if whether a Recordset is filtered or not.
Author Bio
This is a guest post by Hollie. Hollie provides Microsoft Access training for Acuity Training. In her spare time she loves to go to the gym and dress making.
