Calculating total from column of subform controls

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

If I have a "Continuous Forms" subform, which contains a column of financial
data, how do I sum all the values in that column and update a control in the
parent form with that total value?
 
To sum the column named "Amount" in the subform, display the subform in
Continuous View, add a Form Footer section (View menu when in form design).
Put a text box in the Form Footer section, and set these properties:
Control Source: =Sum([Amount])
Format: Currency

If you wish to display the total on the main form instead, open the
Northwind sample database, the Orders form, and see how it picks up the
total from the subform.

You would not store this total in the main form's table. Doing so violates
one of the most basic rules of data normalization (not to store dependent
data), and will cause you unnecessary maintenance problems (to guarantee the
total is always right, no matter how the data is updated.)
 
Mike,
In the past when I have done similar operations I have used the "SetValue"
Macro function. It is pretty handy. Open a new Macro and choose any field
to the left. In the drop down menu that appears select the SetValue
function. You will have two fields to fill in; what field (including ALL of
the fields in the property window), and to what value. The only other thing
is to set the action of the Macro. You can attach it to a button or an on
focus function for example. I hope this helps.
 
Back
Top