Introduction

Welcome! Barcode Scanning & Labeling

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Access Developer Level 27. In this course we will cover using barcodes with Microsoft Access, including scanning items in and out of inventory, tracking unit serial numbers, and managing product codes. We will discuss different types of barcode scanners and barcodes, creating and printing barcode labels using a free font, integrating QR codes with the Google Charts API and web browser control, and downloading and saving QR code images. Additional topics include synchronizing forms, adjusting inventory, tracking and printing serial numbers, and integrating barcode features into your developer database for inventory control and order management.

Navigation

Prev: D27
Next: Lesson 01

Keywords

Access Developer, barcodes, barcode scanning, barcode labels, Code 39, QR codes, Google Charts API, web browser control, serial number tracking, inventory control, product lookup, barcode fonts, printing labels, syncing forms, product table, inventory adj

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 27 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's lesson, we are going to cover pretty much everything there is to know about using barcodes with Microsoft Access. Even if you are not planning on scanning and printing barcodes yourself, you will find these lessons useful. I will be talking about scanning items into and out of inventory, including tracking unit serial numbers.

You can skip the lessons on printing barcodes if you are never going to print, but if you are ever going to do any kind of inventory control, the rest of the lessons will be great as far as tracking product codes, unit serial numbers, and so on.

Plus, there is some extra cool stuff in here like using the web browser control and downloading images from the web. So there is extra stuff in here for everybody.

This class follows Access Developer 26. A lot of the database that we use in this class is the database that we have been building over the last couple of classes. If you have not taken 26 and before, you might be a little lost. So go take 26 first.

Of course, I expect that you have a solid foundation in all my beginner, expert, advanced, and other developer lessons, especially from 16 on, when we cover record sets. I am using Access 365, which is roughly equivalent to 2016 or 2019.

If you have any questions regarding the material covered in today's class, scroll down to the bottom of the page that you are on right now and post them there. Take a minute to read through any other questions that have been posted, as your question may have already been answered. That happens a lot.

Also, make sure you click on the subscribe button for this page to make sure you get notified if any other questions or comments or addendums are posted.

As always, if you have Access questions that are not related to today's class, go ahead and post them in my Access forum.

Now, let's take a closer look at what's covered in Access Developer 27.

In lesson one, we will 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.

In lesson two, we will learn how to scan a product in and have it automatically added to an invoice. We will also check to see if the product is already on the order and if so, we will increment the value by one 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.

In lesson three, we are going to learn how to print barcode labels. We will talk about the Code 39 barcode and why I prefer it for Access databases. I will show you how to install a free Code 39 barcode font. Then we will see how to create custom barcode labels for our products and print them out.

In lesson four, you will learn how to print multiple labels for the same product. So if you want to print out 10 copies of the same label, it is easy to do. We will create a button on our product form to create individual labels and then a button to clear the table once we have printed.

In lesson five, we are going to learn how to use the Google Charts API and the web browser control to display QR codes, which are 2D barcodes, in your Microsoft Access Forms. QR codes are great for detailed product info, employee name badges, whole web page URLs, or anytime you want to display an image for someone to scan with their phone to get detailed information.

In lesson six, you will learn how to download a QR code from the web and save it as a local file on your hard drive in a folder under the current database folder. Then you will learn how to display that file as an image in a report and this way we can print employee name badges.

In lesson seven, we are going to merge the barcoding stuff that we've done in with the developer database. We are going to take the barcode scanning text box that is on the order form and add that over here. We are going to build a product list form. I will show you how to synchronize two different open forms. So if you click on something in the product list, it will automatically jump to the product form as well. Then we will put the product code lookup scan box there on the product list form.

In lesson eight, we are going to add the ability to manually adjust our inventory from the product list form. We will make that scan box so we can pick from a combo box whether we are going to just look up, add to inventory, or subtract inventory.

So this way, when we scan a product, if I have got add to inventory selected, it will add a unit, add a unit, add a unit. If I go subtract from inventory, it will take stuff out of inventory by subtracting a unit. We will put logic in there to prevent them from going negative.

In lesson nine, we are going to start tracking serial numbers. So we have made a product code that tracks unique products like this particular hard drive, this particular stool, this particular whatever you are selling. Now we are going to track serial numbers for individual units.

So if you are selling hard drives, you have all the same product, you have 10 of the same hard drive: this is unit one, unit two, unit three, unit four - specific items. We will print out serial number barcodes for each one of them. We will track that in your database.

We will make a table that is part of it. It is a subform underneath the product form where we can say, click on the button add units, I am going to add 10 of these, it will add 10 labels to your system and print them out. We will mark them as printed so we know those ones are good. Then we will get ready to scan them in the next lesson.

In lesson ten, we are going to scan those serial number barcodes onto our orders. Again, we will program the barcode scanning field so it will know whether you scan a serial number or a product number. It will look it up appropriately in the database. It will add it to the order. It will not do that increment trick where it just adds one to the quantity. If it sees it has got a serial number, that has to be a separate line item. We will check to see if that serial number has been used on a different order. We do not want to scan the same barcode twice.

Then we will print the barcodes on that person's invoice so they have them and then we are all set with that.

In lesson eleven, we are going to integrate the printing of the barcodes from the product table that we did in one of the earlier lessons. We are just going to add them into the developer database so everything is nice and together.

We will make it also so we can print any number of barcode labels because remember in the first lesson, we just did one at a time. This one will again ask the user how many they want. We will make a button to print them and then a button to mark those labels as printed.

Quiz

Q1. What is the primary focus of Access Developer 27?
A. Using barcodes with Microsoft Access
B. Data encryption techniques in Access
C. Advanced reporting in Access
D. SQL Server integration with Access

Q2. Which lesson discusses the types of barcode scanners and different barcode symbologies?
A. Lesson two
B. Lesson one
C. Lesson five
D. Lesson seven

Q3. If you want to print out 10 copies of the same barcode label, which lesson covers that?
A. Lesson six
B. Lesson four
C. Lesson ten
D. Lesson eight

Q4. Why does the instructor prefer Code 39 barcodes for Access databases?
A. They support color printing
B. They are easy to print using a free font
C. They are the only barcodes compatible with Access
D. They can encode large images

Q5. What capability is added with the web browser control and Google Charts API in lesson five?
A. Creating PDF reports
B. Displaying QR codes in Access Forms
C. Running Access on the web
D. Importing data from Excel

Q6. What is discouraged if you have not taken Access Developer 26 before starting this course?
A. Skipping advanced VBA lessons
B. Starting with lesson four
C. Using outdated barcode hardware
D. Beginning this class, as you might get lost

Q7. In which lesson do you learn to scan a product and have it automatically added to an invoice, incrementing quantity if it already exists?
A. Lesson nine
B. Lesson two
C. Lesson six
D. Lesson seven

Q8. What is unique about tracking serial numbers as described in lesson nine and ten?
A. Serial numbers can be added as a batch and tracked individually
B. All serial numbers are grouped under one line item on an invoice
C. Serial numbers are only for employee badges
D. Serial numbers are replaced by product names

Q9. What feature do you learn to implement for manual inventory adjustments using barcode scanning?
A. Barcode-based login authentication
B. Picking actions like lookup, add, or subtract inventory from a combo box
C. Highlighting expired products
D. Scheduling inventory checks automatically

Q10. When scanning a serial number into an order, what does the system check?
A. If the serial number belongs to a different product category
B. If the serial number has already been scanned on a different order
C. If the serial number matches the invoice number
D. If the serial number is the highest in the database

Q11. For which users are the lessons on printing barcodes optional?
A. Those tracking employee time
B. Those not planning to print barcodes
C. Those only using Access web apps
D. Those using 2D scanners

Q12. How are multiple barcode labels tracked to avoid duplicates?
A. By storing the date and time of each label
B. By marking labels as printed in the database
C. By emailing a confirmation for every label
D. By using label paper with watermarks

Q13. Which feature allows synchronizing two open forms so a selection in one highlights data in the other?
A. Lookup macros
B. Form event synchronization
C. Embedded macros
D. Linked tables

Q14. What does the course suggest doing before posting a question about the lesson?
A. Submitting a helpdesk ticket
B. Reading through existing questions and answers
C. Calling the instructor directly
D. Skipping to the next module

Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-D; 7-B; 8-A; 9-B; 10-B; 11-B; 12-B; 13-B; 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 Access Learning Zone covers everything you need to know about working with barcodes in Microsoft Access. Even if you have no immediate plans to scan or print barcodes, the material here will benefit anyone handling inventory, tracking product codes, or dealing with unit serial numbers. You can skip the barcode printing sections if that's not something you'll ever need, but the rest of this course will prove very useful for inventory management and tracking.

You'll also find some interesting content on topics like using the web browser control in Access, as well as downloading images from the internet. There is a little something in this class for everyone.

This course picks up where Access Developer 26 left off. We will continue working with the same database structure we've been building throughout the previous lessons. If you haven't completed Developer 26 or any of the prior courses, you may find yourself a bit lost, so I recommend you start there if you are not already caught up.

It's important that you already have a strong foundation from my beginner, expert, advanced, and earlier developer courses, especially starting around Developer 16, where recordsets are introduced. For these lessons, I am using Access 365, which is more or less the same as Access 2016 or 2019.

If you have any questions related to the material in this class, scroll down to the comments section on the page where you're watching and post your question there. Before you post, take a minute to read through existing questions to see if your issue has already been answered, as that happens quite often. Be sure to subscribe to notifications on the page so you'll be updated if new questions or addendums are posted.

For Access questions unrelated to today's class, please use my main Access forum instead.

Let me walk you through the topics covered in Access Developer 27.

In the first lesson, I introduce the various types of barcode scanners and the different barcode symbologies you'll encounter, including both 1D and 2D barcodes. I also talk about using your cell phone as a barcode reader. We will build a product table with a field dedicated to barcodes, and then create a form that allows you to scan a barcode and quickly look up a product.

The second lesson focuses on scanning products and automatically adding them to invoices. I show you how to check whether the product is already on an order and, if it is, increment its quantity instead of creating multiple line items. We'll also look at scanning a barcode and using it to trigger a Google search, which is particularly useful for products like books that have a UPC.

In the third lesson, we begin working with barcode labels. I talk about the Code 39 barcode format and explain why I recommend it for use in Access databases. You'll learn how to install a free Code 39 barcode font, set up custom barcode labels for your products, and print them.

Lesson four covers printing multiple labels of the same product. If you ever need to print, for example, ten identical labels, you'll see just how simple that is. We set up buttons on the product form to create individual labels and another button to clear the label table once printing is complete.

Moving on to lesson five, I introduce the Google Charts API and show you how to use the web browser control to display QR codes directly on your Access forms. QR codes, which are a type of 2D barcode, are especially handy when you need to store more detailed information like product details, employee badges, or links to web pages.

Lesson six demonstrates how to download a QR code image from the web and save it as a file inside a folder beneath your database directory. You'll then learn how to display that image on a report, which lets you print things like employee ID badges complete with QR codes.

In lesson seven, we integrate all the barcode features we've developed so far into the main developer database. We'll add the barcode scanning textbox to the order form and build a product list form. I'll go over how to synchronize two forms so that selecting a product in one will automatically open the correct record in the other. We'll also include the product code lookup scan box on the product list form.

Lesson eight adds manual inventory adjustments from the product list form. We modify the scan box to let you use a combo box to pick whether you'll look up a product, add to inventory, or subtract from inventory. When you scan an item, if "add to inventory" is chosen, it increases the inventory count. If "subtract from inventory" is selected, it reduces the count, with safeguards to prevent negative inventory values.

Lesson nine introduces serial number tracking. Rather than just tracking different types of products, this lesson goes further to let you track individual units by their serial numbers, like differentiating between multiple identical hard drives. You'll learn how to print out barcode labels for each serial number and how to track them in your database. We'll create a subform so you can add multiple units, generate labels, and mark them as printed.

In lesson ten, we cover how to scan serial number barcodes into your orders. The barcode scanning field will recognize whether a scanned code is a product or a serial number, handle each correctly, and prevent duplicate serial numbers from being entered into orders. Serial numbers will always appear as separate line items, and we'll ensure none are reused on different orders. We will also print the corresponding barcodes on the customer's invoice.

Finally, lesson eleven combines all the barcode label printing features into the main developer database. You will have the flexibility to print any number of labels for your barcodes, with prompts for the quantity desired, as well as buttons to print and mark those labels as printed when you're done.

Everything covered in these lessons is available as a complete video tutorial with step-by-step instructions on my website at the link below. Live long and prosper, my friends.

Topic List

Types of barcode scanners
1D and 2D barcode symbologies
Using your cell phone as a barcode reader
Creating a product table with barcode field
Creating a form to scan barcodes and look up products
Scanning products into invoices in Access
Automatically adding scanned items to orders
Incrementing order quantities with barcode scans
Preventing duplicate line items for same product
Performing Google searches using barcode data
Printing Code 39 barcode labels
Installing a free Code 39 barcode font
Creating custom barcode labels in Access
Printing multiple copies of barcode labels
Creating buttons to generate and clear barcode labels
Using Google Charts API for QR code generation
Displaying QR codes in Access Forms
Downloading QR code images and saving locally
Displaying QR code images on reports
Printing employee name badges with QR codes
Synchronizing two open forms in Access
Building a searchable product list form
Adding barcode scan lookup to product list
Manually adjusting inventory via barcode scans
Selecting add or subtract inventory modes
Preventing negative inventory values
Tracking individual unit serial numbers
Creating subforms for serial numbers
Printing serial number barcode labels
Marking barcode labels as printed
Scanning serial numbers into orders
Handling separate line items for serial numbers
Preventing duplicate serial numbers on orders
Printing barcodes on customer invoices
Integrating barcode printing into developer database
Allowing custom number of barcode labels per product

Article

Welcome to this tutorial on using barcodes in Microsoft Access. In this article, you will learn everything you need to know to integrate barcode scanning and printing with your Access database. Even if you do not plan on printing barcodes yourself, you will find the lessons here useful for inventory management, tracking product codes, and managing unit serial numbers. Along the way, you will also discover interesting features like using the Web Browser control in Access and downloading images for use in your database.

This article assumes you have a solid foundation in Microsoft Access and are comfortable working with tables, forms, and basic VBA code. We will primarily focus on Access 365, but the lessons will also work with Access 2016 and 2019.

To begin, let us discuss the different types of barcode scanners and barcodes. Barcode scanners come in many forms, such as handheld USB models, wireless versions, and even mobile phone apps. Many USB scanners function just like a keyboard: when you scan a barcode, the data is typed directly into the active field. There are various kinds of barcodes as well. The most common are 1D barcodes like UPC and Code 39, which are suitable for simple product codes. There are also 2D barcodes like QR codes, which can store much more information such as URLs and long strings of text.

A practical place to start is by building a Product table in your Access database. This table should include a field for the product name, a field for the barcode (ideally a text field to accommodate letters and numbers), and possibly an image or description. Next, you can create a Product form with a textbox designed to accept a barcode scan. With your scanner ready, you simply scan a product code into the textbox. Use VBA code in the AfterUpdate event of that textbox to look up the product in your table and display relevant information.

Here is an example of VBA code you might use to find the product:

Private Sub txtBarcode_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Products WHERE Barcode='" & Me.txtBarcode & "'", dbOpenSnapshot)
If Not rs.EOF Then
Me.txtProductName = rs!ProductName
Me.txtDescription = rs!Description
'Add other product fields as needed
Else
MsgBox "Product not found."
End If
rs.Close
Set rs = Nothing
End Sub

In many business scenarios, you want to scan a product and add it to an invoice or sales order automatically. After scanning, the system should check if that item is already on the order. If it is, increase the quantity by one. Otherwise, add a new line item. Use VBA code to perform this logic in the order form. Here is an example:

Private Sub txtScanBox_AfterUpdate()
Dim productID As Long
Dim rs As DAO.Recordset
productID = DLookup("ProductID", "Products", "Barcode='" & Me.txtScanBox & "'")
If Not IsNull(productID) Then
Set rs = Me.OrderDetails.Form.RecordsetClone
rs.FindFirst "ProductID=" & productID
If Not rs.NoMatch Then
rs.Edit
rs!Quantity = rs!Quantity + 1
rs.Update
Else
Me.OrderDetails.Form.AddNew
Me.OrderDetails.Form!ProductID = productID
Me.OrderDetails.Form!Quantity = 1
Me.OrderDetails.Form.Update
End If
Set rs = Nothing
Else
MsgBox "Product not found."
End If
End Sub

Sometimes you want to look up product information online, for example, to display details from a Google search. You can use the Web Browser control in Access and send the scanned code to Google automatically. Set the control's URL to something like "https://www.google.com/search?q=" & Me.txtScanBox after scanning.

Next, let us look at printing barcode labels. The easiest way to print barcodes from Microsoft Access is to use a barcode font. Code 39 is a popular and free barcode font that works well for Access databases. Download and install the Code 39 font on your system. In your label report, add a text box for the barcode. Code 39 requires an asterisk at the start and end of the code, so set the control source to "*" & [Barcode] & "*", and set the Font Name property to "Free 3 of 9" or whatever your Code 39 font is called. This will make Access render the barcode correctly for your label printer.

If you want to print multiple labels for the same product, you can make a separate labels table with a field for the barcode and a field for the quantity. Add a button to your product form that adds as many records as needed. After printing, you can clear the table with a button click so it is ready for the next batch.

You can also display 2D barcodes such as QR codes. These are useful for encoding more information, such as product details or URLs. Microsoft Access cannot natively generate QR codes, but you can use the Google Charts API, which generates QR code images from a URL. Place a Web Browser control on your form and set its ControlSource like this:

="https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & [YourDataField]

This URL generates a QR code representing whatever is in YourDataField. To use the generated QR code as a static image (for reports or badges), download the image file locally using VBA. For example, you can use the following code:

Dim strURL As String
Dim strFile As String
strURL = "https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & Me.txtData
strFile = CurrentProject.Path & "\QR_" & Me.txtData & ".png"
URLDownloadToFile 0, strURL, strFile, 0, 0

Display the image control in your report with its ControlSource set to the local file.

If you want better inventory control, you can create a form for adjusting inventory amounts. Place a combo box that lets you choose an action (look up, add to inventory, subtract from inventory) and a barcode scan box. When you scan an item, run VBA code to either increment or decrement the inventory for that product, ensuring values do not drop below zero. For example:

If Me.cboAction = "Add" Then
Me.txtInventory = Me.txtInventory + 1
ElseIf Me.cboAction = "Subtract" Then
If Me.txtInventory > 0 Then
Me.txtInventory = Me.txtInventory - 1
Else
MsgBox "Inventory cannot go below zero."
End If
End If

For businesses that track serial numbers, not just product codes, you will want to create a serial number table linked to your products. For each product, you can add individual records representing each unit with a unique serial number. Use a subform to display the serial numbers for a product. When you add units to inventory, generate serial numbers, print barcode labels for those serial numbers, and mark which ones have been printed.

Later, when processing orders, scan a product or serial number barcode. The system will check whether the scanned code matches a product or a serial number. If it is a product barcode, update the order as before. If it is a serial number, add it as a unique line item and check to make sure it has not already been used on another order.

Finally, combine the barcode printing and scanning features into your main developer database. Ensure that you can print any number of barcode labels quickly and easily and keep your inventory database organized and efficient.

Through these steps, you can implement a powerful barcode system into your Access database. It will allow you to easily track products, manage inventory, and streamline order entry processes. By leveraging barcode fonts, web services, and VBA, you can transform your workflow and reduce manual data entry errors. Remember to always test thoroughly and verify your barcode scans match the correct records in your database.