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

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*"));
 
J

John Spencer

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...
 
J

JohnW

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I show a specific date! 1
Calculations from 1 field used in another 2
Update Query to add an value from an expression to an existing field 1
Populate field based on another field's value 1
extracting a field to a new table when another field's total gets to a certain value? 1
Got the custom form to work -- now #error? 5
Automatically select value from a calculated field 1
Why on earth VB.NET gives me this date, "12/30/1899", when it's supposed to give just a time value? 3

Top