Resources

Description

This is the full, editable template and source code from my Barcode Inventory Seminar. Please see that page for a complete description of all of the features included in this database.

Sample Database

Download this sample ACCDE (encrypted) database file if you'd like to walk through the features yourself and see how it works. There are sample databases included for both 32-bit and 64-bit versions of Access. If you want to print any barcodes, you will need to install the Code39 Barcode Font which can be found on my Barcode TechHelp video page.

Full Purchased Database

Once you have purchased the full database template, come back to this page and click on the Download button below. This will give you access to the ZIP file containing the Template ACCDB file. The same database file will work on both 32-bit and 64-bit versions of Access.

Learn How This Database Was Built

Again, if you have any questions about the construction of this database, how anything works, or how it was built, please see my Barcode Inventory Seminar. Please note that the purchase of the template only does NOT entitle you to any help or technical support. If you have questions about the database that are answered in the seminar, I'm just going to direct you to the seminar videos.

Helpful Lessons

If you don't wish to purchase the full seminar, there are several free TechHelp videos on my site that may assist you with understanding some of the concepts covered in this database:

License

For internal use only. This template does NOT come with a royalty-free license. You may only customize the template for your business needs and for use within your organization at one location ONLY. You may not resell or distribute any form of this template to others without express written permission. Contact me for additional information on obtaining a license to distribute if you plan on including this template in a product you are reselling. Additional licenses are required if you plan on using this template with more than ten (10) employees or in multiple locations within your organization.

Not a Finished Product

Please keep in mind that most of my template databases are not designed to be finished products that are ready to go in a working environment. My templates are meant as starting points for you to customize for yourself so you don't have to reinvent the wheel. This requires that you have a basic understanding of Microsoft Access development. To work on most of my databases you should have completed my Access Beginner series and the first couple levels of my Expert series at a minimum. Most of my databases require knowledge of SQL and VBA as well. Keep in mind that most of my templates are not like off-the-shelf software. They're starting points for developers to be able to modify and use as their own. It really is going to be in your benefit to watch all of the prerequisite videos so you understand the database and how it works moving forward. This is not like QuickBooks where you just install it and use it. My templates are mostly designed for people who already have an understanding of how Microsoft Access works.

Customize For Your Needs

If you would like to discuss customizing this template for your needs, and integrating it into your current setup, please see my consulting page for details. While I no longer accept custom jobs that are specific to a single user, I may include your features in a future version of this template if they have mass appeal. If you are looking for custom enhancements made to this template just for you, visit my Developer Network.

Technical Support

Please note that technical support is NOT guaranteed for any of my courses, seminars, or templates. If you require help with modifying this template, you may post a question in the Forums, however an answer to your question is not guaranteed. If this template comes with an accompanying Seminar, then you should purchase that Seminar to see how the database was constructed. If not, then you should have taken the suggested courses. Most of my templates are designed on a Developer level and you should have a thorough understanding of SQL and VBA before attempting to modify them. If you have a problem with one of my databases, I will only support the unmodified database exactly as it's downloaded from my site. If you have modified it in any way, it may not be something I can help you with. You can post in the Forums, and I may be able to help you, but if the issue doesn't exist in my unmodified database, it's not something I can support.

Questions?

Got sales or customer service questions about this template? Feel free to contact me. If you have technical "how do I" questions about this template, you discover a bug, or want to suggest a new feature, then please post your comments below.

Subscribe to Access Barcode Template
Get notifications when this page is updated

Intro

In this video, you will learn how to scan and print barcodes with Microsoft Access and set up a complete inventory management database, including rentals, products, and serial numbers. We will talk about customer and order management, using record sets in VBA, tracking rental and regular inventory, scanning and printing Code 39 and QR code barcodes, handling bulk item returns, printing barcode labels, and managing inventory in and out. You'll also see how to use advanced barcode scanning techniques, add Google-powered QR codes, and track individual units with serial numbers.

Transcript

Welcome to the Microsoft Access Barcoding and Inventory Seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

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.

We will cover basic customer management, order entry and invoicing, record set programming, managing rental inventory, scanning most barcodes, printing Code 39 and QR code barcodes, tracking product inventory in and out, managing units with serial numbers, printing product and unit barcodes, and using a demo label maker.

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 to create a simple customer table, customer form, and customer list form.

Next, 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.

We will build an order entry form with the related tables and queries and link it to our customer tables. Then we'll create a fully printable invoice report. 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 record sets. These are programming objects that we can use to read, write, add, delete, and manipulate data directly in VBA code without having to use SQL. We'll talk about what record sets are, why we want to use them, and we'll set up our first example.

In order to work with inventory, you really need to learn record sets. I tried to build this database with just basic SQL, but the end results are clunky, slow, and not the best. But don't worry, I'll show you the basics and exactly what you need to get started.

We will learn how to loop through the records in a table using a record set, displaying whatever fields we want. This will be invaluable when we get to inventory. We will then add those items from the record set to an unbound value list box. I'll also show you how to manually add and delete records from the list box as well.

We will learn how to track rental inventory items. For example, if you have a store or a library that tracks items to 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'll 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 one batch. This is great for book returns when you do not want to take the time to pull up each book. Just enter in the whole list. This will become very useful when we learn how to scan in barcodes. Just scan in the returns one after the other and then analyze them all at once.

Moving back to our order entry form, we will add quantity on hand to our product table and create a product history table so we can audit transactions. We will add "is shipped" 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 the 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 the inventory.

Next, we'll get to barcode scanning. 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, and 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 our invoice.

We'll also check to see if the product is already on the order and if so, increment the value by 1 instead of adding lots of line items. We'll also learn how to scan a product and perform a Google search on it to display details. This is great for books or any product with a UPC barcode.

Next, we'll learn how to print barcode labels. We'll talk about the Code 39 barcode and why I prefer that for Microsoft Access databases. I'll show you how to install a free Code 39 barcode font. Then we'll see how to create custom barcode labels for our products and print them out. We'll learn how to print multiple labels for the same product, so if you want to print 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.

Next, we'll learn how to use the Google Charts API and the Web Browser control to display QR codes. QR codes are great for detailed product info, employee name badges, webpage URLs, or anytime you want to display an image for someone to scan. They can even use their cell phones to get detailed information.

We'll learn how to download the QR code from the web and save it as a local file on our hard drive. This way, the information is stored with the database and you can print the image in a report. This way, 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. We will then change the quantity on hand of the product scanned, and we'll include logic to prevent negative inventory.

We'll add tracking of individual unit serial numbers. We'll create a unit subform under the product form. This way you can track each of the individual 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, so we'll 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. Then we'll put the barcodes on the printed invoices.

Before taking this seminar, I strongly recommend you have completed my Access Beginner series, Expert 1 and 2 at a minimum, and I strongly recommend you take Developer Level 1 so you have a background with some Visual Basic programming. If you have not taken Developer Level 1, I at least strongly recommend you watch my Intro to VBA video before taking this seminar. It's a free video; you can find it on my website, the link is right there.

I will be using Access 365, which is roughly equivalent to Access 2016 or 2019. The vast majority of the material in this course will work with versions of Access all the way back to 2007, with the exception of the QR code material. The web browser control that I use to get the QR code is new in Access 2016. Everything else, including scanning and printing the regular barcodes, should work back to Access 2007.

If you have questions about the material covered in today's seminar, please feel free to post them in the comment section below. If you have other questions about Access not related to today's class, please feel free to post them in my Access forum.

Now let's get ready to enjoy the Microsoft Access Barcoding and Inventory Seminar brought to you by AccessLearningZone.com.

Quiz

Q1. What will you learn about barcodes in this seminar?
A. Scanning and printing barcodes with Microsoft Access
B. Designing barcodes in Excel
C. Creating barcodes using HTML
D. Scanning barcodes with Microsoft Word

Q2. Which of the following types of inventory management will be covered in the seminar?
A. Only sales inventory without serial numbers
B. Rental inventory and serial number tracking
C. Fixed assets only
D. Financial inventory reporting

Q3. Why does the instructor recommend learning about record sets?
A. They allow advanced formatting of reports
B. Record sets enable direct table manipulation in VBA, crucial for inventory workflows
C. Record sets are required for table joins
D. They make exporting data to Excel easier

Q4. What will you use to represent products uniquely in the database?
A. Only the product name
B. Only the manufacturer
C. Product codes such as UPC, product ID, or serial numbers
D. The date of purchase

Q5. What does the seminar teach about handling multiple products at once?
A. How to enter one product at a time only
B. How to analyze and scan multiple items in a batch
C. How to delete all products at once
D. How to reprint all invoices at once

Q6. What feature will be added to the order table to prevent editing after completion?
A. "Is shipped" status to lock the order after shipping
B. Product color coding
C. Automatic archiving after 30 days
D. Customer approval status

Q7. What type of barcode font is demonstrated for label printing in Microsoft Access?
A. Code 128
B. PDF417
C. Code 39
D. Data Matrix

Q8. Why might you want to print multiple labels for the same product?
A. To save paper
B. If you have several units or need redundant labels
C. To display on different web pages
D. For security features only

Q9. How does the seminar utilize QR codes?
A. To provide direct access to VBA tutorials
B. To display detailed product info and employee badges using the Web Browser control
C. For auditing security logs
D. To display Access version information

Q10. What happens if you scan a product that is already entered on an order?
A. It creates a new customer record
B. The value is incremented on the existing line item
C. The old entry is deleted and replaced
D. Nothing happens

Q11. What protection is added to avoid negative inventory?
A. Inventory is reset to zero automatically
B. Access blocks inventory removal beyond available quantity
C. Negative values are allowed but highlighted
D. Inventory decreases without checks

Q12. Why should you have taken the Access Developer Level 1 course or watched the Intro to VBA video before this seminar?
A. Because advanced VBA programming is required to complete the seminar
B. Because SQL knowledge is not required
C. Because only report design is covered
D. Because product management is not discussed

Q13. Which Access versions are generally compatible with the seminar's material?
A. Only Access 365 and newer
B. Access 2003 and earlier only
C. Access 2007 and newer, with QR code features needing Access 2016 or later
D. Microsoft SQL Server only

Q14. What is one of the final features related to serial number tracking discussed in the seminar?
A. Sending serial numbers by email only
B. Marking unit barcodes as printed and adding the barcodes to printed invoices
C. Assigning serial numbers to customers only
D. Encrypting serial numbers by default

Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-A; 7-C; 8-B; 9-B; 10-B; 11-B; 12-A; 13-C; 14-B

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 the Microsoft Access Learning Zone focuses on barcoding and inventory management with Microsoft Access. I will guide you through the essential skills necessary to scan and print barcodes, as well as track inventory, including handling rentals, products, and items with individual serial numbers.

We start by creating a new database, where I share some of the database options and preferences I rely on for my projects. We'll begin with the basics, such as building a simple customer table along with forms for customer data entry and a list view to display your customers.

I then walk you through some key advanced Access settings I always change for my work. This includes setting up trusted locations, enabling confirmation for document deletions and action queries, and adjustments that help make your Access environment more effective and secure.

From there, we build an order entry system. I show you how to design the necessary tables and queries, link them to your customer information, and set up a printable invoice report. You'll also learn how to organize and view your order history by date, and instantly access order details. We also ensure that each order captures the correct shipping and billing address from the customer at the time the order is made. To make it easier to add products to an order, we develop a product list you can quickly select from.

A significant portion of the seminar is dedicated to working with record sets. Record sets are objects in Access VBA that allow you to interact with table data directly from your code. You can read, modify, add, and delete records, all without using SQL queries. I explain the concept behind record sets, why they are valuable, and how to set up examples you can build on. Mastering record sets is crucial if you want to handle inventory efficiently. While you can try to use just SQL, the performance and functionality will suffer. I make sure to provide you with the necessary foundation to get started, demonstrating how to loop through records and display information on forms, a technique that becomes indispensable as we move into inventory management.

We also look at how to work with unbound list boxes. Using record set data, I'll show you how to add items to a list box, remove them, and manually handle entries, giving your users more flexibility and control.

You will then learn how to manage rental inventory, which is ideal for businesses or organizations that loan or rent items like equipment, books, or other assets. We establish a system to track which customer has which item, keep rental history, and monitor overdue returns. This step forms the groundwork for robust inventory tracking.

Next, we explore the use of product codes in inventory management. You can use UPC codes, product IDs, serial numbers, or any identifiers to keep track of individual items. Access provides the ability to look up an item by entering its code.

I introduce a feature where you can paste or scan a batch of item codes into a large text field, and Access will process them at once. This is especially helpful in scenarios like book returns, where scanning items rapidly is more efficient than looking them up one at a time.

We then enhance the order entry system by adding a field to track the quantity of products on hand and by introducing a transaction history table. Orders are further managed by tracking their shipped status, preventing edits to shipped orders, and implementing a "ship order" function. When shipping, the system checks if you have enough stock and automatically adjusts inventory levels once an order is shipped.

Barcode scanning is an important focus. We discuss the types of barcode scanners available, 1D and 2D barcode types, and using your phone for barcode scanning. We set up a barcode field in the product table and build forms for scanning barcodes to quickly bring up product information. You'll learn how scanned products can be added directly to invoices, including checking if items are already on an order and increasing their quantities, or otherwise adding new line items. There's even a demonstration of how you can scan a barcode and instantly search for product information online, which is particularly useful for books and items with UPC codes.

Printing barcode labels is another area we cover. I explain why Code 39 is my preferred barcode symbology for Access projects and walk you through installing a free barcode font. We create and print custom barcode labels, including generating multiple copies of labels for a single product. Buttons streamline the process for printing and clearing out old label data.

I also cover the use of Google's Charts API and Access's Web Browser control for displaying QR codes. QR codes are handy for conveying more detailed information, whether it's product details, employee name badges, or web addresses. Users can scan them using their phones for quick access to more data. We also set up a workflow to download QR codes to your hard drive, making them available for printing on reports like name badges.

Inventory adjustment is an essential part of real-world inventory systems. I show you how to handle new product shipments and create options for scanning items, removing them, or adding them via a combo box. The system keeps accurate quantity records and can prevent negative inventory levels.

Managing inventory with serial numbers is addressed by building a unit subform tied to each product, allowing you to track every individual item, its serial number, and to whom it was sold. We also set up reports and processes for printing barcodes for these serial number labels, with tools to confirm printing completion.

You will see how to scan products or their serial numbers directly on your order form. With serial numbers, you cannot simply increase quantity; each is a unique item, so every scan adds a new line. The system checks for duplicate serial number assignments and flags any issues. We also make sure these barcodes appear on printed invoices.

Before beginning this seminar, I strongly suggest you first complete my Access Beginner series, and at least Expert Level 1 and 2. For better results with the programming sections, I highly recommend my Developer Level 1 course, or, at the very least, watch my free Introduction to VBA video, which is available on my website.

This seminar is taught using Access 365, but almost all features are compatible with Access 2016 and 2019, and nearly everything except the QR code segment will work with Access 2007 and above. The QR code demonstration uses the Web Browser control introduced in Access 2016.

If you have questions about the seminar, feel free to ask in the comment section. For other Access-related concerns, my forum is open to your participation.

A full video tutorial with comprehensive, step-by-step instructions for everything I've discussed here is available on my website at the link below.

Live long and prosper, my friends.

Topic List

Setting up a new Microsoft Access database for inventory
Creating customer tables, forms, and list forms
Configuring advanced Access options and preferences
Building order entry forms linked to customer tables
Creating printable invoice reports
Implementing an order list with sortable and clickable entries
Copying customer address info to orders
Creating and managing product lists for order entry
Introduction to record sets in VBA for data handling
Looping through records using VBA record sets
Adding and deleting items in an unbound value list box
Tracking rental inventory and rental history
Assigning and managing product codes and serial numbers
Batch entry and analysis of multiple inventory items
Updating inventory quantity on hand and transaction history
Implementing shipped status and order locking
Shipping orders and updating inventory automatically
Overview of barcode types and barcode scanners
Setting up barcode fields in product tables
Scanning barcodes and looking up products in Access forms
Auto-incrementing invoice line items when scanning products
Performing Google searches on scanned product barcodes
Printing barcode labels using Code 39 fonts
Installing free Code 39 barcode fonts for Access
Creating and printing custom barcode labels
Printing multiple copies of product labels
Clearing label tables after printing
Displaying QR codes with Google Charts API and Web Browser control
Downloading and saving QR code images locally
Printing QR codes in reports such as employee badges
Manually adjusting inventory quantities via form controls
Batch scanning and updating inventory additions and removals
Enforcing logic to prevent negative inventory
Tracking inventory by individual unit serial numbers
Managing unit subforms under product forms
Printing barcode labels for unit serial numbers
Marking serial number labels as printed
Enabling barcode scanning for products and serial numbers in orders
Handling serial-numbered units in orders to prevent duplicates
Including barcodes on printed invoices