Using the Access Sample
What does this example do?
This example demonstrates how to use Access in conjunction
with a bar code 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 2000.
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 bar
code 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; whereas 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 setup?
How it works (on the surface)
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
and our table.
frmLookup will lookup the information in the table
for the Product ID that you scanned in and display at 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 and 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 lets
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.
Launching And Terminating WinWedge
From Access
Collecting Data Directly into
a Form in an Access Database
Transmitting Variables Out The
Serial Port From Access
|
