Access 2007-2013
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  
 
 
 
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/24/2014 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
Current
LessThan30
LessThan60
MoreThan60
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
vbYesNoCancel

 


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.

Thanks.

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]

Alex

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:

=Date

try changing it to:

=Date()

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
Me.PercentDiscount.Undo
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.

Regards
Alex

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
ExtPrice-Discount=40.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)

LessThan30:IIf([DueDate]=Date()-30,[OrderTotal],0)

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

MoreThan60:IIf([DueDate]
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.

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
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