saving calc result

[alexlds]

I seem to have failed to understand something fairly fundamental.

Say I have a query form with 4 fields, height , width , calculated area:[height]*[width] and area:[calculated area]

In the querie's table and also in the querie's form there are only 3 fields, height, width and area.

When I enter 3 and 4 in the form, the result 12 appears in the area control on the form as it should.

But when I save the form and then look at the table, the width 3 and the height 4 are saved - but the area 12 is not saved and is blank.

As I say, I seem to have failed to understand something fairly fundamental.

But my real question is this - How do I go about saving the result of a calculated query field into a table in such a way that even if the calculation is subsequently altered, the original result remains recorded in the table unless it is deliberately overwritten by saving a new calculated value? Ive been puzzling over this for some time and cant see how to do it. Can anyone help me with that??

 

[jdraw]

You don't save a calculated value in a table.
If you can calculate a value based on stored fields, you use a query with those fields and do the calculation whenever you need to.

 

[alexlds]

ok I suppose what Im asking is how do you store a calculated result if the field info that you used to calculate it are no longer available. Is it possible to make some sort of a "passive copy" of the calculated result and store that? Im probably using the wrong terminology here . .

 

[jdraw]

Maybe you should give a description of what you are trying to do with an example.

Why would the fields used for the calculation no longer be available?

Bottom line is you don't normally store calculated fields in a table in a relational data base.

 

[Trevor G]

ok I suppose what Im asking is how do you store a calculated result if the field info that you used to calculate it are no longer available. Is it possible to make some sort of a "passive copy" of the calculated result and store that? Im probably using the wrong terminology here . .

You could use an append query to another table. But as Jdraw is asking can you paint a better picture of what you are trying to achieve.

 

[apr pillai]

If you still insist on storing the calculated value in your table then write a one line vba routine like the sample given below:

Sample Fields on the Form:
1. Quantity
2. UnitPrice
3. Total (keep the field locked on the form to prevent from manual change)

Write the following vba routine in the UnitPrice LostFocus Event Procedure:

Private Sub UnitPrice_LostFocus()
   Me![Total] = Me![UnitPrice] * Me![Quantity]
End Sub

The calculated value is directly stored on the Total field in the Table.

 

[Pat Hartman]

If you want to store the value (and I strongly recommend that you do NOT), the calculation needs to go in either the BeforeUpdate event of the Form (best) or in BOTH the BeforeUpdate event of the Price and the Quantity since a change in either would cause the total to change.

You can see from apr's suggestion why we recommend that you NOT store calculated values. If you don't get the code in ALL the correct places, you WILL end up with BAD data.

Me.Total = Nz(Me.UnitPrice, 0) * Nz(Me.Quantity,0)
I added the Nz() function for good measure although in this case it probably doesn't matter whether you end up with 0 or null in Total if either UnitPrice or Quantity is null.