Others

How to Fix DCount Counting All Records in Access Form

Nimra Malik Sep 30, 2026 868 views

Question details

The user needs to configure a DCount expression on a main form to count only the subform records associated with the currently displayed record.

How to Fix DCount to Count Records for the Current Artist in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Displaying a dynamically updated count of related records (albums) for a specific entity (artist) being viewed on a main form.
Observed behavior: The DCount expression ignores the current form record and evaluates to the total count of all albums across the entire database.

Before you start

Verify your table relationships and ensure that the primary and foreign keys (e.g., ArtistID) share the exact same data type in both the main and related tables.

Solution 1Recommended

Apply Dynamic Criteria to the DCount Expression

Modify the DCount criteria string to dynamically reference the ID of the current record, ensuring it filters the count properly.

By default, if the third argument (criteria) in a DCount function is omitted or written statically, Access will evaluate the entire table. To restrict the count to the current form's record, you must concatenate the current control's value into the criteria string.

Open Form in Design View

Right-click your main artist form in the Navigation Pane and select 'Design View'.

Locate the Calculation Text Box

Click on the text box where you want the album count to display, then press F4 to open the Property Sheet.

Update the Control Source for Numeric IDs

If your ArtistID is a Number data type, enter the following expression in the Control Source property: =DCount("*", "AlbumTitle", "ArtistID=" & [ArtistID])

Update the Control Source for Text IDs

If your ArtistID is a Short Text data type, you must wrap the value in single quotation marks: =DCount("*", "AlbumTitle", "ArtistID='" & [ArtistID] & "'")

Save and Test

Switch back to 'Form View' and navigate between different artist records to confirm the album count updates dynamically.

Apply Dynamic Criteria to the DCount Expression

Replace Placeholder Names: Be sure to replace 'AlbumTitle' and 'ArtistID' with the actual table/query name and field name used in your specific Access database.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite? Try WPS Office

While you manage your databases in Microsoft Access, you may also need a fast, reliable solution for documents, spreadsheets, and presentations. WPS Office is a completely free, all-in-one alternative to Microsoft Office that is fully compatible with your existing files.

Seamlessly open, edit, and save Microsoft Word, Excel, and PowerPoint formatsLightweight software that runs swiftly even on older operating systemsFamiliar tabbed interface requiring zero learning curve for Office usersBuilt-in PDF toolkit for converting, editing, and signing documents securely

microsoft office alternative - wps office

Frequently Asked Questions

Why does my DCount expression return an #Error on the form?

This usually indicates a syntax error in your expression. Check for missing quotation marks, mismatched ampersands, or misspelled field and table names. Also, ensure the control you are referencing actually exists on the current form.

Can I count subform records without using DCount?

Yes. Instead of DCount, you can add a text box to your subform's Footer section with the expression =Count([AlbumID]). Then, on your main form, add a text box that references the subform's text box (e.g., =[SubformControlName].[Form]![CountTextBoxName]). This is often more efficient than domain aggregate functions.

How do I add multiple criteria to a DCount function?

You can include multiple conditions in the criteria string by using the SQL 'AND' operator. For example: =DCount("*", "AlbumTitle", "ArtistID=" & [ArtistID] & " AND ReleaseYear > 2000").

Office Quick Solutions