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  
Courses - Microsoft Access 305
Description: Advanced Access
Running Time: 79 minutes
Pre-Requisites: Access 304 very strongly recommended
Previous Lesson: Access 304
Next Lesson: Access 306
Main Topics: Aged Accounts Receivable, IIF, MsgBox, Before Update, Cancel Event, vbYesNo
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.


Order before 4/28/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

Microsoft Access 305
Advanced Access Development

Aged Accounts Receivable, IIF Function, MsgBox with vbYesNo, BeforeUpdate Event, Cancel an Event, vbYesNoCancel. 79 Minutes

AC305 Major Topics

  • Aged Accounts Receivable
  • IIF Function
  • MsgBox with vbYesNo
  • BeforeUpdate Event
  • Cancel an Event
  • vbYesNoCancel

Access 305 continues our advanced Access database development with VBA programming. We continue work on our sales and order-entry system.

This lesson focuses on building an Aged Accounts Receivable. You will learn a lot of new tricks including using a MsgBox to ask questions, the vbYesNoCancel option, the BeforeUpdate event, and much more.

We will begin by creating a query to hold our Accounts Receivable data.

You will learn how to determine how old certain invoices are - which are current, which are 30-days late, 60-days, etc.

This is the basis for an AGED Accounts Receivable:

Then we'll create a Report to display our AR information. We'll create form headers and footers to sum up the data.

Next we'll learn how to give each order a Percent Discount.

We'll give each item on the invoice a possible discount as well (in case certain items are, for example, 50% off).

We'll learn how to get information back from the user by asking questions with the MsgBox command:

We'll learn about the BeforeUpdate event which allows us to cancel an event in progress:


Access 305 Outline

1. Accounts Receivable Query
Aged Accounts Receivable
Create a Query with OrderTotals
Add OrderDate to tell Age of Order
Use IIF Function to Add Aging
Add DaysOld Field

2. Accounts Receivable Report
Create the report
Format as Currency
Hide $0.00 values with VBA Code
The Visible Property
Put SUMs in the Report Footer
Create an AR Total

3. Percent Discount on Orders, Part 1
Discounts as a dollar amount
Percentage Discount
Add PercentDiscount to OrderT
Add PercentDiscount to OrderDetailT
Adjust SalesTax to figure Discount
Link the Discount default to OrderF

4. Percent Discount on Orders, Part 2
Add PercentDiscount to CustomerTable
Add PercentDiscount to CustomerForm
Add to AfterUpdate event CustomerCombo
Update DiscountRate on Line Items if Form Changes
Create an Update Query to change Rate
Create an AfterUpdate event to run Update Query
MsgBox to Get a Response
vbYesNo, vbYes, vbNo
Line Continuation Character in VB Code
Dim variables
Option Explicit

5. BeforeUpdate
The Cancel parameter of BeforeUpdate
OldValue property


Try a FREE Demo Lesson


Student Interaction: Microsoft Access 305

Richard on 1/1/2007:  Accounts Receivable Aging, vbYesNoCancel MsgBox, BeforeUpdate Event, Cancelling Events, more
Bin Chen on 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.

Richard Rost on 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?
Bin Chen on 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.

Richard Rost on 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)

Bin Chen on 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.


Richard Rost on 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.
Jonathon Mun on 9/11/2009: Hi Richard,
I was surprised that you did not use VBA code for the Aging Receivables. Can this be done?

Richard Rost on 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. :)
 Ayman Hama on 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.

Ajay Sunker on 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.

 Jim Gray on 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.

Alex Hedley on 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.

Alex Hedley on 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.

Mark Clark on 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.

Kevin O'Malley on 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?
Kevin O'Malley on 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?
Benjamin Chua on 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.

Sandra Bischler on 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.

Sandra Bischler on 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.

Clay Fulgham on 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?

CHARLES FULGHAM on 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.
CHARLES FULGHAM on 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 :)

vicki Hudson on 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

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

Michael C on 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?

Shallena Ayers on 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.


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


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