|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Barcode Scanning By Richard Rost 
5 years ago
Scanning Barcodes to Lookup Products In this video, 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. Coleman from Honolulu, Hawaii (a Platinum Member) asks: I would love to be able to use a barcode scanner with my Access database. At first, it would be nice to be able to just scan a product and have the details appear on the screen while I'm talking to a customer, but eventually I'd like to be able to tie it in to my order entry system. I know nothing about scanners. Can you help? MembersMembers will 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.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Barcode Lessons
Links
Recommended Products
Online ISBN Lookups
Online UPC Lookups
IntroIn this video, I will show you how to scan UPC and ISBN barcodes using a barcode scanner and look up products in your Microsoft Access database. We'll talk about different types of barcode scanners and barcodes, setting up your scanner with Windows, creating a product table and form in Access, and configuring your database so you can scan a product code and instantly display the product details. You'll also see how to set up a lookup field on your main menu form that works seamlessly with your barcode scanner.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's video is all about scanning barcodes. I'm going to show you how to scan UPC or ISBN barcodes and look up products in your Microsoft Access Database.Today's question comes from Coleman in Hanaluu, Hawaii, a platinum member. Someone says, I would love to be able to use a barcode scanner with my Access Database. At first, it would be nice to be able to just scan a product and have the details appear on the screen while I'm talking to a customer. But eventually, I'd like to be able to tie it into my order entry system. I know nothing about scanners. Can you help? Well, Coleman, you're in luck. I certainly can. I used to sell computer hardware back in the 90s and really early 2000s. I built my own barcode system using Microsoft Access, of course, and I scanned products for the things we'd sell off the shelf using UPC codes. We sold some books, so I had ISBN numbers in the database. I also put serial numbers on components of computers we'd buy. So, if a hard drive went into a computer, I could track that specific part using a barcode. If the customer brought the computer back, we could scan the barcode and make sure that was the hard drive that we sold with them. So, yes, I know a lot about barcoding. Let's go through some of the basics first. First, you have to decide what kind of scanner you want. There are corded scanners where you just plug it into a USB port. There are wireless scanners that again have a USB dongle, or they might run on Bluetooth. I recommend those, although I've got an old corded scanner here that I still use because I barely use it now that I'm doing online training. But even back in my PC hardware days, I still just had a wired scanner that plugged into the USB port. Of course, there are encounter scanners like the ones you see at the supermarket, and there are also things called portable batch scanners. These are scanners that aren't connected to a computer, but you can scan things. They're good for warehouses. They'll store an internal memory, like if you're doing inventory, you go beep beep beep beep, and then when you get back to the computer, you plug it in or you connect it and it will download what it just scanned. I haven't really used those much, but the first two, corded scanners and wireless scanners, are what we're going to talk about today. Now, of all the barcode scanners that are popular, there are laser scanners and image scanners. Laser scanners have that little red light that comes on. When you scan a barcode, usually you have to pull a trigger and then the little red light comes on. Those are usually good for scanning your normal barcodes that you find on products or on books. The image scanners, like the kind that you find in your cell phone, actually take a picture of the barcode and analyze it. Now, those can be used with what are called 2D barcodes. I'm going to talk about that in a second, like QR codes. Those are 2D barcodes. And if you want to get into barcoding, I'll recommend an app for you in a few minutes. You can do barcode scanning right on your Android phone, or I'm sure they have them for iPhones. I'm not an iPhone guy. I use Android, but as you can see there, I got one that I recommend. Types of barcodes: 1D barcodes are the typical bars that you see. They have bars in one dimension, a straight line, goes right across. 2D barcodes are a little more complicated. These require an image scanner. 2D barcodes can hold a lot more information. You can load them up with whole URLs to web pages, for example. 1D barcodes are typically the ones you find in inventory solutions and simple stuff that you do with your access database. That's what I'm going to show you in today's video. Yes, it's possible to do 2D barcodes with Access, but it's a whole lot more complicated, and that will be the topic of a future video. Now, even with just the 1D barcodes, there are a ton of different symbology, different kinds of barcodes. Fortunately, pretty much every barcode scanner that you're going to buy nowadays is programmed to understand the majority of the barcodes. You've got the UPC and EAN codes that are on pretty much every product made. One is mostly United States and Canada, UPC codes. The other ones, Europe and the rest of the world. But they're basically the same thing. One's 12 characters, one's 13 characters. Of these, there are a lot of different variants, like an ISBN for book numbers and so on. Then you've got code 39 and code 128 and a ton of different standards. I like to use code 39 myself. It's a very simple thing to install on your computer. You can get a free font that lets you produce the barcodes. I'm going to do a separate video talking about printing your own barcodes. In this video, we're just going to talk about scanning existing barcodes. But I'll put a link to my printing barcodes video down below. Code 39 is one that I like to use. It's nice and simple. The US Postal Service has their own PostNet code that sometimes you'll see on Mail Merges. There are all kinds of different barcodes. As I said, most handheld scanners that you're going to buy today will read pretty much all of these. Then we've got 2D symbologies. There are lots of these: QR codes, you'll see those all over the place. QR codes can have all kinds of information stored in them, usually web page addresses. You scan it with your phone and it takes you right to a web page. But there are lots of other ones: Data Matrix, Aztec, pdf417. You'll see these on UPS packages and stuff from Amazon and all kinds of different places. It's just another way to cram more data into a smaller space. Again, you will need an image-type scanner, not a laser scanner, to read these. But you can also read them with your cell phone too. Setting up your scanner is pretty easy. Like I said, I've got myself a simple little wired handheld barcode scanner. I've had this thing for probably 20 years, and it still works great. Plug it into the USB port. From what I've seen from all the newer ones, it's got all the same features. This technology really hasn't changed since the 90s. You plug it in, Windows will automatically configure it. Windows 10, Windows 8, even Windows 7 should work fine with most handheld USB barcode scanners. The drivers are built into Windows. You should see "setting up device," and then a second later it'll say your device is ready. Unless you buy some cool new image scanner that's got custom drivers, you shouldn't have to deal with that. I set up an image scanner for a customer a couple of years ago and it just was plug and play. Plug in, Windows recognizes it. Good to go. Then, you take your product, the book, like this one. You find the barcode on the back. Load up whatever application on your computer you want the scanned numbers to go into. I'll just load up Notepad, for example. You can just scan a bunch of stuff into Notepad. Push the little button, laser comes on, you scan the barcode, and there it is, right into your Notepad application. So, I've got my laptop set up with my little handy dandy barcode scanner, I got Notepad running, I got a copy of a book here written by some really cool dude. Wonder who that is. On the back of every book you'll find, of course, a barcode, an ISBN barcode. Take my little handy dandy scanner, just like this, you've seen this happen in stores 7 million times, and there it goes. There goes the number right into Notepad. Grab another book. This is one of my favorite books to teach yourself C. I learned a lot of programming on C in this book a long, long time ago. ISBN book on the back, and scan it, and there it goes. Now, it's important to note that every time I scan something, the number would come in and then it would be followed by an enter. So it would scan in the digits and then enter. We can use that in Access because now we can use that to either press a button, a default button in a form to do something, or to fire an after update event if you know VBA programming. I have seen some scanners that were configured not to do that. They scan in a certain number of characters and stop. Usually, there are some settings that come with the scanner, you can change. You will have to consult the scanner documentation if your scanner does not put that enter. You want that. If you want to see my recommendations for which barcode scanners to purchase, if you're looking to buy one or what cell phone apps I recommend to use your cell phone as a barcode scanner, go take a look in the description down below. I'll put some links down there for you. Now that we understand the basics of what barcodes are and how they work, let's go ahead and build a database that we can use to quickly scan a barcode and pull up what product that is. That's what Coleman wants to do first. Here I am in my TechHelp free template. Go download a copy of this if you want. It's on my website. It's a free download. There is a link down below. Let's start off by making a product table. Go to Create and then Table Design. ProductID, now that's going to be an AutoNumber. That's going to be saved in the table. Access handles it. Access maintains it. If you don't know about AutoNumbers, go watch my AutoNumber video. We're going to put an AutoNumber in pretty much every table. Now, that's not going to be involved with our barcoding at all. What we are going to have is a ProductCode. That's going to be short text because, yeah, you can use text too, especially if you're making your own barcodes. If you want to keep the barcodes short, you can incorporate letters too, e.g., A463. You can fit into four letters what might take you eight numbers. We'll talk more about that in the next video when we get into generating our own barcodes. For now, we're going to stick with barcodes that are on other products like UPC codes or ISBN numbers. Now put in the standard product stuff. Whatever information you want to store on your products. So we've got the product name. We've got a description maybe. That could be long text. We can put in the unit price (currency). We can put the quantity on hand (a number), whatever other stuff. A picture. You want to store a picture of each of your products? Go watch my images video. I show you how to attach pictures without actually attaching them in the database. Watch my imaging video. I'll put a link to that down below too. Let's save this table. This will be my ProductT. Close that. Let's make a form for it. I'll copy my SingleF. This is part of the template. If you didn't watch the video where I build the template, we make some blank forms down here that we can use and just copy these. Copy, paste, ProductF. This will be my product form. Let's open that guy up. Go to Design View. This is where the product is going to be. First thing we do is we bind the table. Go to All, Record Source, that'll be my ProductT. We can close that now. Let's bring in the fields. Click, shift-click, click and drag. I cover this in my Access Beginner 1 class, by the way. That's free also. The only reason I leave these guys in here is for the formatting, because I can do this: watch, click, and go to the Format tab, Format Painter, and then paint over that. I want to paint over the rest of them, so I'm going to double-click on it, hold it down, and go paint, paint, paint, paint. Just copy the format over. Then I can get rid of these. Cute little trick. ProductID. I'm going to left justify everybody. Left align. ProductCode. This is where we're going to scan our barcodes into. Full product name. Save it. Close it. Let's open up our product form. Let's put some stuff in. First, I'm going to tab here. ProductCode is where the barcode is. So grab your handy scanner, find a product. I'm going to grab a copy of my book, my Complete Idiot's Guide to Excel 2010. I'm going to grab it. I'm going to scan that barcode in. Now notice it scanned in the barcode, and it pressed enter, so it went right down to the next field. That's exactly what I want. Now I can type in the product name and the description. Complete Idiot's Guide to Excel 2010, whatever else I want, by Richard Rost, and so on. Price, I don't know what this thing goes for, 15 bucks. Quantity on hand, I got one left. Got rid of them all. Tab. Now I can go to the next product, scan something else in. Now, you're probably asking at this point, is there a way to grab the product information off the web? Yes. There are databases that have thousands and thousands of products in them. If it has a UPC code or an ISBN number, chances are it's in one of the free databases. One of my favorites is OpenLibrary.org. They have a database of all of the ISBN numbers for books, and they have an API where you can send it a URL like this. You should replace this number right here with whatever ISBN you want to look up. Now, I just pasted mine in and then hit enter, and it will bring back all the information on my book. That's a JSON file. I will cover how to read this and actually use Access to send that out and retrieve the information and to fill those values into your database in my barcoding seminar that's coming up. This does involve a little bit of VBA programming, but it is possible. I'll put a link to more information on the barcoding seminar down below. I'll include a couple of links down below for different ISBN lookups for books. Those are usually free. UPC codes: I do have one free one that I use from time to time. I don't do much of it to be honest with you. There are a lot of them online, but a lot of them charge a fee. If you just go online and do a search for UPC lookups, you'll find tons of them. In my barcoding seminar, I will show you how to do this. But for today, we're going to create a database where we scan in the product code and we type in the product information ourselves. Let's put one more in. Okay, just scanned in a UPC code. That's a bottle of Gatorade. So I just put in Gatorade. Price, I don't know, five bucks. So we've got a couple of things in our database here. Now, could you make a continuous form where you can just go scan, scan, scan, scan, scan and have it continue to scan? Sure. Set up a continuous form like my customer list here, where you can just go from field to field. If you want to make it so that you just go scan, scan, scan, all you have to do is come in here, take the properties for all of these text boxes, go to Other, where it says Tab Stop, say No for all of them except for one. Let's pretend FirstName is the barcode field. Make that guy a tab stop. Now, if you open up this form, you can go scan, scan, scan, scan, scan. I'm just hitting the Tab key. Scan, scan, scan. If you want to scan in a bunch of products at once, but still see all their information, for example, there is lots you can do. Now, how do I make a form so that I can scan in something and have it look up the product? That's the main focus for this class. Let's put a scan field right here on our main menu, Design View. Let's grab a text box, put it right there. We'll put the label above it. Make the label white. And let's make the label say "Product Code Lookup," like that. Right underneath it, we'll put the field that we'll scan stuff into, like that. This will be an unbound text box. Let's give it a name. Let's call it ProductCodeLookup, just like that. What we'll need to do is we'll scan into this field, then we'll need some way of opening up our product form to the product code that was scanned in. There are a couple of ways we can do it. If we know VBA, we could use an AfterUpdate event, but there's a simpler way too. We can create a default button on this form without any code. A default button is simply a button that is pressed if the user presses Enter, which happens to be what the database does after a barcode scan. It gives you all the numbers and then presses Enter. So, let's drop a button on here. Go to Form Operations, Open Form, Next. We're going to open up the ProductF. Find that, next. We're going to find specific data to display, next. Match the ProductCodeLookup with the ProductCode. That's a text box on the form we just created. Match that with ProductCode, next. Pictures fine, or Go, or whatever you want to put on here, Lookup, whatever. Next, give it a name, LookupButton, and then Finish. That's the button that will execute the search. Let's save this and test it real quick. Open this up. I'm going to, oh, I opened up the wrong thing. Well, that's a good idea, though. Let me copy that product code because I'm not going to actually scan it out with my scanner. I'm on a different computer right now, the scanner is on another computer. But let's pretend I scan it in, Scan, and then hit the Look Up button. Boom, there's my product, see? The button does that. Let's find the other one. Let's unfilter this. Let's get the other guy. Let's grab the Gatorade copy. Come over here, scan, and then hit Look Up. Boom, there's the Gatorade. Now, how can we get that to handle automatically? Design View. Open up the properties for this button. Go to the Other tab, there is Default, it's normally set to No. Set that to Yes. There are two types of buttons you can have on a form that are special: Default and Cancel. The Default button is pressed if the user presses Enter anywhere on that form, except in a text box where you can press Enter to go to a new line. If you're outside of that, if you're sitting anywhere else, if you press Enter, then the Default button kicks. The Cancel button will execute if the user presses Escape anywhere. That's good for a Close button. But I'm going to set the Default to Yes. Now, what's going to happen? If I open this up, if I scan that at the scanner, the scanner is going to insert an Enter at the end, which I'm going to press Enter on my keyboard and look at that, it executed. I'm actually going to go test this on my other computer right now that has the barcode scanner on it. Hang on one second. Here I am on the other computer that's got the barcode scanner on it. Here's the barcode scanner, got a little book right here. Ready to do my scan. Make sure you get your cursor sitting in the ProductCodeLookup box. Ready for the scan? Here we go, and look at that. I didn't do anything. It scanned it, it pressed Enter, and it looked up the product right there. So there you go. That's the basics of it. There's the basics of barcoding, 101. Different kinds of scanners, different kinds of barcodes. How to create a product table and form to store your products with their UPC codes or their ISBN numbers or whatever you want, or your own internal codes if you want that. We know how to create a field with a lookup button that our scanner will automatically push with that Enter to look up our products. Now, I've got a lot more coming with barcoding. In the extended cut for members, I'm going to show you how to scan a product in and have it automatically added to your orders. Go to customers, go to orders. We've done this before. If you've never seen this, go watch my invoicing video. That's free too. I'll put a link down below. The members have a little box down here where they can pick a product. I'm going to add a scan box. You can scan something in and then beep, it goes right up here into the order. That's going to be in the extended cut. Plus, I've got another free video coming showing you how you can print your own barcodes. I'll put a link to that down below as well. Lots more coming on barcoding. Want to learn more about scanning barcodes? Well, in the extended cut for members, I'm going to show you how to scan products directly to an order. Now, if you haven't watched my invoicing TechHelp video, go watch that. It's free. I'll put a link down below. What we're going to do is we're going to add a little "Scan Barcodes Here" box down there. Use your scanner, click scan. If it doesn't exist in the order already, it will drop it in the order. If it does exist in the order, it'll just increase the quantity. So you don't get those big, gigantic long receipts. Then I will also show you how to have the database say, "Hey, the barcode isn't in your product database. Do you want to Google it?" That will at least pop up a Google window with the product information you can copy and paste into your database. I will be covering in my full barcode seminar coming up soon, how to actually have Access go out to the web and get the product details. But that's later on. For now, this is all covered in the extended cut for members. Silver members and up get access to all of my extended cut videos. Here I am in the product database. Notice I've added a little box. This is "Scan Barcodes Here." Come down here and click on the scan barcodes. Got my book ready. Here we go. Scan it in. What happened? There it is. It's up in the order. Scan it again. Oh, it goes up. Look at that. Three, four, just like in the store. Got my empty bottle of Gatorade. Scan it, and we got Gatorade. Scan it, scan it, scan it. Here we go. That's all covered in the extended cut for members. How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you like this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What are the two main types of barcode scanners discussed in this video?A. Dot-matrix and inkjet B. Corded and wireless C. 3D and 4D D. Thermal and laser-jet Q2. Which of the following barcode scanner types is best for warehouse inventory when not connected to a computer? A. Corded scanner B. Wireless scanner C. Portable batch scanner D. Image scanner Q3. What is the most common output device that is typically used to see the result of a barcode scan? A. Calculator B. Barcode printer C. Computer application like Notepad or Access D. Cash register Q4. What is a key difference between 1D and 2D barcodes? A. 1D barcodes are circles; 2D barcodes are squares B. 2D barcodes can hold more information, often requiring an image scanner C. 2D barcodes can only be read by laser scanners D. 1D barcodes cannot be read by handheld scanners Q5. Which barcode type is most commonly found on books for identification? A. UPC B. QR Code C. ISBN D. PostNet Q6. What must you ensure your barcode scanner does after scanning a barcode in order to automate lookups in Microsoft Access? A. Adds a space after the code B. Sends a tab key C. Presses enter after the barcode D. Sends a shift key Q7. When building a product table in Access for barcoding, which field is typically used to store the barcode value? A. ProductID (AutoNumber) B. ProductName C. ProductCode (short text) D. Description Q8. What is the function of setting a button's Default property to Yes in an Access form? A. The button only appears for administrators B. It executes when the user presses Enter, such as after scanning a barcode C. The button auto-clicks every 30 seconds D. It highlights the button in red Q9. What happens if your barcode scanner does not enter a carriage return (Enter key) after scanning? A. The scanned value is erased B. It may not trigger desired events or actions in Access forms C. The product price is doubled D. The code is automatically formatted Q10. Which barcode symbology does the video author prefer for creating custom barcodes, mentioning it's easy to use with free fonts? A. UPC B. EAN C. Code 39 D. Aztec Q11. To look up product information on the web using a scanned ISBN, which free resource does Richard mention? A. ISBNdb.com B. OpenLibrary.org C. Amazon.com D. ProductHunt.com Q12. What tool is NOT needed if you are only scanning existing UPC or ISBN codes into Access? A. Barcode scanner compatible with Windows B. API for ISBN lookup C. Product table with ProductCode field D. Microsoft Access database Q13. What type of Access control is recommended for ProductCode on the product entry form to enable barcode scanning? A. Combo box B. Check box C. Text box D. Label Q14. For rapid multiple-scanning into a continuous form, what property should be set to 'Yes' only for the barcode field's text box? A. Visible B. Tab Stop C. Read Only D. Locked Q15. What is a benefit of using a continuous Access form for barcode entry? A. Each scan creates a new table B. You can scan multiple items in succession without switching forms C. It disables barcode scanning D. It increases system security Q16. When using a button to look up a product in Access by its ProductCode, what feature allows the scan + enter to execute that button automatically? A. The button's Caption property B. Making the button the Default button C. The AfterUpdate event of the ProductCode field D. Tab order setting Q17. Which of the following is required to automatically populate product info from online databases using scanned barcodes in Access? A. Manual typing of all product details B. An internet connection and some VBA programming C. Linking the Access table to Excel D. Enabling macros in Word Q18. What is a 2D barcode example commonly used for URLs? A. UPC B. ISBN C. Code 128 D. QR code Q19. What will scanning a barcode into a text box with an associated Default button do in the described Access form? A. Open a web page B. Automatically look up and display the product info C. Send data to Excel D. Add a new user Q20. Why is it important to read your barcode scanner documentation? A. To ensure you know how to replace the battery B. To understand and configure output options such as auto-enter after scan C. To enable network sharing D. To find the reset button Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-C; 7-C; 8-B; 9-B; 10-C; 11-B; 12-B; 13-C; 14-B; 15-B; 16-B; 17-B; 18-D; 19-B; 20-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. SummaryToday's video from Access Learning Zone is all about working with barcode scanners in your Microsoft Access database. I will explain how to scan UPC or ISBN barcodes and retrieve product information directly within Access.The inspiration for this lesson comes from a student who asked about using a barcode scanner with his Access database. His goal is to be able to scan a product and immediately display its details for customers and eventually connect barcode scanning with his order entry system. He also asked for advice on choosing a scanner and starting with barcoding, since this is completely new territory for him. Drawing from my experience working in computer hardware sales during the 90s and early 2000s, I built my own barcoding system using Access. We used barcodes for all the products on our shelves, including books with ISBN numbers and even individual computer parts with serial numbers tracked via barcode. This system made it easy to identify specific components, manage inventory, and provide better service. Let me start with the fundamentals. First, you have to select a barcode scanner. The most common options are corded scanners that plug into a USB port and wireless scanners that connect via USB dongle or Bluetooth. Wireless models offer greater flexibility, but even a basic corded scanner will cover the needs for most Access projects. There are also stationary scanners like those found in grocery stores, and portable batch scanners designed for inventory tasks in warehouse environments. These portable devices store scans in memory for later upload to your computer, which is perfect for large-scale inventory jobs but less relevant for typical small business needs. When looking at scanners, you need to know the difference between the two major types: laser scanners and image scanners. Laser scanners use a red light to read traditional barcodes and are perfect for reading standard barcodes found on products or books. Image scanners, such as those in your smartphone, take a digital picture and analyze the code, allowing them to read 2D barcodes like QR codes. If you are planning on working with QR codes or other complex barcodes, image scanners are required. Many smartphone apps can serve as barcode scanners as well, and I will share some recommendations for those as we go. Barcodes themselves come in two forms. 1D barcodes are the typical linear codes used on most retail products. They hold a small amount of information such as product IDs. 2D barcodes, such as QR codes, are more complex patterns that can contain significantly more data, including URLs. For everyday business tasks in Access, 1D barcodes are sufficient and much easier to integrate. I plan to cover 2D barcodes and QR codes in a later video. Within 1D barcodes, there are many different formats called symbologies. The most common types include UPC and EAN barcodes, which are used globally for products. ISBN numbers are a variant used for books. There are also formats like Code 39 and Code 128. Code 39 is my personal favorite because it is simple, and you can easily print your own barcodes with a free font in Windows. If you want to get into generating barcodes for your own labels, I have another video on barcode printing. For now, we will stick to reading existing barcodes. To set up your scanner, all you usually need to do is plug it in. Barcode scanner technology has barely changed for decades, so whether you are using Windows 7, 8, 10, or newer, drivers are usually built in. Windows should recognize the device automatically. Unless you are using an image scanner with specialized software, setup should be completely plug-and-play. Once everything is connected, you can start scanning. I like to demonstrate this using Notepad - if you scan a barcode, the number will instantly appear, followed by an Enter keystroke. That Enter is important because it can trigger events in Access forms, such as running searches or moving to the next record. If your scanner does not send an Enter after scanning, check the documentation to change this setting. Let's now build a simple product lookup table in Access. Start by creating a new table for products, including a ProductID as an AutoNumber, a ProductCode (which will store your scanned barcode), and other information such as product name, description, unit price, quantity on hand, and perhaps a link to a product photo. Once your table is set up, create a bound form for data entry. You can customize the form's design to fit your needs and set up the ProductCode field to receive input from the barcode scanner. To add real-world products, simply scan the barcode into the ProductCode field and fill in the rest of the product details. If you have physical books or products handy, you can scan each one, and the barcode number will appear in the ProductCode field, saving you the trouble of typing long serial numbers or codes. Some people ask if it is possible to automatically fetch product information online based on a scanned code. The answer is yes; websites like OpenLibrary.org offer APIs where you can look up book details using ISBNs. By constructing a URL with the appropriate code, you can retrieve product data in a format called JSON. Reading this information into Access requires a bit of VBA programming, and I will be covering this in more detail in a future seminar. Links for further information on ISBN and UPC lookups can be found in the video description. For now, focus on building your product database by scanning barcodes and recording product information manually. You can also scan items like bottled drinks and add their details to your table. If you want to batch scan items quickly, you can set up a continuous form in Access that allows rapid data entry. By adjusting the tab stop properties, you can make it so only the barcode field is active for tabbing, which lets you scan repeatedly without stopping. To provide a quick product lookup feature, create an unbound text box for scanning barcodes and a button to open the product form directly to the matching record. You can configure this button to be the default action so when a scan is made and Enter is sent by the scanner, Access will automatically search for the product and display its details. This is achieved by linking the value in the unbound text box to the product code in your table when the button is triggered. After setting everything up, place the cursor in the barcode input field, scan a product code, and Access will jump to the correct product record automatically without you having to click on anything. This method makes product lookup very efficient. To sum up, we have covered the basics of different types of barcode scanners and barcode formats, how to construct an Access table and form for managing products with barcode support, and how to set up a lookup field that works seamlessly with scanned input. This gives you a solid foundation for integrating barcode scanning into your business workflow. Also, in today's Extended Cut, I will show members how to scan products and automatically add them to an order. We will enhance the order entry system so that scanning a barcode can either add a new item to an order or increase the quantity if it already exists. I will also cover how the database can prompt you to search Google if a scanned barcode is not found. This is a practical and functional upgrade for point-of-sale or inventory systems. If you want to learn more about printing your own barcodes, I will have a follow-up video available. There are many more barcoding topics planned, so stay tuned for those. For those interested in further learning, consider joining as a member for more advanced tutorials, and remember that free video content and resources will always be available. If you have questions or want your query featured, submit them via my TechHelp page. 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 ListTypes of barcode scanners: corded vs wireless vs imageDifferences between 1D and 2D barcodes Common barcode symbologies (UPC, EAN, ISBN, Code 39) Plug-and-play setup of barcode scanners in Windows Testing scanner input using Notepad Configuring scanner for Enter key after scan Designing a product table for barcode storage in Access Creating a product form for barcode entry Scanning barcodes directly into Access forms Populating product information manually after scanning Using OpenLibrary API to look up ISBN product details Overview of online UPC lookup resources Designing a form for rapid barcode product entry Setting up a continuous form for bulk barcode scanning Creating a product code lookup field on a main menu Using a button to open a product form based on scanned code Setting a button as default to trigger on Enter after scan Demonstration of scanning and auto-lookups in Access forms |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access barcode scanners, corded, wireless, laser, image, cell phone, 1d, 2d, upc code, ean, code 39, code39, code 128, usps postnet, qr code, data matrix, aztec, pdf417, dotcode, default button property PermaLink Barcode Scanning in Microsoft Access |
