Batches setup in tables

[AndyC121]

Good morning Steve,



I must say a big thank you for all your patience and especially your hard work to date...it looks great. Not forgetting a thank you to all who have helped me so far. I'm just getting my head around the changes and the code.
The customer Batch or Product codes cannot be used for barcodes, (these are Not unique and the customer sometimes re-uses them...long story) the only unique identifier is ProductRecievedID_PK? so problem I see is that the repackBatchBarcode generates numbers 0001 to X and is linked to ProdRecievedID_FK. there could be multiple boxes in the factory labelled 0001 or 0002 etc?.

[ssanfu]

The customer Batch or Product codes cannot be used for barcodes, (these are Not unique and the customer sometimes re-uses them...long story) the only unique identifier is ProductRecievedID_PK? <snip>

Yep, I missed that. There is a field named "ProdBarcode" that would be for the bar code of the incoming batch.

<snip> so problem I see is that the repackBatchBarcode generates numbers 0001 to X and is linked to ProdRecievedID_FK. there could be multiple boxes in the factory labelled 0001 or 0002 etc?.

I envisioned the repack boxes label to have both the customer received batch bar and the bar code for the repack box.
Of course, you can develop a bar code scheme for the repack bar codes any way you want/need.

Same for the look and feel (L&F) of the form. I tried to keep the same L&F when I made the changes.....


Does a specific customer batch ALWAYS get repacked to a specific number (say 10)? Can a customer have different products that get repacked in different quantities? If you, would need another table to be able to select different quantities.


Added rec'd bar code control...

[AndyC121]

Morning Steve,
Its the traceability of each box around the factory..I've been racking my brains constantly of how this could work even in my sleep haha!. The work you did on the repack Batch Barcodes is perfect but it needs to somehow represent the original batch after its repacked. Maybe adding the PK first. eg ProdRecievedID_PK first entry in table is (1) so Repack batch Barcodes start with 1001, 1002, 1003....Next record ProdRecievedID_PK entry in table is (2) so Repack batch Barcodes start with 2001, 2002, 3003....
Would this work, can it be done? Its so I can identify every box and where its is/at what stage in the factory process. Hope you can see what I'm trying to achieve...Sorry to be a pain

[ssanfu]

I wouldn't use "ProdRecievedID_PK" because it is an autonumber. An autonumber is unique, but not guaranteed to be consecutive nor positive.
You might read:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers


What is the length of a barcode (how many characters)?
How long (days, months,years) would it take to max out the barcode system?
Is the repacking (entering into the computer) done in more than one place?
How many customers do you have? Active and inactive....

I've worked with bar codes/scanners in total about 2 minutes.
Is your bar code system numbers only, or can letters also be used?

[AndyC121]

greetings Steve,

What is the length of a barcode (how many characters)?

It can be any length, numbers and/or letters.

How long (days, months,years) would it take to max out the barcode system?

Honestly! no idea but records can be archived as the database grows if needed once product has left the factory. wont truly know until I'm using it.

Is the repacking (entering into the computer) done in more than one place?

No, this will be done at goods in. Customer/product info etc will be entered into the database and the barcodes will be raised and printed for that batch at this point.

How many customers do you have? Active and inactive....

Only active customers will be entered and there are approx 8.. but lots of product.

The barcode part of the database is okay, I worked on this before and tested parts of it. alittle more work needed but just tweeks.

Each work area in the factory has a barcode next to it (Goods In -example) so the employee scans the box containing customer product and then scans the workstation barcode (Goods In), this data is captured in a table with time and date stamp. I can then run queries from this to ascertain where the product is in the chain of work that's why I was trying to individualise each box and wanted unique ids on them.
Its just the repack batch barcodes unique to the original reference.

[AndyC121]

As you said...many times in plain English, use the prodRecievedID_PK and RepackBatchBarcode then concatenate and I get the unique ID...couldn't see the wood for the trees!.
Problem I have now, the format on the report needed for printing my labels... *in front of numbers and * at the end for the scanner to read the barcodes. Any idea's?

[ssanfu]

How many customers do you have? Active and inactive....

I asked because of your question about using barcodes beginning with 1 for the first customer, 2 for the second customer, ....
You could use letters for each customer: A for the first customer, B for the second customer, .... A10001, A10002, B10001, B10003

Problem I have now, the format on the report needed for printing my labels... *in front of numbers and * at the end for the scanner to read the barcodes. Any idea's?

Maybe
1) In the control before update event, add the asterisks...??
2) When you print the bar codes, add the asterisks...??

As you said...many times in plain English, use the prodRecievedID_PK and RepackBatchBarcode then concatenate and I get the unique ID...couldn't see the wood for the trees!.

No, I wouldn't use the prodRecievedID_PK (it is an autonumber). I was suggesting using the original (customer) bar code.
So you would print:
"the original (customer) bar code", a dash, "then your generated RepackBatchBarcode"


Again, I don't know what I am talking about, so I'm just tossing ideas out there.

[AndyC121]

Hi Steve,

Getting there. I have been working on the barcoding side of things and I think Its almost complete (removed from this zip)

Printing current record with concatenated fields and sub form is proving to be an issue, tried various ways after lots of reading but missing something. When you hit button "print barcodes" it pulls "barcode" report but this displays all records also tried concatenate direct on the report as well as queries. I want just current record Repack Batch Barcodes to print from current record.

[ssanfu]

I have it working, but I have some concerns.

To create the bar code, you are using

"*" & [ProdReceivedID_FK] & [RepackBatchBarcode] & "*"

You are using "ProdReceivedID_FK" (and ProdReceivedID_PK) and the "RepackBatchBarcode" to create a printable bar code.
The problem is that ProdReceivedID_PK (ProdReceivedID_FK) is an autonumber and doesn't (and shouldn't) have any real world meaning.

And because it is an autonumber, think about a year from now. Every new entry will cause "ProdReceivedID_PK" to increment. Lets say you have 3 customers and each customer has 3 products. There are 3 products per day per customer.... for 300 days.
What will the "ProdReceivedID_PK" number be at by then? 3 customers X 3 products per day X 300 days. = 2700.
So now, instead of *10001*, *10002* and *10003*, you will/could have *27000001*, *27000002*, *27000003* for Customer A. Is that acceptable??

What are the maximum number or characters allowed in a bar code? 9? 12?

I would advise changing the code to create the repack barcode to add the "*" and store the entire "number" instead of generating the barcode with the asterisks on-the-fly. Much easier searching....

I also suggest having a form to enter new records and a form to select the records to print. (see attached )

Yes, I made a LOT of changes in the attached accdb. (code, queries and forms)

[AndyC121]

Once again Steve thank you for all you knowledge, help and work. I have the database running now and in the test stage..i will take your advice and look at other options for producing the barcode.
Andy

[AndyC121]

Back again!!. Ive tried moving "optimumBatchSize" from tblCustomers and added it to tblProducts. edited the code and row source but Cbocustomer on frmCustomer doesn't display correctly. what am I missing?

[ssanfu]

Post the dB so I know what changes you made???


Could the "OptimumBatchSize" field name be changed to "OptimumRepackSize" when it is moved to "tblProducts"?
It would be less confusing because two tables currently have a field named "OptimumBatchSize" with different values.

[AndyC121]

Hi Steve,
I will post the database to you if you send your email (has a lot of confidential data etc in it now) I may not of explained what I am trying to do well enough. I want to Move the "optimumBatchSize" from the customer table, it will work better on the individual product and allow me to set the batch size for individual product rather than each customer.

[ssanfu]

Maybe this???

[AndyC121]

Exactly what I was trying to achieve. I see now where I was going wrong also. thank you
Andy