Ok well the first barcode contains the following
Product Name
Product ID
Batch Number
The second barcode contains
Product Weight
Pallet ID
Don't ask me why there needs to be two barcodes but i'm stuck with it unfortunately
Thanks again for your help
Thanks. My guess would be that there are too many bits of information to include them all in a single bar code, or that they are created at different times for different purposes. The first identifies the product coming of the production line; the second identifies the specifics for an individual pallet. That makes sense to me. It also suggests that there is potential for additional bar coding in the future should the organization decide to add a different type of information to each pallet, such as the customer for whom it is intended. See why I am allergic to putting repeating fields in tables? They make future extensibility much harder.
So, I would create, as previously suggested, a table for scanned bar codes and I would NOT try to input them through a single input field. They are two things, and they need to be handled as such.
The Barcode scan table would need a Primary Key, a Foreign Key to the Raw Material Table, a BarcodeType field, and a ScannedBarCode field. I see the two current BarcodeTypes as "ProductionCode" and "PackagingCode", or similar names that reflect the two different purposes.
The way I would do this would be to create a main form/sub form for data entry. The sub form can be relatively small as it needs only two field and two records.
The main form is bound to the raw materials table. The sub form is bound to the barcode table. The Link Master Fields/Link Child Fields property is set to the Primary Key and Foreign Key pair that related raw materials to bar codes.
Users put focus on the subform control and select the BarcodeType from a dropdown. Then they put focus on the barcode field for that record and scan the appropriate barcode. Then they put focus on the dropdown and select the other BarcodeType. Focus goes to the barcode field for that record and the other barcode is scanned. You can automate some of this so that focus automatically moves from control to control.
The alternative, in which both barcodes are scanned into a single text control on a form, requires some interesting logic. First, you have to validate the entry. It has to contain two and only two barcodes. There has to be one barcode of each type, Production and Packaging. They have to be in the proper sequence, although you could probably add a logic step to inspect the raw input and decide which of the two parts is the Production code by comparing it to a pattern.
You'd probably need to add a step in there to have the user insert a separator after the first and before the second scanned barcode to make splitting them easier. That might be automated, but I can't think of a simple way to do that off the top of my head. Maybe just a command button to click after the first scan, and a second command button to start the parsing and validation step in the VBA.
Once the input is split and validated, your code would then insert the appropriate scanned code into the appropriate fields in the table.
Yes, doable, but it falls under what I call the "Code Wad" approach to designing Access.
It's like this. A lot of things can be done pretty directly using standard table design and interface design, with enough code to smooth the operation.
Or, a lot of code, wads and wads of it, can be written to manipulate and manage data .
The latter sometimes can't be avoided, when tasks are truly complex. But being lazy means I'd much rather use the "out of the box" tools and methods and avoid writing a wad of new code.
