Database with barcode scanner

[GaP42]

For each barcode type you can parse the string using functions as suggested in post #13. The reliability of doing that depends upon how well you know the structure of each type of barcode, and the type of data it represents. You mentioned (#16) something about Batch Number and Product ID not always being the same length - if you mean they are not of constant length - although they may have a different length from each other - then that creates a problem - and you need to know what the delimiter is used in each - (possibly those codes in brackets). MarkK however may have provided a mechanism that addresses the problem, built into the API.

 

[Emma35]

Hey all,
I wrote a library database a while back that contains a class that raises a BarcodeScan event. The Zebra CCoreScanner software exposes an API you can reference in Access, so this library references that API, and basically does all the legwork, but the upshot is that you no longer need to point the keyboard focus to a Textbox control or InputBox to handle your scan. Instead, you can handle the BarcodeScan event in your code, and just read the barcode internally, and decide what to do with the result without any concern about what user interface element is going to handle the result.

First, you unzip and set a reference to the attached database, MyScanner.accdb. Then, at it's simplest, you write code like this on a form...

Private WithEvents scan_ As MyScanner.cMyScanner

Private Sub Form_Open(Cancel As Integer)
    Set scan_ = MyScanner.New_Scanner
    scan_.OpenScanner
End Sub

Private Sub scan__BarcodeScan(ScanText As String)
    MsgBox ScanText
End Sub

MyScanner.cMyScanner is a PublicNotCreatable class in MyScanner.accdb. To get an instance of the class, call the MyScanner.New_Scanner method, and assign that result to a WithEvents variable in your code. Then call the OpenScanner method, and your scanner should beep. Then, if you scan something, you can handle that scan without respect to your user interface.

Cheers,

Thanks Mark.....this sounds great and hopefully it would solve the problem. Unfortunately i honestly wouldn't have a clue how to implement this method....it's a mile over my head with where i am with Access.

 

[Emma35]

For each barcode type you can parse the string using functions as suggested in post #13. The reliability of doing that depends upon how well you know the structure of each type of barcode, and the type of data it represents. You mentioned (#16) something about Batch Number and Product ID not always being the same length - if you mean they are not of constant length - although they may have a different length from each other - then that creates a problem - and you need to know what the delimiter is used in each - (possibly those codes in brackets). MarkK however may have provided a mechanism that addresses the problem, built into the API.

When the barcode is scanned into Access, those numbers do appear but not in brackets. The brackets only appear when it's printed. I would've thought that using numbers as delimiters wasn't the best idea

 

[Josef P.]

Is this a GS1 barcode (with application identifiers)?

GS1 DataBar Coupon - Wikipedia

en.wikipedia.org

GS1 General Specifications - Standards | GS1

GS1 General Specifications is the core standards document of the GS1 system describing how GS1 barcodes and identification keys should be used. Find it here.

www.gs1.org

(15) 240114 (10) 612768 (240) DA110587
AI 15 = Best before date ... N2 + N6
AI 10 = BATCH/LOT ... N2+X..20 (end with special key fnc1)
AI 240 = ADDITIONAL ID .. N3 + X..30 (end with special key fnc1)

 

[Emma35]

Is this a GS1 barcode (with application identifiers)?

GS1 DataBar Coupon - Wikipedia

en.wikipedia.org

GS1 General Specifications - Standards | GS1

GS1 General Specifications is the core standards document of the GS1 system describing how GS1 barcodes and identification keys should be used. Find it here.

www.gs1.org

(15) 240114 (10) 612768 (240) DA110587
AI 15 = Best before date ... N2 + N6
AI 10 = BATCH/LOT ... N2+X..20 (end with special key fnc1)
AI 240 = ADDITIONAL ID .. N3 + X..30 (end with special key fnc1)

I'm not sure Josef and nobody i've asked is sure either. Our IT dept thinks it's EAN 128.

 

[Josef P.]

Then test it.
Try to find the special character fnc1.

dim c as string
dim i as long
dim bc as string

bc = YourBarcode

for i = 1 to len(bc)
    c = mid(bc, i, 1)
    debug.print ">" & c & "<" , asc(c)
next

 

[Emma35]

Then test it.
Try to find the special character fnc1.

dim c as string
dim i as long
dim bc as string

bc = YourBarcode

for i = 1 to len(bc)
    c = mid(bc, i, 1)
    debug.print ">" & c & "<" , asc(c)
next

I have zero knowledge of VBA....what do i do with that code ?

 

[GaP42]

This site indicates GS1-128 and EAN-128 are the same: https://barcode.tec-it.com/en/GS1-128?data=1524011410612768240DA110587
Entered the code you supplied to generate the barcode, however 240 was not identified as a delimiter - which may mean the std was not followed?

 

[Josef P.]

fnc1 is not a visible character.

Test with URL in #28:
1524011410612768\F240DA110587
AI 10 must end with fnc1.

Free Online Barcode Generator: GS1-128 (UCC/EAN-128)

Free GS1-128 (UCC/EAN-128) Generator: This free online barcode generator creates all 1D and 2D barcodes. Download the generated barcode as bitmap or vector image.

barcode.tec-it.com

 

[Emma35]

Just an update guys.....i've contacted the manufacturer of the scanner to see if it's possible to change the delimiters from numbers to some sort of symbol like an asterix or something. It's a long shot but i can't see this being solved as long as those numbers are in the barcode.

 

[Josef P.]

If this is a GS1 barcode, then these numbers must be in the barcode so that you can evaluate the content.

 

[Emma35]

Fair enough Josef but i still have the problem of extracting the content from the barcode to the database. From reading a couple of the other posts, this would be easier if the delimiters were symbols....but i'll see what they come back with

 

[Josef P.]

An example (code is not optimized, but hopefully somewhat understandable):

 

[Emma35]

Thanks Josef, looks promising. I have a couple of questions : I scanned in the barcode below and it didn't separate the batch number from the product ID ?
Also, how do i get the results saved into a table ?

 

[Josef P.]

Please click on "check barcode characters" and check if there is a special character before 240.
Maybe in this barcode the FNC1 is not chr(29).

Have you checked the barcode online yet?
E.g. with https://products.aspose.app/barcode/recognize

 

[Emma35]

There are 2 'threes' before the 240....not sure if these are special characters ?
The link is blocked here where i work so can't check it online

 

[GaP42]

Emma - does 240 always precede/separate the batch no / product ID.
FYI: if it does
ASCII Code 240 In the ISO-8859-9 character set, ASCII code 240 is represented by the character ğ also known as the latin small letter g with breve.
Binary: 1111 0000
Decimal: 240
Hexadecimal: F0
Octal: 360
It represents the delimiter set up in generating the barcodes - which may not follow the GS1 / EAN barcode std.. for some reason.
Check a set of barcodes. Once you can break it up reliably you can then look at the problem of getting it into the db.

 

[Josef P.]

I meant that:

In this text is the chr(29) letter the FCN1.

Please take a picture of the barcode and test it online.
Maybe your scanner cannot pass the FCN1 key.

 

[GaP42]

So when i scan a barcode into Notepad it comes out like this 1524011410612768240DA110587
This barcode prints on the pallet sticker as follows (15) 240114 (10) 612768 (240) DA110587

The info is 240114 (BB Date) 612768 (Batch No) and DA110587 (Product ID)

Are the numbers in brackets some kind of separator ?

If this is the breakdown for one barcode, do other barcodes follow the same pattern? I would expect so, but you also indicated that there was some variability in length? - Post #16 you said:

The batch numbers and even the Product ID numbers will not always be the same length.

If these are not of fixed length are they padded out in the barcode? By what? If not are the (bracketed values) always the same for the same type of barcode? (You have 2 types I think to analyse) - as per post #10. You know what is represented in the barcodes, but you need to know the rules for parsing them.

 

[Emma35]

Emma - does 240 always precede/separate the batch no / product ID.
FYI: if it does
ASCII Code 240 In the ISO-8859-9 character set, ASCII code 240 is represented by the character ğ also known as the latin small letter g with breve.
Binary: 1111 0000
Decimal: 240
Hexadecimal: F0
Octal: 360
It represents the delimiter set up in generating the barcodes - which may not follow the GS1 / EAN barcode std.. for some reason.
Check a set of barcodes. Once you can break it up reliably you can then look at the problem of getting it into the db.

Yes (240) always precedes the Product ID. The two barcodes below are sitting on my desk and you can see the one at the bottom is actually longer so they're not always the same

1524011410612768240AD23051274
15240502107051533240AD86515786