Microsoft

A quick method for counting Access records in a form

When you need to count records in an Access form, try the Count() function.

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:

  1. With the form in Design view, insert a text box control.
  2. 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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
mmatchen
mmatchen

DCount is also quite useful

Data Nut
Data Nut

It would be really nice if the authors and/or whomever approves these articles for publication would make sure the graphics display properly. Adding graphics to an article is a great way to add value ...unless you can't make out the details that make the graphic so useful. Just my .02

Marshwiggle
Marshwiggle

I copied this article to Word, then checked the pictures' Size properties. The first figure was 100%; the other two were 61%. When I set them to 75% they looked OK.

ssharkins
ssharkins

I know some of them are lacking -- I've tried. They look fine on my end. They look fine during the transfer. The end results are often iffy.

Editor's Picks