Displaying Calculation In Form

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

How do I set up a form where 2 values are input and then
the 3rd value is displayed on the form once the first 2
values are input----then the 3rd value is saved in the data
base as a field.

Thanks
Paul
 
Make the calculation a variant function that takes in the
values of both fields in the form and returns null if
either (or both) of the fields is null, and the value you
want if neither is null. Put an AfterUpdate event into
both of the first two fields that assigns the third field
the value of the function.

I.e.:
Private Function calculate(val1, val2)

if IsNull(val1) Or IsNull(val2) then
calculate = Null
Else
...(your calculations here)
calculate = (your calculated value)
End If

End Function

Private Sub Field1_AfterUpdate()

field3.Value = calculate(field1.Value, field2.Value)

End Sub

Second Sub also goes for Field2

HTH

Chris
 
How do I set up a form where 2 values are input and then
the 3rd value is displayed on the form once the first 2
values are input----then the 3rd value is saved in the data
base as a field.
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.

Once in a while (e.g. to capture a value as of a point in time) you do
want to store the value; one way to do so is to have TWO textboxes on
the form. One would be visible and have as its Control Source the
desired expression; the second (which can be invisible) would be bound
to the table field. In the Form's BeforeUpdate event you could put
code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me.txtBoundControl = Me.txtCalcControl
End Sub
 
Back
Top