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, 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 (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])
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:
- If you want to use the value from a calculated control (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) 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 (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) or SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.) and use the name of the calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) 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 (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).