Storing a Calculated Field back to the table

[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

 

[Allen Browne]

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

 

[Mike Painter]

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

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.

 

[Allen Browne]

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).

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

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.