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

You can calculate a subtotal in a subform (subform: A form contained within another form or a report.) and display it in the main form (main form: A form that contains one or more subforms.).

In the following example, the main form and the subform are based on tables with a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.). 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 and subform in Form view

Callout 1 The main form, based on the Orders table, limits the records in the subform to those for the current order.

Callout 2 The subform, based on the Order Details table, calculates the order subtotal, but does not display the total.

Callout 3 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 (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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

Callout 1 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]


Applies to:
Access 2003