Microsoft Access Barcode & Inventory Seminar

Welcome

Welcome to the Microsoft Access Barcoding & Inventory Seminar. In this seminar you will learn everything you need to know about scanning and printing barcodes with Microsoft Access. You will also learn how to manage inventory with rentals, products, and individual unit serial numbers.

Resources

• Full Course Lessons Page
• Course Topic Outline
• Template Only Page

Topics Covered

We will begin by setting up a new database, including many of the options that I prefer for my projects. Then I'll show you how create a simple customer table, customer form, and customer list form.

We'll build an order entry form with the related tables and queries. We'll link it to our customer tables. We'll create a fully printable invoice report. We’ll cover some of the advanced preferences that I use for all of my projects. I'll show you which of the Access Options I like to change including setting up a Trusted Location, confirming document deletions and action queries, and more.

I'll show you how to create an order list so you can see all of your orders sorted by date and double-click on one to open it up. We'll copy the customer's address info to each order, so you know where the order was shipped or billed at the time it was placed. We'll also create a product list so you can pick a product and add it to the order with one click. 

We are going to learn about a very powerful way to work with data directly in our tables using Recordsets. These are objects that we can use to read, write, add, delete, and manipulate data directly in VBA code without having to use SQL, which can be slow. We'll talk about what Recordsets are, why we want to use them, and we'll set up our first example. There’s really no way around it. In order to work with inventory, you really NEED to learn Recordsets. I tried to do this with just basic SQL, but the end results are clunky and not the best. Don’t worry, I’ll show you the basics and what you need to get started. We will learn how to loop through the records in a table using a recordset, displaying whatever fields we want. We will then add those items from the recordset to an unbound "value list" list box. We'll also see how to manually add and delete records from the list box.

We will learn how to track rental inventory items. Say you have a store or library that tracks items that get rented (or borrowed) and need to be checked in and out. You can assign them to a customer, track their rental history, know when they're late, and so on. This will give us a foundation on how to track products and manage them going into and out of inventory. We will add product codes to each item in our inventory. These can be UPC codes,  product IDs, serial numbers, or whatever other type of data you wish to track each specific item with. You can pull up an item by typing in the code. We will also create a big text box so you can type in a bunch of items at once and have Access analyze the list and scan all of those items in with one batch. Great for book returns and you don't want to take the time to pull up each book. Just enter in the whole lot. This will become very useful when we learn how to scan in barcodes. Just scan in the returns one after the other and analyze them all at once.

Moving back to our order entry form, we will add QtyOnHand to our product table and create a product history table so we can audit transactions. We will add IsShipped to our order table to track whether or not an order has been shipped. If so, the order can no longer be edited (we'll lock it down). When the order is ready to be shipped, we'll click on a "Ship Order" button. The inventory will be checked to make sure we have enough product to ship the entire order. If so, we'll then remove the products from inventory.

We'll discuss the types of barcode scanners, the different types of barcodes, 1D and 2D barcode symbologies, using your cell phone as a barcode reader, creating a product table with a barcode field, creating a form to scan a barcode and look up a product.

We’ll learn how to scan a product and have it automatically added to an invoice. We'll also check to see if the product is already on the order, and if so, increment the value by one instead of adding lots of line items. We will also learn how to scan a product and perform a Google search on it to display details. Great for books or any product with a UPC barcode.

Learn how to print barcode labels from Microsoft Access. We'll talk about the Code39 barcode and why I prefer it for Access databases. I'll show you how to install a FREE Code39 barcode font. Then we'll see how to create custom barcode labels for our products and print them out from Access. 

Learn how to print multiple labels for the same product, so if you want to print out 10 copies of the same label, it's easy to do. We'll create a button on our Product Form to create individual labels, and then a button to clear the table once we've printed.

I will show you how to use the Google Charts API and the WebBrowser Control to display QR Codes (2D barcodes) in your forms. QR Codes are great for detailed product info, employee name badges, whole web page URLs, or any time you want to display an image for someone to scan with their phone to get detailed information. Learn how to download the QR code from the web and save it as a local file on their hard drive, in a folder under the current database folder. Then, you will see how to display that file as an image in a report so we can print employee name badges.

We'll create a method to manually adjust inventory. This is useful when you receive product shipments from your vendors. Just scan the items in. We'll create a combo box where we can select either a lookup scan, remove from inventory, or add to inventory action. We'll change the quantity on hand of the product scanned. We'll include logic to prevent negative inventory.

We'll add tracking individual unit serial numbers. We'll create a unit subform under the product form. This way you can track each of the ndividual items you have in stock, what their serial numbers are, and to whom they were sold. We'll create a barcode report for serial number labels, and a button to mark them printed when finished. 

We'll add the ability to scan barcodes from products or serial numbers to our order form. If it's a serial number then we cannot increment the quantity, we have to add a new line item with the unique barcode. We'll check to see if this unit is already on another order and generate a warning if so. We'll put barcodes on the printed invoices.

We'll add the printed barcode labels from the products that we designed earlier. We'll add the ability to print as many labels as we want in a loop, and mark the labels printed when we're done.

Pre-Requisites

It is strongly recommended that you have completed my entire Access Beginner and Expert series (at least Expert 1 and 2). My Developer 1 class is highly recommended. If not, at least watch my free Intro to VBA video to make sure you understand the basic concepts of VBA programming. 

Version

I am using Microsoft 365, roughly the equivalent of Access 2016 or 2019. Everything in this seminar should work with every version of Access back to 2007 with the exception of the lessons on the QR Codes. Those require the new WebBrowser control added to Access 2016.

Current Students

If you are a current Access Developer student of mine and have taken Developer 1 through 27, please contact me before you purchase this seminar. Several of the lessons here were taken from existing Developer courses and I will be happy to give you a discount based on which other classes you've already taken. No need for you to pay full price to receive duplicate lessons.

Enroll Today

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Disclaimer

While this database is fully functional and capable of supporting your business operations right out of the box, it is primarily intended as an educational tool. It has been designed to serve as a template that teaches you the intricacies of database management, rather than acting as a finalized product. Therefore, prospective purchasers should bear in mind that the database is best viewed as a foundation upon which to build and tailor to your specific needs. Although it has been utilized by hundreds of students and is fully operational, I make no assertions that it is a flawless or complete solution.

Questions?

Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.