Record sum from subform

[KevinK]

Hi
I have a sub form which contains purchase order items. What I would like to
is to record the sum of what I paid for these items in the AccountPayable
field on the parent form. Is there an easy way to do this?
The Inventory field on the subform contains the total price paid for each
item. If I purchase 1 or 10 items I need to calculate the sum and record in
the AccountPayable field on the Parent form which contains all of the extra
costs associated with the Purchase Order i.e., tax etc.
Thanks

 

[Allen Browne]

You can *display* the sum of the items in the subform, but please do not
store the total in the main form's table. If you do, you create a nightmare
for yourself, trying to ensure that the total is always right, regardless of
how the items are added, deleted, or edited, via the interface or elsewhere.

It's just not reliable if you store the total: why do the hard work
yourself, when the computer can do it for you? (Technically, one of the
rules of database normalization is that you never store a dependent value
like that.)

To sum the Amount field in your subform, place a text box in the Form Footer
section, and set its properties like this:
Control Source =Sum([Amount])
Format Currency
Name txtSubTotal

Show the subform in Continuous Form view (i.e. lay out the text boxes side
by side, with the labels in the Form Header so they appear over ach column),
and the total shows in the Form Footer section.

If you really need to show the total on your main form, you the
ControlSource of the text box will be like this:
=[Sub1].[Form]![txtSubTotal
where you substitute the name of your subform control for Form1.
Explanation of the .Form bit:
http://allenbrowne.com/casu-04.html

There's an example in the Northwind sample database that comes with Access,
on the Orders form.