Validating the sum of a subforms entries

[Guest]

Hi,

I have a form with an embedded subform. The form is an invoice header with
the invoice number and other related information including cost. The subform
allows entries that belong to the invoice, but the belong in separate
accounts.

My problem is this: I need to validate that the sum of the lines entered in
the subform equal the total invoice cost. I'm using Access2k so there is no
record exit event. I can't use BeforeUpdate since this fires before the entry
of any subform lines. The closest I came was Cancelling the subform exit if
the total doesn't match. The problem with that is the subforms record isn't
updated or inserted to allow checking the final sum since the record is not
final. Is there anyway around this problem.

 

[Mark A. Sam]

Are you trying to make sure that the total of the cost amounts on the
subform doesn't exceed the total on the main form cost amount?

 

[Guest]

Yes! More than that I want to be sure that if a user goes in to an invoice
and changes the (parent) total or the amount of any of the (child) associated
lines that the total of the (parent) invoice matches the sum total of the
child records. Any ideas how to code this or at least what event on can hang
my code on.

 

[Guest]

Anyone? Anybody?

Yes! More than that I want to be sure that if a user goes in to an invoice
and changes the (parent) total or the amount of any of the (child) associated
lines that the total of the (parent) invoice matches the sum total of the
child records. Any ideas how to code this or at least what event on can hang
my code on.

 

[Nikos Yannacopoulos]

To begin with, basic database theory says you shouldn't store the total
at all; instead, you should calculate it on the fly whenever you need it.
Now, there can be counter-arguments as to why you might actually need to
store the invoice total at header level (actually all major ERP's
do!). In that case, the Before Update or After Update or On Change
event of the line item amount will do the job just fine, while the
control bound to header level total in the mainform should be
disabled/locked, so the user can't change it anyway - why should they
have access to it?

HTH,
Nikos