Others

How to Use DCount to Count Employees by Location in Access

Amos Gikunda Sep 27, 2026 869 views

Question details

The user needs to count the number of employees assigned to a specific location in Microsoft Access using a combo box selection and the DCount function.

How to Use DCount to Count Employees by Location in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a dynamic form where selecting a location from a combo box calculates and displays the employee count for that location in a text box.
Observed behavior: The user wants to display the location name in the combo box while passing the underlying location key to the DCount function or grouped query to execute the count.

Before you start

Ensure your combo box has at least two columns: the bound column containing the location key (usually hidden with a width of 0") and the second column displaying the location name.

Solution 1Recommended

Calculate Employee Count Using the VBA After Update Event

This method uses a VBA script triggered by the combo box selection to calculate the count and assign it to an unbound text box.

Using the After Update event provides more control over when the calculation happens and allows you to easily handle blank selections using the Nz function.

Open Form in Design View

Right-click your form in the navigation pane and select 'Design View'.

Clear the Text Box Control Source

Select the text box where the count will appear (e.g., EmployeeCount). Open the Property Sheet, go to the Data tab, and ensure the 'Control Source' field is completely empty.

Access the Combo Box Event

Select your combo box (e.g., LocSearch). In the Property Sheet, navigate to the Event tab, click on 'After Update', and then click the ellipsis (...) button to open the Code Builder.

Add the DCount VBA Code

Enter the following code into the subroutine: Me.EmployeeCount = DCount("*", "tblEmployees", "tblLocationsFKey = " & Nz(Me.LocSearch, -1))

Save and Test

Save the VBA module, switch your form back to 'Form View', and select a location from the combo box to see the updated employee count.

Calculate Employee Count Using the VBA After Update Event

Handling Null Values: Using the Nz() function ensures that if the combo box is cleared, the DCount function searches for a non-existent ID (like -1) instead of returning an error.

Solution 2

Use DCount Directly in the Text Box Control Source

A code-free approach where the text box's Control Source directly references the combo box value to calculate the employee count.

Solution 3

Use a Grouped Query for Large Datasets

Domain aggregate functions like DCount can be slow on large tables. Creating a grouped query provides much faster performance.

Free Microsoft Office alternative

Looking for a Free Office Suite? Try WPS Office

While WPS Office does not include a database management tool like Microsoft Access, it provides an excellent, highly compatible alternative for handling data in spreadsheets. WPS Spreadsheet offers powerful functions, pivot tables, and data processing tools to manage employee records seamlessly and for free.

1. Visit the Official Website: Go to the official WPS Office website to find the latest version.
2. Download the Installer: Click the 'Free Download' button to download the lightweight installation package.
3. Install and Use: Follow the simple installation steps and open WPS Spreadsheet to easily manage and count your data.

Completely free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx)Manage massive datasets efficiently with built-in Pivot Tables and advanced formulasFamiliar tabbed interface makes transitioning effortless

Frequently Asked Questions

Why does my DCount formula return an #Error or #Name?

This usually happens if the field names or table names in your DCount formula are misspelled. Double-check that 'tblEmployees' and the foreign key field match your actual database names perfectly. It can also occur if the text box has a conflicting Control Source while you are trying to push a value to it via VBA.

How do I count employees matching multiple criteria in DCount?

You can string multiple criteria together using 'AND' inside the criteria string. For example: DCount("*", "tblEmployees", "tblLocationsFKey = " & Me.LocSearch & " AND Status = 'Active'").

Why isn't the employee count updating when I change the combo box?

If you are using the Control Source method (=DCount...), the form may need a requery to realize the combo box value has changed. To fix this, open the combo box's After Update event and add the VBA line: Me.EmployeeCount.Requery.

Office Quick Solutions