John Account
asked on
OnClick event to manipulate Control Source
On my form, I have 4 textboxes with record sources to theses fields in a query:
WithShipping: [TOTAL]+15
DaysOverDue: DateDiff("d",[DATEVER],Now ())
LateFee: (IIf([DaysOverDue]>30,[TOT AL]*0.02/3 0*[DaysOve rDue],0))
GrandTotal: [WithShipping]+[LateFee]
Now, my question is: I would like the user to use a OnClick event to change the late fee. Lets say the late fee totals to $73.23, and the Collector is talking to customer over the phone and tells customer he will change late fee to be only, say $25.00. I would like the GrandTotal to then change to reflect this. Now I realize this is probably way too complicated to easily do via a query or VBA, so any ideas or suggestions for a similiar solution would be very much appreciated.
Many kind thanks in advance,
John
WithShipping: [TOTAL]+15
DaysOverDue: DateDiff("d",[DATEVER],Now
LateFee: (IIf([DaysOverDue]>30,[TOT
GrandTotal: [WithShipping]+[LateFee]
Now, my question is: I would like the user to use a OnClick event to change the late fee. Lets say the late fee totals to $73.23, and the Collector is talking to customer over the phone and tells customer he will change late fee to be only, say $25.00. I would like the GrandTotal to then change to reflect this. Now I realize this is probably way too complicated to easily do via a query or VBA, so any ideas or suggestions for a similiar solution would be very much appreciated.
Many kind thanks in advance,
John
ASKER
Nope, the collector wouldn't have to go back and agree to another amount. That would basically be like a calculator on the form that does the work for the collector. Oh, but I forgot to mention, I'd need the report that the Collector would print from that form, to reflect the same changes. Ahhhhhhh, so in the Override field, the new value would be entered, and then, use an onclick event to let form know to change which control sources it reads?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, damn, you come up with the most ingenious ideas!
Okay...I'm gonna be working with this thing; got my fingers crossed. Wish me luck. I think I know exactly where you're coming from!
Okay...I'm gonna be working with this thing; got my fingers crossed. Wish me luck. I think I know exactly where you're coming from!
ASKER
Must be doing something wrong here...because the LateFee field is displaying nothing unless there's a value in the OverrideFee field. Here's what I got so far:
Private Sub Form_Current()
If Me!OverrideFee.Value <> "" Then
Me!txtLateFee.ControlSourc e = "LateFee"
Else
Me!txtLateFee.ControlSourc e = "OverrideFee"
End If
Private Sub lblChgLateFee_Click()
Me!txtLateFee.ControlSourc e = "OverrideFee"
Me.txtLateFee.Enabled = True
Me.txtLateFee.Locked = False
Me.lblLateFeeOk.Visible = True
Me.lblChgLateFee.Visible = False
End Sub
Private Sub lblLateFeeOk_Click()
Me.lblChgLateFee.Visible = True
Me.lblLateFeeOk.Visible = False
Me!txtLateFee.ControlSourc e = "LateFee"
Me.txtLateFee.Enabled = False
Me.txtLateFee.Locked = True
End Sub
Private Sub Form_Current()
If Me!OverrideFee.Value <> "" Then
Me!txtLateFee.ControlSourc
Else
Me!txtLateFee.ControlSourc
End If
Private Sub lblChgLateFee_Click()
Me!txtLateFee.ControlSourc
Me.txtLateFee.Enabled = True
Me.txtLateFee.Locked = False
Me.lblLateFeeOk.Visible = True
Me.lblChgLateFee.Visible = False
End Sub
Private Sub lblLateFeeOk_Click()
Me.lblChgLateFee.Visible = True
Me.lblLateFeeOk.Visible = False
Me!txtLateFee.ControlSourc
Me.txtLateFee.Enabled = False
Me.txtLateFee.Locked = True
End Sub
Try:
If IsNull(Me!OverrideFee) Then
Me!txtLateFee.ControlSourc e = "LateFee"
Else
Me!txtLateFee.ControlSourc e = "OverrideFee"
End If
You've actually got the logic the wrong way round - you want to display the LateFee if the OverrideFee is null (or blank), rather than the other way round :)
If IsNull(Me!OverrideFee) Then
Me!txtLateFee.ControlSourc
Else
Me!txtLateFee.ControlSourc
End If
You've actually got the logic the wrong way round - you want to display the LateFee if the OverrideFee is null (or blank), rather than the other way round :)
ASKER
I'm a bit dislexic, lol--thanks for transposing my logic there! Now...I'm about stumped here:
LateFee: (IIf([DaysOverDue]>30,IIf( Not IsNull([OVERRIDE]),[OVERRI DE],[TOTAL ]*0.02/30* [DaysOverD ue]),0))
That LateFee is already in the query, so I'm wondering if should create another field in that query naming it something like LateOverrideFee or create an entirely different query. Not too sure what you mean about nested IIf statement. You mean creating another query and calling it from my LateFee query? Or, if I just add it to existing query, reference it instead of LateFee if [OverrideFee] is not null? Hmmmmnnn...very interesting! Points increased to 350.
LateFee: (IIf([DaysOverDue]>30,IIf(
That LateFee is already in the query, so I'm wondering if should create another field in that query naming it something like LateOverrideFee or create an entirely different query. Not too sure what you mean about nested IIf statement. You mean creating another query and calling it from my LateFee query? Or, if I just add it to existing query, reference it instead of LateFee if [OverrideFee] is not null? Hmmmmnnn...very interesting! Points increased to 350.
If you've already got the nested IIf in the expression, the LateFee field will always show the correct amount - that is to say, if the item is not overdue, it shows 0. If it is, and there is an Override fee, it shows the override fee otherwise it shows the normal calculation. You don't need any code at all - just bind the textbox to the LateFee field and let the expression handle the calculation for you.
ASKER
I'm sitting here in complete awe. That is amazing. Wow. I am so damn elated, it aint even funny.
Thanks, Shane. Sigh...okay, back to work.
Thanks, Shane. Sigh...okay, back to work.
I might create a new field in the table called something like OverrideFee. If this field is not blank, use the field - otherwise use the calculation.