Displaying Aggregate Expressions on a Main Form from a Subform
In Microsoft Access, when a main form and subform are created based on one-to-many relationships, you can display aggregate information in the main form; for example, a total COUNT of customer records for each sales person or a SUM total of sales amounts that have been displayed in a subform.
As you scroll through this main form, many customers may be associated with each sales representative:
Before aggregate data can be placed in a main form, its value must be found in the subform. You can do this by creating a text box and setting its Visible property to No so that it will be hidden in the subform. Then place the aggregate expression in the Control Source property.
In the example here, the aggregate expression would look like this:
=Count([CustID]).
Then in the main form, you would reference this aggregate expression in a new text box:
=[Subform].Form![Aggregate Control]
where Subform is the name of the control in the main form that contains the embedded subform and Aggregate Contol is the control in the subform that contains the aggregate data. The .Form means that the control path points to another form, and the ! is used as a separator. This expression will allow the main form to reference the text box in the subform that performed the calculation.
Note: The Subform name in this expression may or may not be the same name as the name of the subform that you see in the Navigation pane.
As you can see here, the subform control is named fsubCustomers, and the subform control with the aggregate Count expression is named txtcustcount. A label (# Customers) has also been added.
The customer count on the main form will update every time you change its value - in other words, every time you add or delete a customer, the number of customers for that sales person will change accordingly.
