Using barcode scanners with Access database

[Guest]

We are converting our inventory over to Microsoft's Retail Management System.
We will also be tracking certain items through a Microsoft Access database.
Our company designs silk screen heat transfers for the imprinted apparel
market. We want to be able to utilize a barcode scanner in this work.

We want it to be able to be used for ,say, a way for an invoice to be
checked off at a certain process. For example, one of our CSR's barcodes the
invoice using her own personal barcode after the order's credit card has been
cleared and it flags the invoice's record field at "T" or something similar.
and it would also tag her name as the person who scanned the invoice with the
barcode scanner.

Just curious if anyone out there has a system in place utilizing Access with
barcode scanning. If so, please let me know some particulars of what you
do., plus suggestions on ways our company can utilize both. We are eager to
get started on this right away. I have already been able to track down an
Active X Control for displaying barcode labels in Access forms, so that's a
start.

Thanks for reading.

Ken

 

[Guest]

I did some contract work several years ago for a company called ASAP Systems,
based in California somewhere. I was converting a couple application from
DataEase to Access and they used bar code scanners for input. You could try
to contact them: www.asapsystems.com is their website. The person I dealt
with was named Brian Cockel, but unfortunately I do not know if he is still
with ASAP Systems.

Hope that helps !

 

[Guest]

I worked for a company where we used a barcode scanner in combination with
Access to check in/check out items from our rental inventory. Basically, the
scanner stored all information until it was set in a cradle, which then
transferred text files to the computer when requested to do so from a vb
script in Access. We'd then import that data into the database after
checking it for any errors (sometimes the scanner would read garbage...).
It was a standard text CSV file.

Our labels were also printed from an Access report utilizing a free standard
barcode font and a laser label printer. Sometimes Access would get confused
and try printing standard reports to the label printer, but we never spent
the time trying to track it down, just worked around it.

It worked pretty well considering they originally tried purchasing a
complete inventory tracking system but ended up ditching the software because
it was overly complex and whenever we wanted it to do something else, their
programmers charged WAY too much. We eventually just created our own
database in Access and made it work how we wanted.

Specific equipment we used was the PT2000 TopGun barcode scanner and
MSAccess 2000. I can't remember the particular brand of printer, but it
took rolls of labels with 20000 labels or something like that.

 

[Fred Boer]

Dear kennyharrill:

My small library application uses a barcode scanner. It is a "wedge" type,
which uses the same port as the keyboard. When you scan a barcode, it acts
as if the characters were typed on the keyboard. I use a barcode font to
print barcode labels from the laser printer.

There is a lot of information here:

http://www.granite.ab.ca/access/barcode.htm

HTH
Fred Boer

 

[Gary Walter]

We are converting our inventory over to Microsoft's Retail Management
System.
We will also be tracking certain items through a Microsoft Access
database.
Our company designs silk screen heat transfers for the imprinted apparel
market. We want to be able to utilize a barcode scanner in this work.

We want it to be able to be used for ,say, a way for an invoice to be
checked off at a certain process. For example, one of our CSR's barcodes
the
invoice using her own personal barcode after the order's credit card has
been
cleared and it flags the invoice's record field at "T" or something
similar.
and it would also tag her name as the person who scanned the invoice with
the
barcode scanner.

Just curious if anyone out there has a system in place utilizing Access
with
barcode scanning. If so, please let me know some particulars of what you
do., plus suggestions on ways our company can utilize both. We are eager
to
get started on this right away. I have already been able to track down an
Active X Control for displaying barcode labels in Access forms, so that's
a
start.

Hi Ken,

We have Symbol scanners (on their desktop stands) at nearly all our
computers
which use the keyboard wedge design. We work with books, the scanners are
always "scanning," and if we stick a books sticker under the scanner, the
code
is sent to the computer like it was typed in from the keyboard.

We use these scanners for several Access programs.

For one, we have a stack of textbooks that need to be shelved (what
classes use these books?).

We use a continuous form bound to tblList with only one textbox set
up for input ("txtBarCode"). In its Change event, we wait until we have
received 13 characters from the scanner which is enough to get the first
9 numbers of an ISBN from the Bookland barcode. We then try to fill in
the other locked-and-out-of-tab-loop textboxes for the record from
"tblAdoptions" using a domain function (could be DLookup instead of DMax).

Once they have scanned the stack of books, we print a report in
reverse order of the scan that gives the classes for each book scanned
(using a query that joins "tblList" with classes table)



Private Sub txtBarCode_Change()
On Error GoTo Err_txtBarCode_Change
Dim varBarCode As Variant, varFullISBN As Variant

varBarCode = Me!txtBarCode.Text
'ignore until have collected 13 chars from scan
If Len(Trim(varBarCode & "")) = 13 Then
'first 12 chars of barcode are same for new and used books
'and our table only has new barcodes
varFullISBN = DMax("ISBNHyph", "tblAdoptions", "[MISBN]='" &
Mid(varBarCode, 4, 9) & "'")
'MsgBox "varFullISBN:" & varFullISBN & "varBarCode:" & varBarCode &
"mid:" & Mid(varBarCode, 4, 9)
If Not IsNull(varFullISBN) Then
'complete other fields in tblList for this barcode
'by looking up in tblAdoptions
Me!txtISBN = varFullISBN
Me!txtAuthor = DMax("Author", "tblAdoptions", "[ISBNHyph]='" &
varFullISBN & "'")
Me!txtTitle = DMax("Title", "tblAdoptions", "[ISBNHyph]='" &
varFullISBN & "'")
Else
Me!txtISBN = "Not Found"
End If
DoCmd.GoToRecord , , acNewRec
Else
If Len(Trim(Me!txtBarCode.Text & "")) > 18 Then
MsgBox "This BarCode cannot be resolved -- please retry."
Me!txtBarCode = Null
End If
End If


Exit_txtBarCode_Change:

Exit Sub

Err_txtBarCode_Change:
MsgBox Err.Description
Resume Exit_txtBarCode_Change
End Sub

For none-book applications, we use 3of9 barcodes to print/scan
(with 3of9 you can put nearly anything you want between
2 asteriks, so it really is the simplest to use).

For example, our Special Orders program prints out
a "who/what/where/how/why" page with the special order
number printed out in 3of9 barcode. This page follows the
order through "its life."

If the book is picked up, the page is put in a "picked-up"
hanging folder; if the book is reshelved because they did
not pick up by set time, the page is put in the "reshelved"
hanging folder.

At any time during the day, someone will collect these pages
and use "frmScanAList" (similar to above).

Private Sub txtSpecOrderID_Change()
On Error GoTo Err_txtSpecOrderID_Change
Dim varBarCode As Variant, varFullISBN As Variant, lngOrderStatusID As
Long, lngCustID As Long

varBarCode = Me!txtSpecOrderID.Text
'ignore until have collected 6 chars from scan
'format of report barcode: "*" & Right("000000" & [SpecOrderID],6) & "*"
If Len(Trim(varBarCode & "")) = 6 Then
lngOrderStatusID = DMax("OrderStatusID", "SpecialOrders",
"[SpecOrderID]=" & CLng(varBarCode))
Me!txtPreChangeOrderStatusID = lngOrderStatusID
Me!txtOrderStatus = DMax("Status", "OrderStatus", "[OrderStatusID]="
& lngOrderStatusID)
lngCustID = DMax("CustID", "SpecialOrders", "[SpecOrderID]=" &
CLng(varBarCode))
Me!txtCustID = lngCustID
Me!txtCustName = DMax("[FirstName] & ' ' & [LastName]", "Customers",
"[CustID]=" & lngCustID)
Me!txtStore = DMax("Store", "SpecialOrders", "[SpecOrderID]=" &
CLng(varBarCode))
varFullISBN = DMax("ISBNNoHyph", "SpecialOrders", "[SpecOrderID]=" &
CLng(varBarCode))
If Not IsNull(varFullISBN) Then
'complete other fields in tblList for this barcode
'by looking up in SpecialOrders
Me!txtISBN = varFullISBN
Me!txtTermName = DMax("TermName", "SpecialOrders",
"[SpecOrderID]=" & CLng(varBarCode))
Me!txtAuthor = DMax("Author", "SpecialOrders", "[SpecOrderID]="
& CLng(varBarCode))
Me!txtTitle = DMax("Title", "SpecialOrders", "[SpecOrderID]=" &
CLng(varBarCode))

Else
Me!txtISBN = "Not Found"
End If
DoCmd.GoToRecord , , acNewRec
Else
If Len(Trim(Me!txtSpecOrderID.Text & "")) > 6 Then
MsgBox "This BarCode cannot be resolved -- please retry."
Me!txtSpecOrderID = Null
End If
End If


Exit_txtSpecOrderID_Change:

Exit Sub

Err_txtSpecOrderID_Change:
MsgBox Err.Description
Resume Exit_txtSpecOrderID_Change
End Sub

Once a set is scanned, they select a "change of status" combox
and click on command button that runs an update query changing
status of each order that was scanned.

This "scanning" routine will also work just as well with someone
simply typing in the special order, but using the scanners means
"for-sure" accuracy.

Those are just 2 cases where we use the scanners, but maybe
they will give you some ideas.

good luck,

gary

 

[Gary Walter]

I forgot to mention that we use
fonts (no activex!!).

there are many free 3of9 barcode fonts
out there on the web.

 

[Arvin Meyer [MVP]]

I wrote a membership tracking application for the Central Florida Computer
Society that reads the member's cards to track meeting attendance. The
scanner we use has a USB input, and we use Code 39 fonts to print the
membership cards.

Also, have a look at Russ Adams' barcode website:

http://www.adams1.com/pub/russadam/share.html
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Thomas Lutz]

For printing bar codes from Access, you would be best off using a bar
code ActiveX control like the one fromTAL Technologies at the
following URL:
http://www.taltech.com/products/activex_barcodes.html
If you need to print bar codes, I would recommend that you download
the demo of the TAL Bar Code ActiveX control from the above URL and
take a look at the sample Access database that is provide with the
demo.


For reading bar codes into an Access application, you have two
choices.
Most bar code readers are available with one of two output options.
The first option is called "Keyboard Wedge" output where you unplug
your keyboard, plug the bar code reader into the keyboard port on your
PC and then plug your keyboard into the bar code reader. This
arrangement makes the bar code reader appear as it it were simply a
second keyboard. Your original keyboard continues to work as normal
however when you read a bar code, the data encoded in the bar code
appears to any application running on your PC as if it were typed in.
The keyboard wedge interface is extremely simple however it has a few
drawbacks. If you scan a bar code, the cursor has to be in the correct
input field in the correct application otherwise you end up reading
bar code data into whatever application has the focus. This can cause
all sorts of potential problems if the cursor is not in the right
place when the bar code is scanned. You also cannot "lock" a textbox
so that the user cannot type in data and force data to come in from
the scanner only.
The keyboard output also is limited in that you cannot modify the data
in any way before sending it into the program that is to receive the
data. For example, if you needed to parse a bar code message up into
pieces or remove some of a bar code message or add in a date or time
stamp you would not be able to with a normal keyboard wedge reader.

The best way to do what you want to do is to get a bar code reader
with an RS232 or "Serial" interface. With these types of bar code
readers, you connect the reader to an available serial port on the
back of your PC. You would then need a program called a "Software
Wedge" to take the data from the bar code reader and feed it to the
application where you want the data to go. The disadvantage to this
approach is that it is a little more complex however you gain much
more control over how and where your data ends up when you read a bar
code. With a Software Wedge, you can control exactly where the data
goes in the target application and you can also perform all sorts of
modifications on the data before it is sent to the application.
You can also set everything up so that the textbox where the data gets
written is locked so that only bar code data can go into the textbox.
In addition, you can minimize your application and have it running in
the background while you use the PC for other tasks and still have all
bar code data go into the database.

TAL Tehchnologies sells a product called WinWedge which is a Software
Wedge for Windows. For interfacing a RS232 bar code scanner to an
Access application, WinWedge is the best tool available.
Visit: http://www.taltech.com/products/winwedge.html
for more information about WinWedge.

TAL also sells a very high quality line of bar code laser scanners at
very reasonable prices. All their scanners are available with either
the keyboard wedge output or RS232 output. For scanners see:
http://www.taltech.com/products/bc_reader.html

This web site is also an extremely good place to obtain information
about bar coding in general be sure to look in the Resources section
of the site for a bar code tutorial.