Adding the value of a calculated field to a table

[lindaclark30]

Can anyone please advise me how to place the the value of a calculate
field in a query into the value of a field in a separate table?

Many thank

 

[R. Hicks]

Normally calculated values are not stored in a table ...
There are only special cases where it is concidered allowable to d
this.
If the values the calculation is based on are stored .. you calculat
the result "on demand" when the result is needed.

If any value the calculation is based on should get altered for an
reason ... a "static" stored calculated value would be incorrect.

RD

 

[DanK]

What you're asking is really vague!

1) You could use DLookup.

2) You could create a QUERY that returns one record, the
one you're interested in, and plug that value into the
TABLE's field, say, via a form. (ie. Me!ControlX = Me!
ControlY) where X is a control based on your QUERY and Y
is a control linked to the TABLE's field.

3) You could also use a (global) variable.

Are you trying to plug the calculated value based on ONE
record into many record fields of a table, or are there
many values each based on its own record within the QUERY,
or what????

 

[james.read]

Hi, I am looking for a solution similar to the original
post. I need the value of my calculated control
(ctrlTotalMiles)to be written to the a table field
(TotalMiles) after the calculated control is updated. So:
I think I need to write the updating code in the
after_update event procedure of the control. This way if
the control value is updated, so is the table and the
record is not static. Is this better practice and if so,
what code would do this?

Thanks, intrigued UK user.

 

[John Vinson]

Hi, I am looking for a solution similar to the original
post. I need the value of my calculated control
(ctrlTotalMiles)to be written to the a table field
(TotalMiles) after the calculated control is updated. So:
I think I need to write the updating code in the
after_update event procedure of the control. This way if
the control value is updated, so is the table and the
record is not static. Is this better practice and if so,
what code would do this?

It's better practice to *not store derived data in the table AT ALL*.

The problem is that if the values underlying the calculation are
edited, whether through the Form, through an update query, or any
other means, the value of TotalMiles stored in the table is now
*WRONG*, and there's no obvious way to determine that it is wrong. For
that matter, once a value is stored in TotalMiles, that value itself
can be edited, so that (again) it doesn't reflect the actual sum.

If this redundancy and risk of storing invalid data is acceptable to
you (it sure wouldn't be to me!) then use the Form's BeforeUpdate
event rather than the textbox's AfterUpdate event; the AfterUpdate
event of a calculated control will never fire, since that event fires
only when the user types something into the control.