If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Recalculate a field when another field's value changes - JCW
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*")); -- JCW |
#2
|
|||
|
|||
Recalculate a field when another field's value changes - JCW
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 JohnW wrote: 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*")); |
#3
|
|||
|
|||
Recalculate a field when another field's value changes - JCW
thanks John...I'll give these a try.....and it's appreciated not mentioning
the faulty structure, haha JCW "John Spencer" wrote: 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 JohnW wrote: 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*")); . |
Thread Tools | |
Display Modes | |
|
|