Microsoft Office Tutorials and References
In Depth Information
Adding Subtotals and Totals from Subforms
2. Add a Form Footer section (assuming that the subform doesn’t already
have one) by right-clicking the location where you want to add a
footer and then choosing Form Header/Form Footer from the
If you see a Form Footer divider bar and space below it, you already
have a footer. Click the bottom edge of the bar and drag down to expand
the footer and make space for a text box or two. (See Chapter 3 of this
minibook for more information about form headers and footers.)
3. Note the name of the field in the record source (not the control on the
form) that contains the values you want to count or total, or note the
expression you want to total.
Frequently, the control that displays a field has the same name as the
field itself, but not always. Be sure to use the field name, not the name
of any control on the form that displays the field. For this example, you
want to total the expression [Unit Price] * [Qty].
4. Make a text box by clicking the Text Box button in the Controls group
on the Design tab of the Ribbon and then clicking the Form Footer
section on the form.
Access creates an unbound text box.
5. On the Data tab of the property sheet for the text box, type the
expression that you want to calculate in the Control Source property.
For this example, type = Sum([Qty]*[Unit Price]) in the Control
Source property, as shown in Figure 4-8. Access displays hints just
below where you’re typing, such as the names of fields and functions.
6. Enter a descriptive name for the control in the Name property on the
Other tab of the property sheet.
Make a note of the control name (such as OrderSubtotal), because
you need it to display the value on the main form. You may want to
change some other properties later, such as switching to Currency
format for the new field in the footer, if appropriate.
7. Switch to Form view by clicking the View button on the Ribbon to
make sure that the new text box works.
Because you’re looking at the subform as an independent form, the
subform shows all the records in its record source, and the calculation
totals all the records, not just those for one order. Don’t be surprised if
you see a very large number. When this form is used as a subform, the
linkage between the subform and the main form restricts the records in
the subform to one order at a time, and the control totals the records
for only the current order.