calculations

[Tammie]

I am developing a personnel application that has a lot of
fields which are calculated using a rates table. The
results need to be be stored otherwise I would just use
the control source. What is the best way to do this? Can
I run a query with the calculated expressions and tell
which fields to store them in? Is it best to handle in
the open form?

 

[John Vinson]

I am developing a personnel application that has a lot of
fields which are calculated using a rates table. The
results need to be be stored otherwise I would just use
the control source. What is the best way to do this? Can
I run a query with the calculated expressions and tell
which fields to store them in? Is it best to handle in
the open form?

If you're SURE that the values need to be stored (i.e. you want to
capture the value using the current rates, and if the rates change you
do NOT want the calculated value to change), it's probably best to
handle this using VBA code in the AfterUpdate event of the field which
is used in the calculation along with the rate. You can "push" the
calculated value into the target field; for instance (hypothetical
here):

Private Sub Cost_AfterUpdate()
Me!TaxedCost = Me!Cost * DLookUp("[TaxRate]", "[Taxes]", "[State] = '"
& Me!cboState & "'")
End Sub

(very simple case of course, in real life tax codes aren't anything
LIKE that easy!)