Storing a Calculated Field back to the table

  • Thread starter Thread starter Monika
  • Start date Start date
M

Monika

Hi,

Thanks in advance, I know I've seen this somewhere but I
can't find it anywhere.

Store the value of a Calculated field from the form like
QTY*COST appear fine as Total, what's the syntax to get
the "value" to populate a field in the table while the
user still has the form open, so they can see the
calculation, and the table get's populated.

I also understand that storing calculations is not good
practice, this is just an off the issue, that I need to
do.

I know if I use the event afterupdate or mousemove after
the record has saved it and put something like:

Total = Me![QTY]*[COST]

works fine, after the fact, but how do store it, while
your in the "dataentry" or new record phase.

Thanks again,

Monika
 
As you know, it would be *much* better to use a query as the RecordSource
for your form, and type a calculated field into the query:
Amount: [Qty] * [Cost]
so you have the Amount field (read-only) in your form and can easily sum it
in the form footer if desired.

Perhaps your example is a simplification of something that does justify
denormalizing. If you have a good reason for doing so, use the AfterUpdate
event procedure of all the fields involved in the calculation:
Private Sub Qty_AfterUpdate()
Me.Amount = Me.Qty * Me.Cost
End Sub
Private Sub Cost_AfterUpdate()
Call Qty_AfterUpdate
End Sub
 
Allen Browne said:
As you know, it would be *much* better to use a query as the RecordSource
for your form, and type a calculated field into the query:
Amount: [Qty] * [Cost]
so you have the Amount field (read-only) in your form and can easily sum it
in the form footer if desired.

Perhaps your example is a simplification of something that does justify
denormalizing. If you have a good reason for doing so, use the AfterUpdate
event procedure of all the fields involved in the calculation:
Private Sub Qty_AfterUpdate()
Me.Amount = Me.Qty * Me.Cost
End Sub
Private Sub Cost_AfterUpdate()
Call Qty_AfterUpdate
End Sub
Click to expand...
This may be one of the times when it is advisable to do so.
I would store the amount and the cost and still keep the total as a
calculation.

The cost may change over time and if someone requests a reprint of an
invoice that had an item with a cost of $1.00 and the price changes,
somebody will be unhappy.
The accountant always will be and the customer will be happy if the price
goes down.
 
Mike, I think you are advocating the same thing that I am.

The quantity and unit cost both need to be stored to cope with price
changes.
The extended amount (quantity times unit cost) should be calculated (not
stored) in my view, unless the designer sees some meaning for the case where
these things don't add up (e.g. to give a discount or penalty
intentionally).

Mike Painter said:
Allen Browne said:
As you know, it would be *much* better to use a query as the RecordSource
for your form, and type a calculated field into the query:
Amount: [Qty] * [Cost]
so you have the Amount field (read-only) in your form and can easily sum it
in the form footer if desired.

Perhaps your example is a simplification of something that does justify
denormalizing. If you have a good reason for doing so, use the AfterUpdate
event procedure of all the fields involved in the calculation:
Private Sub Qty_AfterUpdate()
Me.Amount = Me.Qty * Me.Cost
End Sub
Private Sub Cost_AfterUpdate()
Call Qty_AfterUpdate
End Sub
Click to expand...
This may be one of the times when it is advisable to do so.
I would store the amount and the cost and still keep the total as a
calculation.

The cost may change over time and if someone requests a reprint of an
invoice that had an item with a cost of $1.00 and the price changes,
somebody will be unhappy.
The accountant always will be and the customer will be happy if the price
goes down.
Click to expand...
 
Back
Top