No they're not padded out in the code. The barcodes in the post above i actually used the scanner to do them on the screen. Yes the bracketed values are always the same
Database with barcode scanner
- Thread starter Emma35
- Start date
OK Emma - can you now write rules for how to parse the barcode (for Product)? You need to be able to do this also for the Pallet barcode too.
- the BBDate is always 6 char from the 3 character in the string. You now know that the Batch no will start at position 11 and is always preceded by 10. As the bar code sting length varies due to either the batch or the product number variations, then - from the information provided so far the product number begins with 2 alpha chars. If this is the case then these alpha char are always precede by "240". You can use this in rules to identify where to start reading the Product ID.
Not wanting to pre-empt the actual operation you want to support - see below, you need to design the tables in which the data will be stored and processed - a normalised table design. Just from what I have seen some of your tables will be
Scanned code - what you scan and capture as the barcode string, including barcode type (Product, Pallet)
Pallet - the details of the pallet - using the parsed info from the Pallet Barcode
Pallet - Product - the association of the products delivered on a pallet
ProductReceived - the details of the product received - using the parsed info from the Product Barcode and potentially able to be matched to a std product list / orders
The other part of your problem is how to get the barcode string into an Access application to carry out processing - such as the parsing. The response from Markk #20 would seem to provide a way in to read the barcode(s) into a database. Have you tried this?
I am not sure of the use case (scenario in which you want this process to work): basically do you envisage one or more scanners being used on the shop floor? Are you building an application to track batches and products arriving on pallets, is it to help maintain product inventory and product location?
1 Pallets - as they arrive. Apart from getting the pallet data what is to happen then? Does it link to other data? What other data?
2 Products - as a product is stored on site. Again how or what then is to happen with the product data you have obtained? Where do you get product details?
Do you have existing processes/ applications this is expected to operate with as a data source?
- the BBDate is always 6 char from the 3 character in the string. You now know that the Batch no will start at position 11 and is always preceded by 10. As the bar code sting length varies due to either the batch or the product number variations, then - from the information provided so far the product number begins with 2 alpha chars. If this is the case then these alpha char are always precede by "240". You can use this in rules to identify where to start reading the Product ID.
Not wanting to pre-empt the actual operation you want to support - see below, you need to design the tables in which the data will be stored and processed - a normalised table design. Just from what I have seen some of your tables will be
Scanned code - what you scan and capture as the barcode string, including barcode type (Product, Pallet)
Pallet - the details of the pallet - using the parsed info from the Pallet Barcode
Pallet - Product - the association of the products delivered on a pallet
ProductReceived - the details of the product received - using the parsed info from the Product Barcode and potentially able to be matched to a std product list / orders
The other part of your problem is how to get the barcode string into an Access application to carry out processing - such as the parsing. The response from Markk #20 would seem to provide a way in to read the barcode(s) into a database. Have you tried this?
I am not sure of the use case (scenario in which you want this process to work): basically do you envisage one or more scanners being used on the shop floor? Are you building an application to track batches and products arriving on pallets, is it to help maintain product inventory and product location?
1 Pallets - as they arrive. Apart from getting the pallet data what is to happen then? Does it link to other data? What other data?
2 Products - as a product is stored on site. Again how or what then is to happen with the product data you have obtained? Where do you get product details?
Do you have existing processes/ applications this is expected to operate with as a data source?
Last edited:
BTW Have you tried setting up a simple table in a test database containing a text field of sufficient length for your barcodes, and simply when the focus in on the text field, using the scanner to attached to the PC to scan a barcode?
You can also use this to load up a substantial set of your barcodes and test/verify your rules for extracting the substrings from the barcode string.
You can also use this to load up a substantial set of your barcodes and test/verify your rules for extracting the substrings from the barcode string.
Hi GaP42.....i basically want to be able to parse the barcode into the following fields in a table.
BarcodeNo
BBDate
BatchNo
ProductID
The database is for traceability where we can use queries to go back and track what products we used, their batch numbers and shelf life info. There is only one scanner in use. I have no idea how to write the VBA code to do this. We are using a scanner to enter the info into the database to avoid human error in typing in the batch numbers etc
Edit : Yes i've had a look at Mark's suggestion but it opens up with a page full of VBA and an error message which i don't know how to correct
Yes i have set up a quick form which is linked to a table as you described. The barcode scans into the txt field but i can't break up the information to populate the other fields (BBDate/BatchNo/productID)
Do you want / need the date and time at which the scan was performed? Will you only need to query your list using the productId, Batchno or BBDate? How will you ensure the barcode is only read once into your application?
Your basic table is
BarcodeScanID - an autonumber field in which a unique integer value is automatically generated for each record
ScannedBarcode - a TEXT field long enough to hold your longest scanned string, and set to unique - no duplicates - so no records will have the same barcode
BBDate - initially you might set this up as a TEXT string until your parsing of the string can present it as a date
BatchNo - Text string : even though it has only numbers in the same we have seen, is that always the case
ProductID - TEXT - it clearly contains a mix of alphanumeric characters.
To break up the barcode you are need to use/understand the following access functions:
Len - to obtain the length of a string eg len([ScannedBarCode]) to obtain the length of the barcode
Mid (string, start,[length]) allows you to extract the part of the string starting from a position, to a specified number of char)
eg Mid ([ScannedBarCode], 3, 6) as a formula that is applied to populate the BBdate. Use the expression builder to create this.
For the batch and product id the STRING length is not always the same length. You will need to apply some logic to work out where to begin and end (for batch no).
Batch numbers will always start at char 9 (following a "10"). How will you determine where they end? Perhaps, if batch numbers NEVER contain alphanumeric data you could use a function to find a string (240) - from position 9, and test whether the next char is alphabetic. (do Product IDs always start with an alpha char?) If so use that position, count back 5 char and the string from 9 to this position is the BatchNo.
(Relevant functions: LEN, INSTR, MID, IIF) in expression builder
ProductID, given that you have found the position of the first alpha char in the barcode, this (if the rule about the Product ID always starts with an alpha char) is the start position for reading the ProductID. you can then devise the formula for extracting the ProductID, using the same functions, or simply read the remainder of the code into the ProductID field (to len([ScannedBarCode]).
Give it a go.
Just one last word - if this is a single table, then you are not really using the capabilities a database - it could be done using Excel.
Good luck
Your basic table is
BarcodeScanID - an autonumber field in which a unique integer value is automatically generated for each record
ScannedBarcode - a TEXT field long enough to hold your longest scanned string, and set to unique - no duplicates - so no records will have the same barcode
BBDate - initially you might set this up as a TEXT string until your parsing of the string can present it as a date
BatchNo - Text string : even though it has only numbers in the same we have seen, is that always the case
ProductID - TEXT - it clearly contains a mix of alphanumeric characters.
To break up the barcode you are need to use/understand the following access functions:
Len - to obtain the length of a string eg len([ScannedBarCode]) to obtain the length of the barcode
Mid (string, start,[length]) allows you to extract the part of the string starting from a position, to a specified number of char)
eg Mid ([ScannedBarCode], 3, 6) as a formula that is applied to populate the BBdate. Use the expression builder to create this.
For the batch and product id the STRING length is not always the same length. You will need to apply some logic to work out where to begin and end (for batch no).
Batch numbers will always start at char 9 (following a "10"). How will you determine where they end? Perhaps, if batch numbers NEVER contain alphanumeric data you could use a function to find a string (240) - from position 9, and test whether the next char is alphabetic. (do Product IDs always start with an alpha char?) If so use that position, count back 5 char and the string from 9 to this position is the BatchNo.
(Relevant functions: LEN, INSTR, MID, IIF) in expression builder
ProductID, given that you have found the position of the first alpha char in the barcode, this (if the rule about the Product ID always starts with an alpha char) is the start position for reading the ProductID. you can then devise the formula for extracting the ProductID, using the same functions, or simply read the remainder of the code into the ProductID field (to len([ScannedBarCode]).
Give it a go.
Just one last word - if this is a single table, then you are not really using the capabilities a database - it could be done using Excel.
Good luck
Emma - I hope you have worked on the solution. I have attached a database with the capability to accept barcode and break it up into the items you wanted. See the associated form / control vba coding for review/consideration. It may be of interest. One table, one form and a module/function. Does not include a search form. Scan a barcode, processes it immediately and advances to the next record to accept another barcode.
Hi GaP42......i've actually been swapping messages with the manufacturer of the scanner itself to see if they can help. Thank you again for your time with this. I tried your database and when i scan a barcode it's actually parsing the barcode into exactly what i need and saving it to the table but for some reason, the barcode itself doesn't appear in the control at the bottom of the form nor does the BBDate, Batch etc appear in the other controls
If i could get this working then i think that would be the problem solved. The screenshot below shows the barcode scanned into the table and then broken up into the other fields perfectly. But you can see below the barcode control and the BBDDate/Batch and ProductID stay blank. Am i doing something wrong ?
Emma - the focus is on a new record - scroll back up to records already scanned to show the selected barcode parsed as needed. I set it up to automatically advance to the next record ready for scanning the next barcode, thinking this was how you might want to progress. If that is not what you want you will need to explain a bit more.
BTW I have not set up a scanner number as you said you only had one, however if you move to more scanners, then it would be good to have a table and identity for each scanner to show whether any issues arise from the use of one scanner or another...
The form has not been developed to be pretty - it is very basic. The list of records is shown in the body. Select any one record in the list in the body to show its details in the footer of the form.
You should introduce "cease scanning" button to allow review of scanned records, and an ability to search for .. whatever you need.
BTW I have not set up a scanner number as you said you only had one, however if you move to more scanners, then it would be good to have a table and identity for each scanner to show whether any issues arise from the use of one scanner or another...
The form has not been developed to be pretty - it is very basic. The list of records is shown in the body. Select any one record in the list in the body to show its details in the footer of the form.
You should introduce "cease scanning" button to allow review of scanned records, and an ability to search for .. whatever you need.
Last edited:
Hi GaP42.....as i originally mentioned in Post 1 there will be two barcodes to be scanned. The first one is the one you've been working on which breaks down into BBDate/Batch and ProductID. The second one contains PalletNo and Weight, so i need the first barcode to be parsed into the BBDate/Batch and ProductID controls and the cursor to move to the second barcode control which then parses the second barcode into the PalletNo and Weight controls. When this is done i want to be able to save the results as one record
There are now a few things that need to be done:
1. The database table will need additional columns to accommodate the extra barcode and storing it associated elements. The pattern from the first barcode, modified to suit, can be used.
2. The form will display these items and allow the capture of the 2nd barcode, and on scanning the first then set focus to the control to capture the second. After the second it can then move to the next new record. Use the processing rules for the first barcode to work through the logic for processing the second barcode. I have not seen a second (pallet) Barcode sample to comment further.
3. What logic will you use to determine which barcode has been scanned first? In fact, reread/reconsider what GPGeorge said in post #7. Pallets and Products are two different things and in the database belong in distinct tables that relate to each other - a Pallet contains multiple products. Scan the Pallet barcode then scan all the products on the Pallet? Then progress to the next Pallet? Workflow is being considered.
4. For the current situation you also may have the situation where the record is incomplete: one scan has been done but not the second, for whatever reason. How will you manage these records?
5. While one table currently, as mentioned you should have some supporting tables to hold the scanner info ("Scanner"), and the operator of the scanner ("Scanned by") - as suggested in the form. These two items need to be selected prior to commencing the scanning process so that the from has this information to add to the scanning record.
6. As mentioned earlier you will need facility / form to help you locate a record - whether by barcode or other attribute. This will not be the input form as provided (assuming you have a need to find an existing barcode - Pallet or Product) or the products of type that were delivered in a period on what pallets.. etc.
1. The database table will need additional columns to accommodate the extra barcode and storing it associated elements. The pattern from the first barcode, modified to suit, can be used.
2. The form will display these items and allow the capture of the 2nd barcode, and on scanning the first then set focus to the control to capture the second. After the second it can then move to the next new record. Use the processing rules for the first barcode to work through the logic for processing the second barcode. I have not seen a second (pallet) Barcode sample to comment further.
3. What logic will you use to determine which barcode has been scanned first? In fact, reread/reconsider what GPGeorge said in post #7. Pallets and Products are two different things and in the database belong in distinct tables that relate to each other - a Pallet contains multiple products. Scan the Pallet barcode then scan all the products on the Pallet? Then progress to the next Pallet? Workflow is being considered.
4. For the current situation you also may have the situation where the record is incomplete: one scan has been done but not the second, for whatever reason. How will you manage these records?
5. While one table currently, as mentioned you should have some supporting tables to hold the scanner info ("Scanner"), and the operator of the scanner ("Scanned by") - as suggested in the form. These two items need to be selected prior to commencing the scanning process so that the from has this information to add to the scanning record.
6. As mentioned earlier you will need facility / form to help you locate a record - whether by barcode or other attribute. This will not be the input form as provided (assuming you have a need to find an existing barcode - Pallet or Product) or the products of type that were delivered in a period on what pallets.. etc.
1. I'll be fixing the table over the next couple of days
2. After scanning the second barcode, i don't want the form to move to the next record. I know it's a bit silly but i've been asked to make the system look as much like the existing one as possible....so i'm going to add a Save button for the Operators to give them a sense of the record being added to the database
3. Each pallet will only contain one product and this will never change (it's actually not possible)
4. Haven't worked that out yet
5. The current Operator's name (an some other data) is actually being added elsewhere on the form.
6. When i finally get this thing scanning the records in correctly, i'll create a search function for Users to go back over the records and find what they're looking for.
Thanks again for the help with this GaP42......when i have something ready to trial i'll see how it goes and post again
Em
I will be interested to hear of your progress and wish you the all the best.
The scannedbarcode control (for product barcode) has the following in the AfterUpdate event
These are the ones to alter / delete to avoid moving to the next record and instead set focus to the pallet barcode ready to scan the second/ pallet barcode.
The record has been saved so a SAVE button will have no effect - so possibly a button for SCAN NEXT Product/Pallet and use it to move to a new record.
To determine which barcode has been scanned I hope the two types start with a different value: the Product Barcode always starts with 15. You can use this to set up some logic to raise a message - see how the existing code checks to see if scanned code starts with 15. Use similar for the Pallet Barcode and prevent the incorrect processing of the scan.
As this is after update - in the circumstance of the wrong barcode being captured - you will need to add logic to clear the incorrect scanned barcode and allow another scanning attempt - or abort the process: a message
If the operators don't share a login then the Scanned by will represent the operator by their network login name. If they do then it won't reflect what you want.
The scannedbarcode control (for product barcode) has the following in the AfterUpdate event
DoCmd.GoToRecord , , acNewRec ' scanned and accepted entries so jump to new Record for next scan
Me.ScannedBarCode.SetFocus
These are the ones to alter / delete to avoid moving to the next record and instead set focus to the pallet barcode ready to scan the second/ pallet barcode.
The record has been saved so a SAVE button will have no effect - so possibly a button for SCAN NEXT Product/Pallet and use it to move to a new record.
To determine which barcode has been scanned I hope the two types start with a different value: the Product Barcode always starts with 15. You can use this to set up some logic to raise a message - see how the existing code checks to see if scanned code starts with 15. Use similar for the Pallet Barcode and prevent the incorrect processing of the scan.
As this is after update - in the circumstance of the wrong barcode being captured - you will need to add logic to clear the incorrect scanned barcode and allow another scanning attempt - or abort the process: a message
If the operators don't share a login then the Scanned by will represent the operator by their network login name. If they do then it won't reflect what you want.
Hi GaP42.....Sorry for the delay with this. The supplier of the scanner rang me and tried a few things over the phone but didn't work out and is ringing me back. I've amended the table to also accept the PalletNo and Weight. I also deleted the line of code you suggested and now the info for the first barcode is appearing on the form when i tab out of the ScannedBarcode control. I can't amend the code to work for the second barcode though....way above my thick head ! Below is an example of how the second barcode scans. The first delimiter is (00), then the PalletNo and the second delimiter is (37), then the weight.
I think the good news is that the PalletNo is always the same length so does that make it easier to set up the code ?
0035052758101476390037150.000
I think the good news is that the PalletNo is always the same length so does that make it easier to set up the code ?
0035052758101476390037150.000
Hi Emma. Can you send
1. a set of barcodes (say at least 20) in a text file, each on a separate line, including both Pallet and Product. I will use that to look at the Pallet barcode structure.
2. What variation in weight do you expect - 0 to 1000 or more? The weight is in what units? And is weight always 3 decimal places?
3. Can you provide a copy of the current database - I will take a look to help with workflow/ validation.
Last edited:
FYI:
AI 00 = SSCC = Serial Shipping Container Code
This is an identifier defined with exactly 18 digits.
AI 37 may have different length. If it is in the middle of other AI codes, it has an FCN1 and end identifier.
@Emma35: I think you would save them a lot of effort and guesswork if you would deal with GS1.
AI 00 = SSCC = Serial Shipping Container Code
This is an identifier defined with exactly 18 digits.
AI 37 may have different length. If it is in the middle of other AI codes, it has an FCN1 and end identifier.
@Emma35: I think you would save them a lot of effort and guesswork if you would deal with GS1.
Last edited:
I have made some mods to the sample provided earlier - adding fields for Pallet and modifying the associated form, and the routine to accept, validate and parse the Pallet barcode. Check/test to see if this meets your needs - and adjust or import to yours.
I have made the assumption pallet weight is in kg.
You might consider adding an index to prevent duplicate entries for Product/Pallet. I assume you cannot receive a Product (a ProductID) on a Pallet with the same PalletID as a previous Pallet (with the same product?). Are Pallet IDs always unique. You earlier indicated that only one product is delivered on a pallet. If that is the case it would be useful to establish a unique index for PalletID or a composite unique index for PalletID and ProductID, as appropriate.
I have made the assumption pallet weight is in kg.
You might consider adding an index to prevent duplicate entries for Product/Pallet. I assume you cannot receive a Product (a ProductID) on a Pallet with the same PalletID as a previous Pallet (with the same product?). Are Pallet IDs always unique. You earlier indicated that only one product is delivered on a pallet. If that is the case it would be useful to establish a unique index for PalletID or a composite unique index for PalletID and ProductID, as appropriate.
Apologies Emma - reviewed the above version and noticed an embarrassing mistake (<> vs =) ... corrected in the attached and added some extra functionality.
The search page initially presents no records - as you do not want to retrieve 1000s when you are only looking for a small set.
The items available do not work in combination - ie search for a product or pallet barcode, a productID or batchID or palletID or best before or weight scanned date. (I think if you do you would find the search will work on the last listed item in the code - the reverse order to the items in this sentence.).
The best before and scanning dates allow you to end the from and to date range : if you only enter one value then the from date defaults to 1/1/1900 and the to date defaults ot 12/12/2999 (to capture anything before a date or since a specified date. Weight is also searchable as a range and if one is provided and not the other then from is 0 and to is 9999 (kg?)
You will also find you can toggle the sort order for the listed records in the barcode list by clicking on the heading for the item sorting by that item.
The search page initially presents no records - as you do not want to retrieve 1000s when you are only looking for a small set.
The items available do not work in combination - ie search for a product or pallet barcode, a productID or batchID or palletID or best before or weight scanned date. (I think if you do you would find the search will work on the last listed item in the code - the reverse order to the items in this sentence.).
The best before and scanning dates allow you to end the from and to date range : if you only enter one value then the from date defaults to 1/1/1900 and the to date defaults ot 12/12/2999 (to capture anything before a date or since a specified date. Weight is also searchable as a range and if one is provided and not the other then from is 0 and to is 9999 (kg?)
You will also find you can toggle the sort order for the listed records in the barcode list by clicking on the heading for the item sorting by that item.
Last edited:
Thanks a lot for your help GaP42. I'll work away on getting this running and hopefully come up with something they can use. The supplier of the scanner has also come back to me with a configuration which is not far off what i'm looking for so fingers crossed. Thanks again for all your time with this.
Em xx
