About calculating a total in a subform and displaying it on a form

You can use a form with a subform to calculate a total for a group of records. The main form and the subform are based on tables with a one-to-many relationship. The main form shows records from the "one" side of the relationship; the subform shows records from the "many" side of the relationship. For example, the following illustration shows an Orders form that includes a subform.

The main form and subform in Form view

To calculate the order subtotal, you add a text box to the form footer of the subform and specify an expression in the ControlSource property box for the text box. The expression uses the Sum function. For example, the following expression calculates the total amount for all the products in the Orders form:

=Sum([Quantity]*[UnitPrice])

In the subform's Design view, the calculated control is in the form footer

To display the order subtotal on the main form, create a text box on the main form that uses an expression to refer to the control on the subform that contains the calculated value. For example, you could specify the following expression in the ControlSource property box of the text box on the main form:

Expression on the main form that references the calculated control on the subform

Notes

If you want to use the value from a calculated control when computing a total, you can't use the name of the control in the Sum function. For example, if you had a control that calculated quantity * unit price, you'd still have to specify the full syntax =Sum([Quantity]*[UnitPrice]) in the expression. For this reason, when you use an expression that requires an aggregate function such as Sum, it's best to define the expression in the form's underlying query or SQL statement. Then you can use the name of the calculated field in the Sum function. For example, you could define a calculated field such as ExtendedPrice: [Quantity]*[UnitPrice] in an underlying query, and then use the field name in the expression that calculates the sum: =Sum([ExtendedPrice]). For information on creating a calculated field in a query in a Microsoft Access database (.mdb), click . For information on creating a calculated field in a query in a Microsoft Access project (.adp), click .
The Orders form in the Northwind sample database has an example of a subform that calculates a subtotal that's displayed on the main form. To view this form, open the Northwind database in the Samples subfolder of your Office folder, and then open the Orders form in Design view. For information on opening Northwind, click .