---

MS Access 2007: Display on a form, the number of related records from another table

This MSAccess tutorial explains how to display on a form the number of related records from another table in Access 2007 (with screenshots).

Question: In Microsoft Access 2007, I have one table called Accounts that stores master account information. I have another table called Account_units that stores unit information for each account. I want to display on my Account form, the number of records in the Account_units table for the account currently displayed in the Account form.

How can I do this?

Answer: To display the number of records in the Account_units table for the current account displayed, you will need to create a custom function in a Module. Then reference this function on your form. We've provided a download to demonstrate how this can be done.

Download version in Access 2007

We've created a form called frmAccounts that lists all records in the Accounts table. We've then created a textbox called TotalUnits that displays the number of records in the Account_units table (for the account displayed on frmAccount).

If you view the Properties for the TotalUnits textbox, you can see that the Control Source property is set to:

=CalcUnits([AcctNo])

This textbox is linked to a custom function that we've created called CalcUnits. You can find this function defined in the Module called modGeneral in our sample database.

This function accepts the AcctNo as the parameter. It then queries the Account_units table and returns the number of records that exist for this AcctNo.