Help with totals on forms!

[dave]

Hi

Im a bit of a newbie to Access so any help with this problem is gratefully
received.

I have a form with a subform in it. The subform called InvoiceDetail
contains several lines of detail for an invoice. Each line has an Amount
field. At the bottom of the subform I have a text box that provides a
running total. This was easy enough to create by using "=sum(Amount)" in a
field called subtotal. However on the main form, Invoice, as well as
having a date and invoice number, I also have a total field. This field is
exactly the same as the one on the subform but I cannot get the total to
appear. I have used the line =[InvoiceDetail subform].Form!subtotal but
when i run the form I get an error message #Name? I need this field to
write back into the Invoice table.

Any ideas?

Thanks

 

[Allen Browne]

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Other tab of the properties box, what is the Name of the subform
control? Its Name can be different from its Source Object (the name of the
form it contains).

You should NOT store the invoice total back to the Invoice table, unless
there is a reason why you sometimes charge an amount that is different from
the sum of the amounts in the subform. That's a basic rule of data
normalization: never store dependent data.

If you do have a reason to do this (and I cannot imagine a valid one for
this example), use the AfterUpdate event procedure and the AfterDelConfirm
event procedure of the form in the subform to DSum() the amount directly
from the subform's table, write the value to Me.Parent.TotalField, and set
Me.Parent.Dirty to False.