There are several ways to count the records in a form’s recordset. In fact, Access forms display the record count by default. For many, this built-in feature is more than adequate. However, some people choose to inhibit the Navigation buttons, which display the record count.
If users need to see the number of records populating a form and the form doesn’t display the Navigation buttons, drop in a Count() function to display the record count as follows:
With the form in Design view, insert a text box control.
Enter the expression in the form =Count(primarykey) as the control’s Control Source property.
The Count() function takes just one argument, and in this case, that’s the name of the field that contains a unique value for each record — usually the primary key.
With the form in Form view, the expression returns the number of records in the primary key field. In the following figure, the primary key is the ProductID value in Northwind’s Products table. Notice that the result of the Count() function in the form’s header matches the form’s Navigation button — there are 77 records.
The Count() expression will also accommodate a filtered recordset, as shown below. In this case, there are 13 records in the filtered recordset.
In both cases, the Count() function works fine. This method won’t work in every situation, but it’s flexible enough to handle most.
Subscribe to the Microsoft Weekly Newsletter
Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays