Calculate tax and store in table

[Linda]

Because we buy items in several states with varying state and local taxes, I
need to calculate the tax based on input by the user and store the amount in
the underlying table. I can perform the calculations, but I am not sure how
to get the result into the table.

I have considered a separate tax table with rates, but I have no way of
knowing in advance what local municipalities we may deal with (it could be in
the hundreds). I need the amounts recorded with each record in the table in
order to produce accurate cost reports.

Any ideas?

 

[Al Camp]

Linda,
You should be saving the PurchaseAmount and TaxRate to your table, but
not the TaxAmount. That is a calculated field, and can always be
re-calculated, on the fly, in any subsequent form, query or report. Don't
save the result of a calculation if you can recalculate it from the data you
have.

If the final TaxRate is a calculated value on your form, then use the
AfterUpdate event of the calculation elements to update your TaxRate table
field.
Ex. a calculated field called [calcTaxRate] on your form with...
= FedTax + StateTax + CountyTax
On the AfterUpdate event of Fed, State, and County...
TaxRate = [calcTaxRate]
That way, you always store the correct TaxRate, even if any of the
elements that make that up, might change later.

 

[Klatuu]

As soon as I saw "Calculate.......and store in table", I thought, oh no, here
we go again. It is always a bad idea to store calculated values. Of course,
every rule has an exception. This may be one, so I'll show you how it can be
done, then, I wll suggest how you can avoid storing calculations.

I will assume you have 2 controls on your form. One for the purchase amount
and one for the tax rate. Let's call them txtPurchaseAmount and txtTaxRate.
We will add one more and call it txtTaxAmount. Bind the field in the table
where the tax amount is to be stored to txtTaxAmount.
The trick in any situation where you have to calculate an amount and store
it is being sure you have values in the controls used in the calculation.
What I usually do is create a sub that does the calculation and call it from
the After Update event of both controls involved in the calculation.
Sub CalcTaxAmount
If Not IsNull(Me.txtPurchateAmount) and Not IsNull(Me.txtTaxRate) Then
Me.txtTaxAmount = Me.txtPurchaseAmount * Me.txtTaxRate
End If
End Sub

Then in the After Update events of txtPurchaseAmount and txtTaxRate:
Call CalcTaxAmount

It is also not a bad idea to check for a tax amount in the Before Update
event of the form:
If IsNull(Me.TaxAmount) Then
MsgBox "Tax Amount has not been Calculated"
Cancel = True
End If

Now, as to not having to carry the tax amount in the table. You could have
a tax rate table that would have the taxing authority and the tax rate. I
would suggest a combo box with the tax rate table as the row source, the
taxing authority as the bound column. Then for a municipality that is
already in the table, you only need to select it and do the calculation in
the After Update event of the combo box. You would still have to be sure
there is an amount in the purchase amount control. You could also use the
Not In List event of the combo box to open a form that would allow you to add
the new municipality and rate to the table.

 

[Linda]

Thanks to both of you for responding. I am now storing the rate for taxes in
the table and calculating the taxes on the form.

The help I have received from the discussion groups during this project has
been excellent, and saved me from tearing my hair out!

 

[Spier2vb]

To "Linda" :

how you can storing the data in the table and calculating the taxes on the
form.

i need your help because I have the same problem .