Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 305
Get notifications when this page is updated

Intro

In lesson 305, you will learn how to create an aged accounts receivable report in Microsoft Access to track customer debts and how long they have been outstanding. We'll cover building queries for aging the receivables, generating reports, and adding percent-based discounts for specific customers. You'll also learn about the before update event to validate data before it is saved and how to use message boxes to capture user responses such as Yes, No, or Cancel.

Transcript

Welcome to Microsoft Access 305. Brought to you by accesslearningzone.com. I am your instructor, Richard Rost.

In today's class, we are going to build an aged accounts receivable, which basically is fancy accounting terminology for a list of who owes you money and how old their debt is: 30 days, 60 days, and so on. It is called an aged accounts receivable.

It is going to involve some query work and a little bit of report generation. Then we are going to see how we can add percent discounts to our orders. For example, customer XYZ gets a 5 percent discount on their orders. We want to be able to provide that.

We are going to learn about a couple of key concepts: the before update event, which allows us to intercept data before it gets saved to the table. This means we can validate it and we can cancel the action.

We are going to learn how message box can be used to retrieve the response values. We can pop up a message box and the user can answer yes or no, or yes, no, cancel. So the next item is their VB-Yes-No-Cancel. There is also VB-Yes-No, there is VB-OK; there are a bunch of different ones. We will learn a couple of them today, using message box to get yes, no, or cancel events from our users.

This class follows Microsoft Access 304. I recommend you take 304 and all the previous classes before taking this one.

We will be using Access XP and Windows XP. It is probably recommended you go through the lessons once before trying them, and then go through it again, building the database on your own.

Sample database files can be found at the website in the Access-305 folder. They are also in the help folder for this class on your hard drive if you installed this from the CD-ROM.

You will greatly benefit by doing the examples yourself. So do not just use my database, build a database yourself.

Quiz

Q1. What is the main focus of Microsoft Access 305?
A. Building a payroll system
B. Building an aged accounts receivable and adding percent discounts to orders
C. Creating an inventory management system
D. Learning advanced VBA programming

Q2. What is an aged accounts receivable?
A. A type of employee record system
B. A list of what products are in stock
C. A list of who owes you money and how old the debt is
D. A log of customer complaints

Q3. Which of the following is one key concept covered in this class?
A. The after update event
B. The before update event
C. The continuous form property
D. The split form view

Q4. What is the purpose of the before update event discussed in this tutorial?
A. To save records automatically
B. To modify form layouts
C. To intercept data before it gets saved for validation or cancellation
D. To generate data reports

Q5. How can Message Box be used according to the lesson?
A. Only for displaying error messages
B. For complex data exports
C. To retrieve response values like yes, no, or cancel from users
D. For formatting reports

Q6. What is recommended before trying the lessons on your own?
A. Skipping straight to database building
B. Only reading the textbook
C. Going through the lessons once
D. Hiring a professional developer

Q7. Where can you find the sample database files for this class?
A. Only in online help
B. In the Access-305 folder and on the CD-ROM if installed
C. They are not provided
D. Only by emailing the instructor

Q8. What is the instructor's advice regarding sample databases?
A. Only use the provided database
B. Memorize every field name
C. Build a database yourself to benefit greatly
D. Do not attempt to build your own

Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on creating an aged accounts receivable in Microsoft Access. This term comes from accounting and simply refers to a list of people or companies who owe you money, along with information about how long those debts have been outstanding - for example, whether they are 30 days old, 60 days old, and so on.

We will tackle this by working with queries and generating a report that presents this information clearly. In addition, I will show you how to set up percent discounts for specific customers. For example, if customer XYZ should always receive a five percent discount on their orders, we want our database to accommodate that.

Along with these topics, I'll introduce a few important concepts in Access VBA. We will look at the Before Update event, which is very useful for validating data before it is saved to your tables. This lets you catch errors or inconsistent data and cancel the action if needed.

Another key topic is how to use the message box to collect input from the user. For instance, you can prompt the user with a message box that gives them the choice of Yes, No, or Cancel, and then take different actions depending on their selection. Today, I will demonstrate the different types of message boxes available, such as Yes-No-Cancel, Yes-No, or OK, and how to process each response in your code.

This class is a continuation of Microsoft Access 304, and I recommend that you complete that and all prior lessons before starting this one.

I will be using Access XP and Windows XP throughout these lessons. It is best to watch the entire lesson one time before building the example yourself. Afterward, try to construct the database from scratch to reinforce what you have learned.

You can find the sample database files for this lesson on my website in the Access-305 folder. If you installed this from a CD-ROM, the files are also available in the help folder for this class on your hard drive.

The most effective way to learn is by building the examples yourself, rather than simply using my finished database. Take the time to follow along and create your own version as you go.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Building an aged accounts receivable query
Creating a report for accounts receivable aging
Adding percent discounts to customer orders
Using the before update event for data validation
Using message boxes to retrieve user responses
Implementing VB-Yes-No-Cancel message boxes