Basic inventory tracking example database

​

Setup for WinWedge

1. Select “Send Keystrokes to…” from the “Mode” menu in the Wedge, then enter: “Microsoft Access” as the Application Name and leave the “Command Line” box blank.
2. Set up the rest of the Wedge as needed for your barcode scanner and then activate it.
3. Note: You could also use BC-Wedge or a barcode scanner with a keyboard Wedge with this application.

​

Using the Access sample

What does this example do? This example demonstrates how to use Access in conjunction with a barcode scanner to look up product information and increment/decrement how many of that product are currently in stock. It is important to realize that this is just a sample of how you could implement an application of this sort. It is extremely unlikely that you would be able to use it “out of the box” to manage your inventory. While it is of course possible to change the values of the table (which currently lists TALtech’s products) to display your own products, the addition of new fields or renaming of existing fields will require a number of modifications to each of the forms, queries and subroutines in the database. Download the Inventory Sample for Access. Why use keystrokes mode instead of DDE? While it is possible to do this entirely through code and using DDE, there is really no advantage to doing so. The main advantage of using DDE with Access is simply to allow the operation to run reliably in the background (allowing you to work in other applications in the foreground while the data is quietly logged into a table for you to look at and analyze later), or to use multiple serial devices that transmit data at the same time. In an inventory application where you are scanning a barcode into your computer simply to retrieve information about a product, the Access application will need to be in the foreground to display the result and it is therefore unlikely that you would be using the PC for any other tasks at that time. It is equally unlikely that you would have two scanners connected to the same PC as Access allows multiple users to modify data at the same time so you could just as easily connect a single scanner to multiple PCs all of which are configured in keystrokes mode and updating data in the same database. All of the lookups and updates to the data in our example are done using queries rather than DAO code. These are much easier to manage, and anybody with a basic understanding of Access should be able to see at a glance how they work and modify them to meet their needs. Those same people may have little or no understanding of how to use VBA, DAO and other programming knowledge. Above all else, why spend hours writing code when you can achieve exactly the same end result using a query that took just minutes to set up?

​

How it works

Tables Our example has only one table, tblProducts, which contains just four fields – Product ID, Description, List Price, Quantity In Stock. In the real world, most databases would use more than one table to take advantage of Access’ relational database capabilities, but the principles are the same. Forms frmAddEdit is a simple form bound to tblProducts. This form allows you to add, edit and browse through the records in our table. frmLookup will look up the information in the table for the Product ID that you scanned in and display it in the format below: frmChangeStatus allows you to increment or decrement the number of the scanned/selected item currently in stock and displays the current number of in stock items. When the “Mode” is set to Increment, the number of in stock items will increase by one and when set to Decrement it will decrease by one. Note: While the value in the table is immediately changed, you may not see the form (or table if you have it open) update with the new stock value for up to 60 seconds due to the default refresh rate in Access. To change the refresh rate, go to Tools > Options > Advanced and change the “Refresh Interval (sec)” value to just 1 or 2 seconds.

​

How it works (behind the scenes)

frmAddEdit does not use any code and is simply bound directly to the table. This form was created using the New Form Wizard in Access and should not require any further explanation. frmLookup uses a simple query by form method to ‘look up’ and return the matching data from the table. If you open qryLookup in Design View, you will see that in the criteria section of the Product field it has a link to the drop-down box on the form: (If you try to run it without frmLookUp open, it will prompt you for a Product ID). Most of the code behind this form (and in the database as a whole) was written by the Access Wizards when the forms were designed, but to make it a little more robust we added some ourselves such as the code to highlight the text in the drop-down box ready for the next scan (to make sure the code is overwritten rather than having the next scan appear beside it). We used the After Update Event of the drop-down box on the form to run this query. Since the drop-down box is the only control that can receive focus on this form, it can be scanned into repeatedly without any special code or settings to move the focus to the right place after each scan. frmChangeStatus takes the query by form method a step further: as well as using criteria based on the value of the drop-down box it also uses an expression to add the value of the option group to the number of that item currently in stock. The “Mode” Option group has values of 1 and -1 so when added to the current number in stock it will increment or decrement it. You could of course change the values of the mode option group or add a text box allowing the user to type in the number to add or subtract from stock or simply to log an item’s status as in or out. The Change Status form has more than one control on it, so to prevent focus being shifted to any of the other controls only the drop-down box has its tab stop property set to yes. This prevents the cursor accidentally being shifted to another control between scans. The After Update Event of the drop-down box runs the query, seeks out the record for the selected product and displays the current stock count and then highlights the selected text ready for the next scan. Again, apart from highlighting the text, 90% of this code was written by the Access wizards. In an effort to ensure you receive as few errors or warnings as possible the warning that “You are about to run an action query, 1 row(s) will be affected…” is temporarily disabled at the top of the After Update subroutine and then re-enabled at the bottom. Finally, there is also some code to set the focus back to the combo box after changing the mode (to be sure that as little intervention as possible by the operator is required) in order to set it up ready for the next scan. There is only one module in this database and this contains a routine taken directly from the NorthWind Database example that ships with Access. Its purpose is simply to test to see if a form is loaded. We use it here to close the Add/Edit form before running the update query. This isn’t necessary, but if the Add/Edit form is open to the record that the update query is updating you may get a warning that the record you are editing is open by another user and may need re-editing.

​

Customizing the sample database

While we recommend you start from scratch with your own database and just refer to our sample as a guide, if you fully understand how our sample works it is certainly possible that you could save a little time by simply deleting our data, adding your own field names and redesigning the forms and queries. In order to give you some idea of what would need changing, let’s assume you plan to make the following changes:

• Add a Field to the table
• Rename the Product Field to ProductID
• Change the increment/decrement values to add and subtract 10 at a time.

This sounds easy enough, but take a look at how many items would need to be changed: After making your changes to the table design, you would need to open both of the queries and change the names of the Product field to ProductID and drag the new field onto the grid. Then you would need to open all three of the forms and change the Product Field’s control source property, change the row source property of the drop-down box, and drag the new field onto the Add/Edit Form. Assuming that you did not change the name of the drop-down box you should not have to modify the code behind it. Finally you would need to change the option values of the “Mode” Option group from 1 and -1 to 10 and -10 on the Change Status form. Missing any of these items would result in the database no longer working as it should and possibly tearing your hair out trying to figure out why. In many cases it would take longer to figure out what you missed than it would to have built a new database from scratch. For this reason, it is generally easier to start by building a new database to your own specifications rather than trying to bend and shape our sample to meet your needs.

​

Troubleshooting

When I scan the data into the Change Status form the stock number does not change. While the value in the table is immediately changed, you may not see the form (or table if you have it open) update with the new stock value for up to 60 seconds due to the default refresh rate in Access. To change the refresh rate, go to Tools > Options > Advanced and change the “Refresh Interval (sec)” value to just 1 or 2 seconds. When I scan the data into the Change Status form in quick succession I get the error: “The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.” This is also due to the default refresh rate in Access. To change the refresh rate, go to Tools > Options > Advanced and change the “Refresh Interval (sec)” value to just 1 or 2 seconds.