Storing calculated values

[Guest]

Yes, I have read all the cautionary posts and I do fully understand that in
general and for various very sound reasons it is BAD to store calculated
values in tables.
However...I do have just a few critical values (amounts quoted to customers,
amounts invoiced) that I definitely do NOT want ever to be updated as a
result of the base figures being fiddled with. Those totals are for ever.
So how do I get them to store in the tables???
I know there will probably be further words of wisdom against this, but I
promise it is just a handful of them and I definitely want to do it.
Please advise me how - I promise not to tell anyone else
Many thanks
CW

 

[Douglas J. Steele]

You've described a number of situations where storing calculated values make
sense.

If you're got a field on your form where you calculate that value, bind it
to the appropriate field in the recordset.

Alternatively, in the form's BeforeUpdate event, set the appropriate fields
equal to the the appropriate values.

 

[John Vinson]

Yes, I have read all the cautionary posts and I do fully understand that in
general and for various very sound reasons it is BAD to store calculated
values in tables.
However...I do have just a few critical values (amounts quoted to customers,
amounts invoiced) that I definitely do NOT want ever to be updated as a
result of the base figures being fiddled with. Those totals are for ever.
So how do I get them to store in the tables???
I know there will probably be further words of wisdom against this, but I
promise it is just a handful of them and I definitely want to do it.
Please advise me how - I promise not to tell anyone else
Many thanks
CW

<g>

Use Douglas' suggestion of doing it in the Form's BeforeUpdate event;
or "push" the value into a bound control in some appropriate event.
For example, you could put a "Generate Quote" button on the form and
have code in it like

Private Sub cmdQuote_Click()
If IsNull(Me.txtCalcField) Then
MsgBox "PLease fill in the necessary data first", vbOKOnly
Exit Sub
Else
Me.txtStoredField = Me.txtCalcField
End If
End Sub


John W. Vinson[MVP]

 

[Guest]

Thanks to both, for your advice. I shall use it sparingly.
CW