Calculations to table

  • Thread starter Thread starter RAH
  • Start date Start date
R

RAH

I'm trying to save a calculated field to a field in a
table. The calculations are as simple as:
Field1+Field2+Field3, etc. But the entries may be one
entry one day another entry on another day, and so on.
The calculation field works fine however, I have not been
able to save this to my table. I tried using the
following code with "Before Update", "After Update", "On
Change", etc. The only one that seems to work is "Got
Focus":
Private Sub TotalUnits2_GotFocus()
Me.TotalUnits.Value = Me.TotalUnits2
End Sub

However, I have to enter the field before it will add the
totals to the table. Does anyone have any suggestions?
 
I'm trying to save a calculated field to a field in a
table. The calculations are as simple as:
Field1+Field2+Field3, etc. But the entries may be one
entry one day another entry on another day, and so on.
The calculation field works fine however, I have not been
able to save this to my table. I tried using the
following code with "Before Update", "After Update", "On
Change", etc. The only one that seems to work is "Got
Focus":
Private Sub TotalUnits2_GotFocus()
Me.TotalUnits.Value = Me.TotalUnits2
End Sub

However, I have to enter the field before it will add the
totals to the table. Does anyone have any suggestions?
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you have some GOOD REASON to save the value anyway, the simplest
event to do so is the Form's BeforeUpdate event which fires after the
form has been filled out but before the data is saved to disk. E.g.

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any validation code goes first>
Me.TotalUnits = Me.TotalUnits2
End Sub

assuming that TotalUnits is the bound control and TotalUnits2 is the
unbound calculated one.
 
John,
Thanks for the info. You made a very good point good
point, I'll try setting my report to reflect a calculated
field.

RAH
 
Back
Top