Solved

OnClick event to manipulate Control Source

Posted on 2004-09-22
9
674 Views
Last Modified: 2008-02-01
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,[TOTAL]*0.02/30*[DaysOverDue],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
0
Comment
Question by:John Account
  • 5
  • 4
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12129304
What happens though if the collector agrees to change the late fee to $25 but the amount still remains unpaid for a month, for example? Would the collector have to go back and agree another amount or should the normal calculation come into effect?

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

Author Comment

by:John Account
ID: 12129337
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?
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 350 total points
ID: 12129364
No, you wouldn't even need an OnClick event - you could use a nested IIf statement like this:

LateFee: (IIf([DaysOverDue]>30,IIf(Not IsNull([OVERRIDE]),[OVERRIDE],[TOTAL]*0.02/30*[DaysOverDue]),0))
0
 

Author Comment

by:John Account
ID: 12129401
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!
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:John Account
ID: 12129649
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.ControlSource = "LateFee"
Else
Me!txtLateFee.ControlSource = "OverrideFee"
End If

Private Sub lblChgLateFee_Click()
Me!txtLateFee.ControlSource = "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.ControlSource = "LateFee"
Me.txtLateFee.Enabled = False
Me.txtLateFee.Locked = True
End Sub
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12129656
Try:
If IsNull(Me!OverrideFee) Then
Me!txtLateFee.ControlSource = "LateFee"
Else
Me!txtLateFee.ControlSource = "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 :)
0
 

Author Comment

by:John Account
ID: 12129784
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]),[OVERRIDE],[TOTAL]*0.02/30*[DaysOverDue]),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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12129826
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.
0
 

Author Comment

by:John Account
ID: 12129861
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.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
WebBrowser Control - ms/ACCESS 9 32
Combobox issue 4 27
Access Date Query 28 29
Access 2010 report is rearranging data 9 18
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now