Handling Subtotal/Tax on a Form/Subform

[DanGriffin]

I've got an invoicing application with a Main form containing Subtotal, Tax, Freight and Total. The subform contains the detail info; Item, Qty, Price and Extension.

I've got it all working fine with the usual calculations as discribed elsewhere in these forums, such as...

=Round(Nz([FormSubTotal]*CCur([TaxRate]/100),0),2)

for the tax calculation and...

=Sum([Exten])

for the subtotal calculation. And then move the calculated text box data to the proper tables when the user clicks on SAVE.

My problem is that I have been told that on rare occations the user needs to be able to override the calculated tax by entering a different amount directly into the "tax field".

Of course Access will not let you enter data into a calculated field (text box). How do I have an automatically calculated field that can later be overridden with direct input?

I have tried putting the table field for the tax data in that position but I have not been able to update it with "current" information!! No matter where I place my "formula", or which event I attach it to, it is always out of sync with the "current" information. If I have 3 detail items on the subform, the tax field shows the result for the first 2. If I have 1 item, it shows zero, etc.

I'm getting the feeling that I took the wrong design approach to begin with, execpt the methods I have employed to this point seem to be "standard" from what I have been able to read so far.

Now what?

Dan

 

[JerryDennison]

Ostensiby you do not use a calculation as the basis for the Tax field. In the AfterUpdate event procedure of the item(or item price) control you should change the tax control(which should be bound to the tax field) to the "default" value of price * tax rate. I'm going to assume that you have either a standard tax rate in a table somewhere or on this form. Either way this will put the calculated amount into a bound field which you can give your user the ability to overwrite.

BTW, it is usually against the Forms of Data Normalization to store calculated results in your table(s). This is one of those rare exceptions to this rule.

 

[DanGriffin]

Your right, that is deffinately a better way to do it. However, I'm not sure it will work in this case because the user often will need to go back and edit the quantities and/or prices and the tax amount needs to be recalculated whenever a quantity or price is changed.

 

[JerryDennison]

This is why you should use the AfterUpdate event of every control this field relies on. You can write a common function to calculate the tax and call this function from each AfterUpdate event for all control's the tax rate is dependent on.