Subform total and Access timing

[Pecanfan]

Hi,

Really hoping someone can help with this one - tried just about every NG
suggestion going and none have really worked. Sure this must be a really
simple thing to sort out...

I have an Invoice form called FRM_Invoices with a subform called
FRM_SubInvoice.

FRM_SubInvoice has (amongst other irrelevant stuff) the following fields:-

Quantity
UnitCost
Discount
LnTotal

The AfterUpdate event for the Quantity, UnitCost and Discount textboxes
contain the following (amongst other stuff) in VB:-

[LnTotal] = ([Quantity] * [UnitCost]) - (([Quantity] * [UnitCost]) *
([Discount] / 100))

The footer of the subform has an unbound control called txtInvTotal with the
expression:-

=nz(Sum([LnTotal]))

FRM_Invoice has (amongst other stuff) the following fields:-

NetDue
VATDue
TotalDue

The 'Close' button on the main FRM_Invoices form has the following code
behind it:-

[NetDue] = FRM_SubInvoiceSubform.Form!txtInvTotal
[VATDue] = Int(([NetDue] * [VATRate] / 100) * 100) / 100
[TotalDue] = [NetDue] + [VATDue]
DoCmd.Close

This all works great BUT if the user doesn't click the close button or if
the database crashes or if they close Access without closing the Invoice
form or any number of other scenarios I can think of, then my [NetDue] field
won't be populated. This is bad since all accounts etc. are produced from
the [NetDue], [VATDue] and [TotalDue] fields.

I've tried every which way possible to force passing of the value
FRM_SubInvoiceSubform.Form!txtInvTotal over to [NetDue] through AfterUpdate
events and the like on both the main form and the subform BUT, due to Access
timing issues, the value passed over from the subform footer is always zero.

The other way I thought of getting around this was by calculating the Sum of
LnTotal within VB but my limited knowledge of programming lets me down here
. i.e. in the AfterUpdate event of the Discount, Quantity etc. fields in
the subform I tried adding:-

Forms![FRM_Invoices]![NetDue] = Sum([LnTotal])

But this don't work. No idea if it would fix the problem even if it did
work.

Can anyone help?

TIA,

Andy

 

[Amy Blankenship]

This is precisely why you don't try to store calculated values in tables.
You should just recalculate it in your queries and reports when you need it.

HTH;

Amy

 

[Pecanfan]

This is precisely why you don't try to store calculated values in tables.
You should just recalculate it in your queries and reports when you need it.

HTH;

Amy

Er thanks, but no that doesn't help at all really. Surely you don't try to
store calculated values in tables because it's bad normalisation practice
and results in duplicate information being stored; not to avoid timing
issues on data entry forms?

One of my main reasons for storing the calculated figure is because of past
floating point rounding errors in Access which means the calculated value
*now* doesn't necessarily match the calculated value when the invoice was
originally given to the customer.

Andy

 

[Amy Blankenship]

Er thanks, but no that doesn't help at all really. Surely you don't try
to
store calculated values in tables because it's bad normalisation practice
and results in duplicate information being stored; not to avoid timing
issues on data entry forms?

That's another reason. But the way forms work in Access assumes *good*
normalization.

One of my main reasons for storing the calculated figure is because of
past
floating point rounding errors in Access which means the calculated value
*now* doesn't necessarily match the calculated value when the invoice was
originally given to the customer.

That's a different problem entirely, and not one I've had to worry about as
eLearning applications tend not to be terribly math heavy.

 

[Pecanfan]

Er thanks, but no that doesn't help at all really. Surely you don't try

That's another reason. But the way forms work in Access assumes *good*
normalization.


That's a different problem entirely, and not one I've had to worry about as
eLearning applications tend not to be terribly math heavy.

OK, thanks anyway.

Andy

 

[Pecanfan]

Er thanks, but no that doesn't help at all really. Surely you don't try

That's another reason. But the way forms work in Access assumes *good*
normalization.


That's a different problem entirely, and not one I've had to worry about as
eLearning applications tend not to be terribly math heavy.

OK, thanks anyway.

Andy