About calculating a total in a subform and displaying it on a form
You can calculate a subtotal in a subform and display it in the main form.
In the following example, 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.
The main form, based on the Orders table, limits the records in the subform to those for the current order.
The subform, based on the Order Details table, calculates the order subtotal, but does not display the total.
The main form displays the order subtotal.
To calculate the order subtotal in the subform, 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])
The form footer isn't displayed if the DefaultView property is set to Datasheet.
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:
=[Orders Subform]![OrderSubtotal]
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. Instead, you can define the expression in the form's underlying query or SQL statement and use the name of the calculated field in the Sum function.
- 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 Microsoft Office folder, and then open the Orders form in Design view.