For a Microsoft Access 97 version of this article,
see
119134.
For a Microsoft Access
2000 version of this article, see
208998.
Moderate: Requires basic macro,
coding, and interoperability skills.
This article applies to a Microsoft
Access database (.mdb) and to a Microsoft Access project (.adp).
IN THIS TASK
SUMMARY
This article shows you how to reference a subtotal control
on a subform from a main form.
back to the top
More Information
If you try to sum a subform field by using a calculated control
on a main form, you may receive an error message in the main form control. For
example, the following expression in a main form control generates a "#Name?"
error message:
=Sum([MySubForm].Form![ExtendedPrice])
To avoid an error, you can sum the subform field by using a
calculated control in the form footer of the subform. You can then reference
the calculated control from the main form to display the summed value.
There is an example of this method in the Orders form of the
Northwind sample database (Northwind.mdb). First, a calculated control in the
footer of the Orders Subform sums the
Extended Price field of the subform to create a subtotal. The calculated control
uses the following syntax:
Text Box Control
----------------
Name: OrderSubtotal
ControlSource: =Sum([ExtendedPrice])
Because the
DefaultView property of the subform is set to Datasheet, you do not see the
calculated control in the form footer of the subform when you open the Orders
form in Form view. However, a text box control on the main Orders form refers
to the calculated control of the subform and makes its value display. The main
form control is called
Subtotal.
Subtotal references the calculated control of the subform.
Subtotal uses the following syntax:
Text Box Control
----------------
Name: Subtotal
ControlSource: =[Orders Subform].[Form]![OrderSubtotal]
This reference permits the main form to display a subtotal of a
subform control while the actual totaling of subform values is performed by
using a calculated control in the form footer of the subform.
back to the top
REFERENCES
For more information about referencing subform controls,
click
Microsoft Access Help on the
Help menu, type
calculating a total in a subform in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.
back to the top