Updating a field with an aggregate query

[TKI]

I have a form where people input invoice data. Within that form is a subform
where details of the invoice are entered, such as product and amount. The
main form is linked to the Invoice table. The subform is linked to the
Invoice Detail table. The two tables are joined by the invoice ID. I am
trying to update the invoice table with the subtotal of the invoice detail
amount. So basically, I want to take the subtotal of the all of the products
and update it to a field in the Invoice table called Subtotal. Is there
anyway to do that?

 

[Allen Browne]

TKI, it is *really* important not to store the subtotal in the invoice table
like that.

If you need to display the total on the main form, the Orders form in the
Northwind sample database shows how to read the total from a text box in the
subform footer, or you can use a DSum() expression if you're stuck.

It is possible to use the AfterUpdate and AfterDelConfirm event procedures
of the subform to assign a value to a field in the main form, but you really
don't want to take on responsibility for ensuing that your total is correct
for all cases, unless you have a special reason for doing so. Much easier to
get Access to give you the right answer, rather than chance something going
wrong and storing incorrect totals.