How do I add a sum of a subform on a parent form?

[Guest]

Hi,

I have two forms and tables say [Invoice Header] and [Invoice Lines].
The tables are joined by [Invoice Number]. I must say for some reason (long
story), the invoice header and lines are fed separately but do tie with each
other.
The user can go in the form header and then open the subform with the line
info. I would like to make it that if the user is updating the one of the
line amounts of the invoice line form, that upon closing the subform the
invoice amount on the header is recalculated.

I tried an update query which looks like
UPDATE [Invoice Header] INNER JOIN [Invoice Line] On [Invoice
Header].[Invoice Number] = [Invoice Line].[Invoice Number] SET [Invoice
Header].[Amount] = SELECT SUM ([Invoice Line].[Amount]) FROM [Invoice Line]
GROUP BY [Invoice Line].[Invoice Number]
WHERE [Invoice Line].[Invoice Number] = Form![Invoice Line].[Invoice Number]

and I get an error "Operation must use an updatable query"

Thanks in advance,
Kanga

 

[Allen Browne]

Kanga, don't store this total in the main form's table.

Instead ask Access to calculate it when you need it. This frees you from the
burden of trying to ensure that the stored total is always right, regardless
of how records are added, altered, or deleted from the order.

For an example of how to show the calculated total on the main form, open
the Northwind sample database, the Orders form, and check out how the
Subtotal works.

 

[Guest]

Thanks Allen! I went by your recommendation and it is working just great.
I think this is not the first time you're helping me... You're very helpful!
Kanga

Kanga, don't store this total in the main form's table.

Instead ask Access to calculate it when you need it. This frees you from the
burden of trying to ensure that the stored total is always right, regardless
of how records are added, altered, or deleted from the order.

For an example of how to show the calculated total on the main form, open
the Northwind sample database, the Orders form, and check out how the
Subtotal works.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have two forms and tables say [Invoice Header] and [Invoice Lines].
The tables are joined by [Invoice Number]. I must say for some reason
(long
story), the invoice header and lines are fed separately but do tie with
each
other.
The user can go in the form header and then open the subform with the line
info. I would like to make it that if the user is updating the one of the
line amounts of the invoice line form, that upon closing the subform the
invoice amount on the header is recalculated.

I tried an update query which looks like
UPDATE [Invoice Header] INNER JOIN [Invoice Line] On [Invoice
Header].[Invoice Number] = [Invoice Line].[Invoice Number] SET [Invoice
Header].[Amount] = SELECT SUM ([Invoice Line].[Amount]) FROM [Invoice
Line]
GROUP BY [Invoice Line].[Invoice Number]
WHERE [Invoice Line].[Invoice Number] = Form![Invoice Line].[Invoice
Number]

and I get an error "Operation must use an updatable query"

Thanks in advance,
Kanga