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 326
Get notifications when this page is updated

Intro

In lesson 326, we will learn how to set up inventory control in Microsoft Access, including locking and refunding orders using VBA, handling order shipping by updating quantity on hand, and incrementing item quantities on existing orders. We will work with various inventory-related fields and continue building on record set techniques from earlier lessons, using a sample database in Microsoft Access 2003.

Transcript

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

In today's class, we are going to get to the big topic: inventory control. Everyone has been asking me for inventory control. We are going to learn how to lock orders when they are paid and how to refund an order. We will do all that with VBA code.

We are going to learn how to ship an order, which means we have to have quantity on hand and all kinds of other inventory control fields in our tables. When you ship an order, we are going to change the inventory levels. In other words, we are going to take that product out of stock by adjusting the quantity on hand using our record set techniques that we have been building up over the last five or six classes.

Then I will show you how to increment the quantity of an item on an order if the product is already on the order. For example, if the user clicks keyboard four times, instead of having four separate line items, we will use our record set to simply go in, see if the product is already there, and then just increment the quantity.

Again, all this stuff deals with record sets that we have been working with since Access 320. Before this class, make sure you have taken at least Access 320 and 321. Those two classes are critical for understanding how record sets work. Of course, by now, in 323-325, we have built many different record sets. It should be second nature to you, so make sure you go through and review those classes before starting this one.

In this class, I will be working with Microsoft Access 2003. However, most of the material should be backward compatible with all previous versions of Access back to about Access 2000.

I strongly recommend you sit back and relax, watch the lesson one time through, then watch it a second time following along with the examples and build the database that I build in class. Do not try to apply what you learn immediately to whatever other databases you might be working on.

The database files for this class can be found online at accesslearningzone.com/students. You will find a list of all the sample database files there. Again, it is strongly recommended that you build the databases in class that I am building.

If you have any questions about the material covered in today's class, please feel free to post a note in the student interaction forums. You will see the student interaction forums next to the video window if you are watching this class on our website or you are using our video player software and you are connected to the internet. You should see the little student interactive forums pop up right next to the video player.

If you have any other Access questions, please visit the forums on the website at accesslearningzone.com/forums. We have brand new interactive forums. As of October 2008, I created all brand new forums and you can log on, check them out, and chat with me, other students, and other Access experts.

And of course, I cannot stress enough, do the examples yourself. You will benefit the most by building the databases that I build in class.

Quiz

Q1. What is the main topic covered in Microsoft Access 326?
A. Creating forms and reports
B. Macros and automation
C. Inventory control
D. Database security

Q2. What action will be taken when an order is shipped?
A. A new record is created in the customers table
B. The quantity on hand will be increased
C. The inventory levels will be adjusted by reducing the quantity on hand
D. The price of the product will change automatically

Q3. What should you do if a product is already on the order and the user adds it again?
A. Create a new line item for each addition
B. Delete the previous entry and add a new one
C. Ignore the new addition
D. Increment the quantity of the existing line item

Q4. What prior knowledge is strongly recommended before taking Access 326?
A. Only Access 101 is necessary
B. No prior experience is needed
C. Access 320 and 321
D. Only experience with Excel

Q5. Which versions of Microsoft Access is this class material compatible with?
A. Only Access 2016 and later
B. Only Access 2003
C. Only Access 2010 and later
D. Most versions from Access 2000 onwards

Q6. What does the instructor suggest you do to get the most benefit from the course?
A. Only read the textbook
B. Do the examples and build the databases yourself
C. Watch the video once and move on
D. Work on unrelated databases while learning

Q7. Where can you find the sample database files for this class?
A. On the main Microsoft website
B. At accesslearningzone.com students
C. Only in the textbook
D. They are emailed to you automatically

Q8. What should you do if you have questions about the course material?
A. Email the instructor directly
B. Post in the student interaction forums
C. Wait until the next class
D. Ignore your questions and move ahead

Q9. If you are watching on the website or video player software, where can you find the student interaction forums?
A. They are not available online
B. In the video description
C. Next to the video window
D. Embedded in your email

Q10. Why is it NOT recommended to immediately apply what you learn to other existing databases?
A. It is too complicated
B. The examples are not useful
C. It is better to build and understand the examples in class first
D. The instructor wants you to memorize instead

Answers: 1-C; 2-C; 3-D; 4-C; 5-D; 6-B; 7-B; 8-B; 9-C; 10-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 Microsoft Access 326, and I am Richard Rost, your instructor. In this session, the spotlight is on inventory control, a topic that many students have requested. I will walk you through several essential features for managing inventory in your Access database.

We start by learning how to lock orders once they have been paid, along with how to handle issuing refunds for those orders. Both of these processes will be managed using VBA code.

Next, I explain how to implement shipping for orders. This process requires tracking the quantity on hand and other inventory-related fields in your tables. When you ship an order, you will need to update your inventory levels accordingly. This involves removing the shipped products from your inventory by adjusting the quantity on hand, and I will show you how to accomplish this using the record set techniques developed over the past several lessons.

I also demonstrate how to handle a situation where a user orders the same product multiple times. For example, if someone wants to buy four keyboards, instead of adding four separate line items to the order, the system will check if the product is already on the order and simply increase the quantity. This, again, uses the record set methods we started working on back in Access 320.

If you are taking this class, make sure you have already completed at least Access 320 and 321. These earlier lessons are critical for understanding record sets, which are used extensively here. The concepts have been built up piece by piece through sessions 323 to 325, so you should be comfortable with these ideas before moving into this material.

While I will be demonstrating everything in Microsoft Access 2003, most of what I cover should work in earlier versions of Access, as far back as Access 2000.

I recommend that you watch the entire lesson once from beginning to end first. On your second viewing, follow along and build the database just as I do in the video. Avoid trying to apply what you learn here to your existing projects right away; it is best to work directly with the examples created in class first.

You can find all the sample database files needed for this class at the students section of my website, accesslearningzone.com. I strongly encourage you to use these files and build along with me as you work through the material.

If any questions come up during the course, please use the student interaction forums. These are available next to the video if you are using our website or video player software while connected to the internet. You will find a direct link to these forums alongside the lesson.

For other questions about Access, visit the general forums on accesslearningzone.com. We now have a new set of interactive forums where you can log in, meet other students, chat with me, and connect with other Access enthusiasts.

Above all, remember to do all of the examples yourself. The hands-on practice of building the databases as shown in class will give you the best learning experience.

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

Locking orders when paid using VBA

Refunding an order with VBA code

Shipping an order and adjusting inventory levels

Tracking quantity on hand in inventory tables

Updating inventory fields when processing orders

Incrementing quantity for existing order items with VBA

Using record sets to manage inventory transactions