Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

News      User Comments     History     Notify Me

Microsoft Access 305
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Accounts Receivable Aging, vbYesNoCancel MsgBox, BeforeUpdate Event, Cancelling Events, more

Permanent Link
Course Link: Microsoft Access 305
Keywords: access vba vbyesnocancel msgbox beforeupdate
Post Reply

Are you Sure Comment from Shallena Ayers @ 1/8/2018
Alex... what does your response mean?  I get the same warning messages as Vicki.   I copy and pasted what your wrote but got an error message.  It doesnt like the word 'code'.  if i take that word out and just have the 2 docmd statements i still get the error messages.  Can you please clarify what you mean by your response?  What do we need to do to not have the error messages pop up.  Thank you

Reply from Alex Hedley:

I updated the response to say
[Type your code here]
I assumed you would know "Code" means replace with the code you have used or want to use
You need the SQL statement that would show a message between those two statements since you are say, turn off warnings, run code, turn back on warnings, these are system wide so if you turn them off and forget to turn them back on you will never get any more warnings.
Show Just This Thread        Post Reply
OldValue Comment from Michael C @ 11/6/2016
Hi Alex,
I'm not able to replicate having the message box display the old value for the discount rate. At approximately 4:30 into the video, Richard inserts percentdiscount.oldvalue into the code. When I try this, the message box displays the new value that I am changing to. Any idea why this might be happening?


Reply from Alex Hedley:

MS: OldValue

Is the Field bound?
Show Just This Thread        Post Reply
Change the Tax Comment from Emad @ 2/16/2016
Hello, If I change the Tax for example 5% and I click on Cancel or No, the Default value changed to 5%, the Default value should be old value, how can I do it?

Reply from Alex Hedley:

MyResponse = MsgBox(“Are you sure you want to change the “ & _ “discount for the entire order?”, _
vbYesNo, “Are you sure?”)

Did you then check for the Response
If MyResponse = vbYes ...
Show Just This Thread        Post Reply
Are you Sure Comment from vicki Hudson @ 10/30/2015
at 11:00 we start setting msgbox for "Are you Sure?" I am automatically getting asked yes/No/Help "You are about to run update query that will modify data in your table."  then again "You are about to update x rows".  Did I miss a step to make these messages not appear?  Should I follow the help instructions to "clear action queries check box"?

Reply from Alex Hedley:

DoCmd.SetWarnings False

[Type your code here]

DoCmd.SetWarnings True
Show Just This Thread        Post Reply
Microsoft Access 305 Comment from CHARLES FULGHAM @ 3/29/2015
Okay, figured out the issue.  I was opening the Acts Rcvbl Report by double clicking it from the All Objects, which opens it up into "Report View" with zero's displayed, open switching over to "Print Preview", zero's not displayed.  The Force was telling me all along it was something simple.  Thanks for everything.  Loving these courses!

Reply from Alex Hedley:

Glad you figured it out :)
Show Just This Thread        Post Reply
Microsoft Access 305 Comment from CHARLES FULGHAM @ 3/29/2015
Mr. Hedley, just now following up on my comment.  Didn't see Clark's response however, for some reason, during Developer:310 as we revisited the Acts Rcvble Report, upon open, now all the zero's are now NOT being displayed.  I'm sure one of these days at some random point (like while grocery shopping) I'll have an A'Ha Moment!  Thanks for the follow up.
Show Just This Thread        Post Reply
Microsoft Access 305 Comment from Clay Fulgham @ 3/23/2015
Same problem happened to me as Jim Gray.  For some reason, the code didn't work as basic as it is, no errors, and the zero's are still being displayed.  I tried for about 30 mins to fix it, but decided to move on.  Great Lessons!!

Reply from Alex Hedley:

Have you seen Mark Clark's response about the default view that is shown?
Show Just This Thread        Post Reply
expression is not valid Comment from Sandra Bischler @ 12/12/2012
I figured it out. I do not know if it has anything to do with the french version, or not, but I changed the comma (,) with a semi colon (;)and now it is working!!!

Reply from Richard Rost:

Yes, that definitely sounds like a language-specific version problem. I've NEVER used a semicolon in that situation.
Show Just This Thread        Post Reply
expression is not valid Comment from Sandra Bischler @ 12/11/2012
Hi, I use Access 2007, and I get an error message when I try to enter these codes in the AccountReceivableAgeQ:

Current:IIf([DueDate]>=Date(), [OrderTotal],0)


LessThan60 :IIf([DueDate]=Date()-60,[OrderTotal],0)

The error message says that the expression is not valid. That I am trying to enter an operation without an operateur.... (I have a french version of Access, so I think it is what it's written....)

Reply from Richard Rost:

I can tell you that if you type in the expressions EXACTLY as they're shown in the video, they will work just fine with Access 2007. I've tested this with 2003, 2007, and 2010.

I can see from your copy/paste that you did type in LessThan30 and LessThan60 wrong. You've got just = in there and not <=. Although that wouldn't generate an error as it's written, it's incorrect, so it leads me to suspect you've got something else typed in wrong too.

Not sure if your language has anything to do with it. I've never used any of the foreign-language versions of Access, but I doubt that's the problem.
Show Just This Thread        Post Reply
Comment from Benjamin Chua @ 10/21/2011
I dont know if its me or the calculations
Timer 5:55
If ExtPrice = $45
Discount = $4.5  
if SalesTaxRate is .0875
the sales tax = 3.54

why you added the ExtPrice+SalesTax

isn't it should be
Discounted Price of 40.5 + SalesTax = $44.04?

Reply from Richard Rost:

You're right. I fix the problem in a little bit. Keep watching.
Show Just This Thread        Post Reply
Comment from Kevin O'Malley @ 6/21/2011
Richard, (lesson 305.5) in Access 2007, after I change the percent discount, I get the vbYesNoCancel msg box. If I say yes, I then get another mesg box that says 'you are about to update three records. do you want to continue?' If I say no, I get an error. How do I get rid of the second message box?
Show Just This Thread        Post Reply
Comment from Kevin O'Malley @ 6/21/2011
Hi Richard,  in Lesson 305 4. at 6:38 you show "PercentDiscount" in the action query and then you add a coupon to the order then return to the query design but Percent Discount is now missing. It then seems like you start the lesson over.  It seems a little disjointed. Did I miss something?
Show Just This Thread        Post Reply
Microsoft Access 305 Comment from Mark Clark @ 5/22/2011
Hi Richard

I was just working through Acts Rcvbl 2 in Access 2010 and had the same problem that Jim Gray reported about the "Current" text box property of visible not being set to false when it's value is zero.  I fought with this for quite a while before realizing that in 2010 the default view when running a report is Reort View and the "On Format" event only fires for Print and Print Preview.  So after switching to print preview for the report I found the hiding of the textbox value had been working all along.
I know that it has been some time since this lesson was recorded and that the solution has probably already been mentioned but I thought that I would post it just in case someone else like me was pulling out their hair.

Mark Clark

Reply from Richard Rost:

I do have yet to run through this example with Access 2010, but thank you very much for the notes.
Show Just This Thread        Post Reply
Comment from Alex Hedley @ 3/31/2011
In the PercentDiscount_BeforeUpdate event I've added some extra code so that when the user clicks Cancel the Discount Rate is returned to the original value.

ElseIf MyResponse = vbCancel Then
    Cancel = True    
End If

I did this because if you make a change to the Percent Discount but then change your mind and press Cancel you must then make a change before you can continue. It won't let you move to another field.
The Cancel = 1 simply stops the cursor leaving the control.


Reply from Richard Rost:

Good idea.
Show Just This Thread        Post Reply
Comment from Alex Hedley @ 3/31/2011
Hi Richard,
Was just playing about with your version of the sample datatbase and sometimes when I click in the the 'DiscountRate' on the 'OrderF' I receive the following error:

Unknown function 'Date' in validation expression or default value on 'OrderT.OrderDate'

Can't seem to work out which code needs amending.

[Using Access 2010]


Reply from Richard Rost:

I haven't tested this course with Access 2010 yet. What do I have the default value set to? If it's:


try changing it to:


Sometimes that makes a difference.
Show Just This Thread        Post Reply
Comment from  Jim Gray @ 3/24/2011
Here is a curious one for you... I am using Access 2010 and wrote the code to hide the zero values in the format section but, for some reason the zeros do not go away.  The code is very basic and I don't get any errors after I type it.  Any ideas?  Thanks.

Reply from Richard Rost:

I haven't tried it yet in 2010. Ill add it to my notes to look at when I update the class.
Show Just This Thread        Post Reply
Comment from Ajay Sunker @ 11/9/2010
Hi Richard, I am work on 305 AccountReceivableAgedQ and I get an error when I run query: Current: IIf([DueDate]>=Date(),[OrderTotal],0)

Error:Unefined Function'Date' in expression.

Reply from Richard Rost:

That shouldn't happen. Date() is a built-in function. Make sure you don't have any FIELDS in your table named Date.
Show Just This Thread        Post Reply
Comment from  Ayman Hama @ 5/21/2010
Can you cover the filter part of the Docmd.openform

Reply from Richard Rost:

Ayman, I almost NEVER use filters. I prefer Where conditions. Perhaps I'll try to cover them in more depth in a future lesson.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 9/12/2009
Jonathon, what do you mean? I used a simple IIF() function in a query for the aging part (30 days, 60 days, etc.) How would you suggest using VBA code for this? Personally, I've always been a "use the easiest solution" kind of guy. If I can get away with a basic query, then that's what I'll use. :)
Show Just This Thread        Post Reply
Comment from Jonathon Mun @ 9/11/2009
Hi Richard,
I was surprised that you did not use VBA code for the Aging Receivables.  Can this be done?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 3/24/2009
Either add the appropriate table to the query, or create another query based on the two and link it in. You can't bring another table into the REPORT, but you can bring it into the underlying query.
Show Just This Thread        Post Reply
Comment from Bin Chen @ 3/24/2009
Hi Richard,
I enjoy learning from your lessons. I have created my Accounts Receivable Report and there is one item that I need for collections that I can't get the data into the report.  Customer Telephone number.  This is nested in another table called Patient Data Table but the AccountsReceivableAgingQ does not have the patient telephone#.  How do I bring another table into the current report so I can I the Telephone as a field list item to choose.

Show Just This Thread        Post Reply
IIF fuction error Comment from Richard Rost @ 3/24/2009
Bin, make sure you if you have SPACES in your field names that you enclose them in square brackets:

LessThan30: IIF([Invoice Date]=Date()-30, Balance,0)
Show Just This Thread        Post Reply
IIF fuction error Comment from Bin Chen @ 3/23/2009
Hi Richard,

Thanks for your response.  I get an error msg " You may have entered an operand without an operator" error when applying the IIF function as per the lesson. I applied the following statement:

"LessThan30: IIF(Invoicce Date=Date()-30, Balance,0)"
I want to use the IIF to use the Invoice Date from the Billing Private Table to determine the aging of balances.  

I don't know where to go from here-Please help.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 3/23/2009
Bill, I would need to know much more than this to help you - for starters, what is the specific error message you're getting?
Show Just This Thread        Post Reply
Comment from Bin Chen @ 3/21/2009
Hi Richard,
I have purchased and gone over Access 305 with respect to Accounts Receivable.  but I am having trouble using the IIF function to count the aging of balances.  I get an error.  Please help.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 1+5:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP