store a calculated field

S

SylvieB

Hello
In a form, i have 2 fields: order value and win% that the user need to fill
out. I need to calculate another field named WeightedValue automatically
based on the 2 fields and store it in a table. The formula is (OrderValue *
Win%)/100. I tried with a query but nothing gets stored in the table. Any
suggestion or ideas how to accomplish that?
Thank you in advance for any help.
 
B

BruceM

The short answer is that you do not store it in the table. Why do you want
to store the value? If you are getting the correct result from the
calculation, why change? In general it is more efficient to calculate on
the fly than to retrieve a stored value. If you store the value you must be
sure it changes when either of the input fields changes.

To store the value you would need to write the calculation result to a
field, perhaps in the After Update event of the text box bound either to
OrderValue or Win%. (BTW, avoid anything other than alphanumeric characters
and underscores in names. In some cases Access can become confused.) But
be very, very careful. A calculation is virtually foolproof. A stored
calculation is a likely trouble spot down the road.

If Excel is your model for this line of thought, consider that Excel does
not store calculation results. The difference from Access is that you can
see the results in the worksheet. You should not be working directly with
tables in Access. Forms are for working with data, and reports for printing
it. In either a form or report you can see the result displayed.
 
S

SylvieB

Hey Bruce
thank you for your quick answer. I have to store the value because my boss
wants to be able to see it on the form. I did what you suggested.

Private Sub order_value_AfterUpdate()
Me.txtWeighted = (Me.order_value * Me.cboWin) / 100
End Sub

this is the code i wrote but it does not work. It does not store anything in
the table.
Any ideas why?
Thanks
 
S

SylvieB

Bruce
Please disregard my previous email. My mistake, my calculation was wrong.
the system could not understand the percentage. I fixed it. It's working
now. thanks again for your help.
 
B

BruceM

Your boss or anyone else could have seen the calculation on the form if you
had based the form on the query containing the calculation, or had used the
calculation as the ControlSource of an unbound text box. As it is you have
the correct result *now*, but if you or anyone else *ever* changes the value
in cboWin (which does not seem to have an After Update event) the
calculation will be wrong. Same thing applies if somebody changes a value
in the table or in a query, or if the value changes by any means other than
by way of the text box with the After Update event.

**WARNING** It is very likely that your approach will lead to incorrect
Order Values in the future.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top