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

  • Thread starter Thread starter JohnW
  • Start date Start date
J

JohnW

I know I have asked this one before but so far I haven't found anything to
work.

I have the following fields I'm working with; they are all on the same
Billing 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. I have tried putting the code for the check box in as
an event for TotalTuitions in both the After Update and the On Change but
this hasn’t worked. I have also tried to run a macro (MsgBox) from these
two event lines in TotalTuitions but the macro does not run when
TotalTuitions changes so it looks like the change is not triggering anything.

Any suggestions? Here is the SQL view of the query that this billing Form
is from and where the calculation for TotalTuitions happens.


SELECT ECG.Gymnasts, ECG.BillingName, ECG.BillingAddress,
ECG.luBillingCity, ECG.BillingState, ECG.luBillingZip, ECG.StartDate,
ECG.TodayDate, DateDiff("d",[StartDate],[TodayDate]) AS Days, ECG.Hours,
ECG.Tuition, Switch([Days]>=365 And [Days]<730,0.05,[Days]>=730 And
[Days]<1095,0.1,[Days]>=1095 And [Days]<1460,0.15,[Days]>=1460 And
[Days]<1825,0.2,[Days]>=1825 And [Days]<2190,0.25,[Days]>=2190 And
[Days]<2555,0.3,[Days]>=2555 And [Days]<2920,0.35,[Days]>=2920 And
[Days]<3285,0.4,[Days]>=3285 And [Days]<3650,0.45,[Days]>=3650 And
[Days]<4015,0.5,[Days]>4015,0.5,True,1) AS CSP, ECG.MultiClassDisc,
ECG.MultiChildDisc, ECG.MilitaryDisc, ECG.MiscDisc,
([TotalTuitions])-[MultiClassDisc]-[MultiChildDisc]-[MilitaryDisc]-[MiscDisc]
AS MonthlyTotal, ECG.[Late Fee], ECG.BalanceOverDue,
[MonthlyTotal]+[BalanceOverDue]+[Late Fee]+[MiscPay] AS BalanceDue,
ECG.Gymnasts2, ECG.Gymnasts3, ECG.Gymnasts3, ECG.StartDate2, ECG.StartDate3,
ECG.StartDate4, ECG.Tuition2, ECG.Tuition3, ECG.Tuition4, Switch([Days2]>=365
And [Days2]<730,0.05,[Days2]>=730 And [Days2]<1095,0.1,[Days2]>=1095 And
[Days2]<1460,0.15,[Days2]>=1460 And [Days2]<1825,0.2,[Days2]>=1825 And
[Days2]<2190,0.25,[Days2]>=2190 And [Days2]<2555,0.3,[Days2]>=2555 And
[Days2]<2920,0.35,[Days2]>=2920 And [Days2]<3285,0.4,[Days2]>=3285 And
[Days2]<3650,0.45,[Days2]>=3650 And [Days2]<4015,0.5,[Days2]>4015,0.5,True,1)
AS CSP2, Switch([Days3]>=365 And [Days3]<730,0.05,[Days3]>=730 And
[Days3]<1095,0.1,[Days3]>=1095 And [Days3]<1460,0.15,[Days3]>=1460 And
[Days3]<1825,0.2,[Days3]>=1825 And [Days3]<2190,0.25,[Days3]>=2190 And
[Days3]<2555,0.3,[Days3]>=2555 And [Days3]<2920,0.35,[Days3]>=2920 And
[Days3]<3285,0.4,[Days3]>=3285 And [Days3]<3650,0.45,[Days3]>=3650 And
[Days3]<4015,0.5,[Days3]>4015,0.5,True,1) AS CSP3, Switch([Days4]>=365 And
[Days4]<730,0.05,[Days4]>=730 And [Days4]<1095,0.1,[Days4]>=1095 And
[Days4]<1460,0.15,[Days4]>=1460 And [Days4]<1825,0.2,[Days4]>=1825 And
[Days4]<2190,0.25,[Days4]>=2190 And [Days4]<2555,0.3,[Days4]>=2555 And
[Days4]<2920,0.35,[Days4]>=2920 And [Days4]<3285,0.4,[Days4]>=3285 And
[Days4]<3650,0.45,[Days4]>=3650 And [Days4]<4015,0.5,[Days4]>4015,0.5,True,1)
AS CSP4, ECG.Hours2, ECG.Hours3, ECG.Hours4, ECG.Gymnasts4,
DateDiff("d",[StartDate2],[TodayDate]) AS Days2,
DateDiff("d",[StartDate3],[TodayDate]) AS Days3,
DateDiff("d",[StartDate4],[TodayDate]) AS Days4, ECG.[Session Payment],
ECG.BillComments, ECG.InvComments, IIf([CSP]<1,[Tuition]*[CSP],0) AS CSPCalc,
IIf([CSP2]<1,[Tuition2]*[CSP2],0) AS CSPCalc2,
IIf([CSP3]<1,[Tuition3]*[CSP3],0) AS CSPCalc3,
IIf([CSP4]<1,[Tuition4]*[CSP4],0) AS CSPCalc4,
IIf([Tuition]>0,[Tuition]-[CSPCalc],0) AS TotTuition,
IIf([Tuition2]>0,[Tuition2]-[CSPCalc2],0) AS TotTuition2,
IIf([Tuition3]>0,[Tuition3]-[CSPCalc3],0) AS TotTuition3,
IIf([Tuition4]>0,[Tuition4]-[CSPCalc4],0) AS TotTuition4, ECG.lupClass,
ECG.MiscPay, ECG.MiscDiscDesc, ECG.StartDateDesc, ECG.MiscPayDesc, ECG.Email,
ECG.luWaiveLateFee, ([TotTuition]+[TotTuition2]+[TotTuition3]+[TotTuition4])
AS TotalTuitions, ECG.MultiClassCheck, ECG.MultiChildCheck, ECG.MilitaryCheck
FROM ECG
WHERE (((ECG.Gymnasts)=[Forms]![Gymnasts List]![cboGymnasts]) AND
((ECG.lupActive) Like "*yes*"));
 
Since TotalTuitions is a calculated value, you actually need to use code in
the after update event of all the tuition controls. OR you can try changing
MultiClassDisc to use an expression. Assuming that

=Abs((Nz([Tuition1]) + Nz([Tuition2]) + Nz([Tuition3]) +
Nz([Tuition4]))*.1*[MultiClass])

You could add a sub to the form and then in the after update event of each of
the tuition controls and in the on current event of the form - call the sub.

Private Sub CalcMultiTotal()
Me.TotalTuitions = Nz(Tuition1) + Nz(Tuition2) + Nz(Tuition3) + Nz(Tuition4)
If Me.MultiClass = True Then
Me.MultiClassDisc = TotalTuitions * 0.1
Else
Me.MultiClassDisc = 0
End If
End Sub


Even better if Multiclass is always TRUE when two or more Tuition fields have
a positive value you could just check for that and automatically set the
checkbox plus the TotalTuitions plus the MultiClassDisc

Private Sub CalcMultiTotal()
Me.TotalTuitions = Nz(Tuition1) + Nz(Tuition2) + Nz(Tuition3) + Nz(Tuition4)

IF Abs(Nz(Me.[Tuition1],0)>0) + Nz(Me.[Tuition2],0)>0) +
Nz(Me.[Tuition3],0)>0) + Nz(Me.[Tuition4],0)>0)) > 1 THEN
Me.MultiClass = True Then
Me.MultiClassDisc = TotalTuitions * 0.1
Else
Me.MulcitClass = False
Me.MultiClassDisc = 0
End If
End Sub

I won't even talk about what seems to be a faulty structure with repeating fields.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I know I have asked this one before but so far I haven't found anything to
work.

I have the following fields I'm working with; they are all on the same
Billing 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. I have tried putting the code for the check box in as
an event for TotalTuitions in both the After Update and the On Change but
this hasn’t worked. I have also tried to run a macro (MsgBox) from these
two event lines in TotalTuitions but the macro does not run when
TotalTuitions changes so it looks like the change is not triggering anything.

Any suggestions? Here is the SQL view of the query that this billing Form
is from and where the calculation for TotalTuitions happens.


SELECT ECG.Gymnasts, ECG.BillingName, ECG.BillingAddress,
ECG.luBillingCity, ECG.BillingState, ECG.luBillingZip, ECG.StartDate,
ECG.TodayDate, DateDiff("d",[StartDate],[TodayDate]) AS Days, ECG.Hours,
ECG.Tuition, Switch([Days]>=365 And [Days]<730,0.05,[Days]>=730 And
[Days]<1095,0.1,[Days]>=1095 And [Days]<1460,0.15,[Days]>=1460 And
[Days]<1825,0.2,[Days]>=1825 And [Days]<2190,0.25,[Days]>=2190 And
[Days]<2555,0.3,[Days]>=2555 And [Days]<2920,0.35,[Days]>=2920 And
[Days]<3285,0.4,[Days]>=3285 And [Days]<3650,0.45,[Days]>=3650 And
[Days]<4015,0.5,[Days]>4015,0.5,True,1) AS CSP, ECG.MultiClassDisc,
ECG.MultiChildDisc, ECG.MilitaryDisc, ECG.MiscDisc,
([TotalTuitions])-[MultiClassDisc]-[MultiChildDisc]-[MilitaryDisc]-[MiscDisc]
AS MonthlyTotal, ECG.[Late Fee], ECG.BalanceOverDue,
[MonthlyTotal]+[BalanceOverDue]+[Late Fee]+[MiscPay] AS BalanceDue,
ECG.Gymnasts2, ECG.Gymnasts3, ECG.Gymnasts3, ECG.StartDate2, ECG.StartDate3,
ECG.StartDate4, ECG.Tuition2, ECG.Tuition3, ECG.Tuition4, Switch([Days2]>=365
And [Days2]<730,0.05,[Days2]>=730 And [Days2]<1095,0.1,[Days2]>=1095 And
[Days2]<1460,0.15,[Days2]>=1460 And [Days2]<1825,0.2,[Days2]>=1825 And
[Days2]<2190,0.25,[Days2]>=2190 And [Days2]<2555,0.3,[Days2]>=2555 And
[Days2]<2920,0.35,[Days2]>=2920 And [Days2]<3285,0.4,[Days2]>=3285 And
[Days2]<3650,0.45,[Days2]>=3650 And [Days2]<4015,0.5,[Days2]>4015,0.5,True,1)
AS CSP2, Switch([Days3]>=365 And [Days3]<730,0.05,[Days3]>=730 And
[Days3]<1095,0.1,[Days3]>=1095 And [Days3]<1460,0.15,[Days3]>=1460 And
[Days3]<1825,0.2,[Days3]>=1825 And [Days3]<2190,0.25,[Days3]>=2190 And
[Days3]<2555,0.3,[Days3]>=2555 And [Days3]<2920,0.35,[Days3]>=2920 And
[Days3]<3285,0.4,[Days3]>=3285 And [Days3]<3650,0.45,[Days3]>=3650 And
[Days3]<4015,0.5,[Days3]>4015,0.5,True,1) AS CSP3, Switch([Days4]>=365 And
[Days4]<730,0.05,[Days4]>=730 And [Days4]<1095,0.1,[Days4]>=1095 And
[Days4]<1460,0.15,[Days4]>=1460 And [Days4]<1825,0.2,[Days4]>=1825 And
[Days4]<2190,0.25,[Days4]>=2190 And [Days4]<2555,0.3,[Days4]>=2555 And
[Days4]<2920,0.35,[Days4]>=2920 And [Days4]<3285,0.4,[Days4]>=3285 And
[Days4]<3650,0.45,[Days4]>=3650 And [Days4]<4015,0.5,[Days4]>4015,0.5,True,1)
AS CSP4, ECG.Hours2, ECG.Hours3, ECG.Hours4, ECG.Gymnasts4,
DateDiff("d",[StartDate2],[TodayDate]) AS Days2,
DateDiff("d",[StartDate3],[TodayDate]) AS Days3,
DateDiff("d",[StartDate4],[TodayDate]) AS Days4, ECG.[Session Payment],
ECG.BillComments, ECG.InvComments, IIf([CSP]<1,[Tuition]*[CSP],0) AS CSPCalc,
IIf([CSP2]<1,[Tuition2]*[CSP2],0) AS CSPCalc2,
IIf([CSP3]<1,[Tuition3]*[CSP3],0) AS CSPCalc3,
IIf([CSP4]<1,[Tuition4]*[CSP4],0) AS CSPCalc4,
IIf([Tuition]>0,[Tuition]-[CSPCalc],0) AS TotTuition,
IIf([Tuition2]>0,[Tuition2]-[CSPCalc2],0) AS TotTuition2,
IIf([Tuition3]>0,[Tuition3]-[CSPCalc3],0) AS TotTuition3,
IIf([Tuition4]>0,[Tuition4]-[CSPCalc4],0) AS TotTuition4, ECG.lupClass,
ECG.MiscPay, ECG.MiscDiscDesc, ECG.StartDateDesc, ECG.MiscPayDesc, ECG.Email,
ECG.luWaiveLateFee, ([TotTuition]+[TotTuition2]+[TotTuition3]+[TotTuition4])
AS TotalTuitions, ECG.MultiClassCheck, ECG.MultiChildCheck, ECG.MilitaryCheck
FROM ECG
WHERE (((ECG.Gymnasts)=[Forms]![Gymnasts List]![cboGymnasts]) AND
((ECG.lupActive) Like "*yes*"));
Click to expand...
 
thanks John...I'll give these a try.....and it's appreciated not mentioning
the faulty structure, haha

JCW


John Spencer said:
Since TotalTuitions is a calculated value, you actually need to use code in
the after update event of all the tuition controls. OR you can try changing
MultiClassDisc to use an expression. Assuming that

=Abs((Nz([Tuition1]) + Nz([Tuition2]) + Nz([Tuition3]) +
Nz([Tuition4]))*.1*[MultiClass])

You could add a sub to the form and then in the after update event of each of
the tuition controls and in the on current event of the form - call the sub.

Private Sub CalcMultiTotal()
Me.TotalTuitions = Nz(Tuition1) + Nz(Tuition2) + Nz(Tuition3) + Nz(Tuition4)
If Me.MultiClass = True Then
Me.MultiClassDisc = TotalTuitions * 0.1
Else
Me.MultiClassDisc = 0
End If
End Sub


Even better if Multiclass is always TRUE when two or more Tuition fields have
a positive value you could just check for that and automatically set the
checkbox plus the TotalTuitions plus the MultiClassDisc

Private Sub CalcMultiTotal()
Me.TotalTuitions = Nz(Tuition1) + Nz(Tuition2) + Nz(Tuition3) + Nz(Tuition4)

IF Abs(Nz(Me.[Tuition1],0)>0) + Nz(Me.[Tuition2],0)>0) +
Nz(Me.[Tuition3],0)>0) + Nz(Me.[Tuition4],0)>0)) > 1 THEN
Me.MultiClass = True Then
Me.MultiClassDisc = TotalTuitions * 0.1
Else
Me.MulcitClass = False
Me.MultiClassDisc = 0
End If
End Sub

I won't even talk about what seems to be a faulty structure with repeating fields.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I know I have asked this one before but so far I haven't found anything to
work.

I have the following fields I'm working with; they are all on the same
Billing 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. I have tried putting the code for the check box in as
an event for TotalTuitions in both the After Update and the On Change but
this hasn’t worked. I have also tried to run a macro (MsgBox) from these
two event lines in TotalTuitions but the macro does not run when
TotalTuitions changes so it looks like the change is not triggering anything.

Any suggestions? Here is the SQL view of the query that this billing Form
is from and where the calculation for TotalTuitions happens.


SELECT ECG.Gymnasts, ECG.BillingName, ECG.BillingAddress,
ECG.luBillingCity, ECG.BillingState, ECG.luBillingZip, ECG.StartDate,
ECG.TodayDate, DateDiff("d",[StartDate],[TodayDate]) AS Days, ECG.Hours,
ECG.Tuition, Switch([Days]>=365 And [Days]<730,0.05,[Days]>=730 And
[Days]<1095,0.1,[Days]>=1095 And [Days]<1460,0.15,[Days]>=1460 And
[Days]<1825,0.2,[Days]>=1825 And [Days]<2190,0.25,[Days]>=2190 And
[Days]<2555,0.3,[Days]>=2555 And [Days]<2920,0.35,[Days]>=2920 And
[Days]<3285,0.4,[Days]>=3285 And [Days]<3650,0.45,[Days]>=3650 And
[Days]<4015,0.5,[Days]>4015,0.5,True,1) AS CSP, ECG.MultiClassDisc,
ECG.MultiChildDisc, ECG.MilitaryDisc, ECG.MiscDisc,
([TotalTuitions])-[MultiClassDisc]-[MultiChildDisc]-[MilitaryDisc]-[MiscDisc]
AS MonthlyTotal, ECG.[Late Fee], ECG.BalanceOverDue,
[MonthlyTotal]+[BalanceOverDue]+[Late Fee]+[MiscPay] AS BalanceDue,
ECG.Gymnasts2, ECG.Gymnasts3, ECG.Gymnasts3, ECG.StartDate2, ECG.StartDate3,
ECG.StartDate4, ECG.Tuition2, ECG.Tuition3, ECG.Tuition4, Switch([Days2]>=365
And [Days2]<730,0.05,[Days2]>=730 And [Days2]<1095,0.1,[Days2]>=1095 And
[Days2]<1460,0.15,[Days2]>=1460 And [Days2]<1825,0.2,[Days2]>=1825 And
[Days2]<2190,0.25,[Days2]>=2190 And [Days2]<2555,0.3,[Days2]>=2555 And
[Days2]<2920,0.35,[Days2]>=2920 And [Days2]<3285,0.4,[Days2]>=3285 And
[Days2]<3650,0.45,[Days2]>=3650 And [Days2]<4015,0.5,[Days2]>4015,0.5,True,1)
AS CSP2, Switch([Days3]>=365 And [Days3]<730,0.05,[Days3]>=730 And
[Days3]<1095,0.1,[Days3]>=1095 And [Days3]<1460,0.15,[Days3]>=1460 And
[Days3]<1825,0.2,[Days3]>=1825 And [Days3]<2190,0.25,[Days3]>=2190 And
[Days3]<2555,0.3,[Days3]>=2555 And [Days3]<2920,0.35,[Days3]>=2920 And
[Days3]<3285,0.4,[Days3]>=3285 And [Days3]<3650,0.45,[Days3]>=3650 And
[Days3]<4015,0.5,[Days3]>4015,0.5,True,1) AS CSP3, Switch([Days4]>=365 And
[Days4]<730,0.05,[Days4]>=730 And [Days4]<1095,0.1,[Days4]>=1095 And
[Days4]<1460,0.15,[Days4]>=1460 And [Days4]<1825,0.2,[Days4]>=1825 And
[Days4]<2190,0.25,[Days4]>=2190 And [Days4]<2555,0.3,[Days4]>=2555 And
[Days4]<2920,0.35,[Days4]>=2920 And [Days4]<3285,0.4,[Days4]>=3285 And
[Days4]<3650,0.45,[Days4]>=3650 And [Days4]<4015,0.5,[Days4]>4015,0.5,True,1)
AS CSP4, ECG.Hours2, ECG.Hours3, ECG.Hours4, ECG.Gymnasts4,
DateDiff("d",[StartDate2],[TodayDate]) AS Days2,
DateDiff("d",[StartDate3],[TodayDate]) AS Days3,
DateDiff("d",[StartDate4],[TodayDate]) AS Days4, ECG.[Session Payment],
ECG.BillComments, ECG.InvComments, IIf([CSP]<1,[Tuition]*[CSP],0) AS CSPCalc,
IIf([CSP2]<1,[Tuition2]*[CSP2],0) AS CSPCalc2,
IIf([CSP3]<1,[Tuition3]*[CSP3],0) AS CSPCalc3,
IIf([CSP4]<1,[Tuition4]*[CSP4],0) AS CSPCalc4,
IIf([Tuition]>0,[Tuition]-[CSPCalc],0) AS TotTuition,
IIf([Tuition2]>0,[Tuition2]-[CSPCalc2],0) AS TotTuition2,
IIf([Tuition3]>0,[Tuition3]-[CSPCalc3],0) AS TotTuition3,
IIf([Tuition4]>0,[Tuition4]-[CSPCalc4],0) AS TotTuition4, ECG.lupClass,
ECG.MiscPay, ECG.MiscDiscDesc, ECG.StartDateDesc, ECG.MiscPayDesc, ECG.Email,
ECG.luWaiveLateFee, ([TotTuition]+[TotTuition2]+[TotTuition3]+[TotTuition4])
AS TotalTuitions, ECG.MultiClassCheck, ECG.MultiChildCheck, ECG.MilitaryCheck
FROM ECG
WHERE (((ECG.Gymnasts)=[Forms]![Gymnasts List]![cboGymnasts]) AND
((ECG.lupActive) Like "*yes*"));
Click to expand...
.
Click to expand...
 
Back
Top