Calculation

  • Thread starter Thread starter DaveB
  • Start date Start date
D

DaveB

I have a form with several calculated fields in it. I
would like these calculated fields to go into other
separate fields in my table.

Can I tell these calculated fields to go into these
separte fields that I've setup in my table?

Thanks.

Dave
 
Dave,
First, in most instances, you don't usually save calculations results in
a table field. (But there are situations where it is necessary)
For example... if you had Price and Qty, you wouldn't have to "save" the
LineTotal (Line Total=Price*Qty).
Since you've captured Price and Qty in your table, you can always
"re-derive" the LineTotal... "on the fly" in any subsequent
form/query/report/ etc...
You can always set up an unbound text control on any form/report/etc. with a
RecordSource of...
=Price*Qty
which will always yield/display the correct LineTotal.

You don't indicate what fields go into your calculation, so let's use the
same example above,
and you apply it to your field/s

If you MUST "capture" the calculation in a field, remove the calculation
in the
ControlSource of LineTotal, "bind" the control to the LineTotal field from
your table, and
on the AfterUpdate event for EITHER Price OR Qty... run this code
LineTotal = Price * Qty
That way, if you enter (or change) either Price or Qty your LineTotal
field will be updated and "captured"
 
Dave,

If these values are calculated, it's best practice not to
store the value, but to calculate it each time it's
needed. Storing a calculated value should only be done if
you need absolutely to retain a value even if the input
values change, and I can think of few reasons this would
be necessary. If you always have the input values, you can
always reproduce a value. Storing a calculated value is
wrong (in my opinion) for two reasons: 1) More disk space
is required. You really are just wasting the disk space
used. 2) If the input values change, you will have to
recalculate the stored value and save that recaculated
value to the database.

That said, is the form a bound form, if so, just make the
control source on your fields the corresponding field in
your underlying table.

Hope that helps!

Kevin
 
Back
Top