Recalculate a field when another field's value changes - JCW

J

JohnW

I have the following fields I'm working with; they are all on the same Form
which comes from a query.

MultiClassDisc
TotalTuitions

TotalTuitions is calculated by the sum of (4) other Tuition fields. I have
a check box to trigger the calculation of the MultiClassDisc field which
works off of the following code.

If MultiClass.Value = vbTrue Then
MultiClassDisc.Value = TotalTuitions * 0.1
Else
MultiClassDisc.Value = 0
End If

It works Ok.....when I check the box it calculates MultiClassDisc to be 10%
of the TotalTuitions and when I click the box off it changes the value back
to zero.

The issue I need help with is that the TotalTuitions value will change
sometimes and when it does if the box is checked the MultiClassDisc field
will not change automatically to reflect the new 10% value. If I uncheck the
box and then re-check it will show the new value but obviously I need it to
work automatically.

Any suggestions? Thank you
 
M

Marshall Barton

JohnW said:
I have the following fields I'm working with; they are all on the same Form
which comes from a query.

MultiClassDisc
TotalTuitions

TotalTuitions is calculated by the sum of (4) other Tuition fields. I have
a check box to trigger the calculation of the MultiClassDisc field which
works off of the following code.

If MultiClass.Value = vbTrue Then
MultiClassDisc.Value = TotalTuitions * 0.1
Else
MultiClassDisc.Value = 0
End If

It works Ok.....when I check the box it calculates MultiClassDisc to be 10%
of the TotalTuitions and when I click the box off it changes the value back
to zero.

The issue I need help with is that the TotalTuitions value will change
sometimes and when it does if the box is checked the MultiClassDisc field
will not change automatically to reflect the new 10% value. If I uncheck the
box and then re-check it will show the new value but obviously I need it to
work automatically.
Click to expand...

If you do not need to allow users to change the
MultiClassDisc value, then I think you should avoid using
code to calculate the discount. Instead, use an expression
in the MultiClassDisc text box:
=IIf(MultiClass, TotalTuitions * 0.1, 0.0)

Because the expression refers to both the MultiClass and
TotalTuitions controls, the recalculation should be
automatic whenever either one is changed.
 
J

JohnW

Marshall

Unfortunately the MultiClassDisc field will be changed from time to time.
That's why I wanted to use the checkbox and the code to be able to turn it
off and on as needed.
 
B

Bob Barrows

JohnW said:
I have the following fields I'm working with; they are all on the
same Form which comes from a query.

MultiClassDisc
TotalTuitions

TotalTuitions is calculated by the sum of (4) other Tuition fields.
I have a check box to trigger the calculation of the MultiClassDisc
field which works off of the following code.

If MultiClass.Value = vbTrue Then
MultiClassDisc.Value = TotalTuitions * 0.1
Else
MultiClassDisc.Value = 0
End If

It works Ok.....when I check the box it calculates MultiClassDisc to
be 10% of the TotalTuitions and when I click the box off it changes
the value back to zero.

The issue I need help with is that the TotalTuitions value will change
sometimes and when it does if the box is checked the MultiClassDisc
field will not change automatically to reflect the new 10% value. If
I uncheck the box and then re-check it will show the new value but
obviously I need it to work automatically.

Any suggestions? Thank you
Click to expand...

Create an Onchange event handler for the TotalTuitions control. I would
suggest copying the above code into a new function in form's module, and
then calling the function from the event handler subs for both controls.
 
M

Marshall Barton

JohnW said:
Unfortunately the MultiClassDisc field will be changed from time to time.
That's why I wanted to use the checkbox and the code to be able to turn it
off and on as needed.
Click to expand...


Ok, then call the checkbox's code from the TotalTuitions
text box's AfterUpdate event:

checkbox_Click

If the TotalTuitions text box contains an expression, then
you will have to do that calculation in code that is called
from each of the (four?) text boxes.
 
Top