Automatic Label Numbering

[ChrisMore]

Hi everyone,

I'm hoping for some assistance in automating label numbers for pallets. I currently have a manually entered Pallet_Number field that stores the pallet number for each item on a shipment and groups the items together so they can be printed on individual pallet labels. However, each pallet label can only contain a maximum of 3 unique items, otherwise it affects the formatting and the information spreads across multiple label sheets when printing. Therefore, I also need a label number field so the items can be grouped by label number instead of pallet number, thus fixing the formatting issue when there are over 3 unique items on one pallet. The label number for each item on a shipment could be manually entered but I would like to explore the possibility of this being automated.

I have attached a screenshot of a mock up in Excel of my database Pallets table to help explain my problem, where I have the Label_Number field automated using the formula shown in the image. The column headings in blue are existing fields in the Pallets table, the yellow column is a helper column for the formula to work in the spreadsheet and the green column is the new Label_Number field to be added to the Pallets table.

As shown in the screenshot, example shipment 10001A consists of 9 pallets but a total of 13 labels because 3 of the pallets have one or more label, as these pallets contain over 3 unique items (Ordered_Item_ID). I have colored the rows to show how the item grouping works for each label in this example.

I am stumped at where to start with this one. I don't know if the Label_Number field can be automated as a calculated field in the Labels report's source query or has to be updated using code as a field in the Pallets table.

Any help would be greatly appreciated.

Thanks,
Chris

 

[arnelgp]

can you upload your sample xlsx file instead or you can copy the range and paste it on this forum so it is easier to convert to excel.

 

[Minty]

I can see a sensible pattern to work with but what I can't see is the source data.
In these circumstances it is often better to have two samples - The data you have to start with and the secondly the desired output.
So ideally we need two excel sheets - starting data, and expected final output.

The sample should include enough variations to cover all possible scenarios.

 

[arnelgp]

As shown in the screenshot, example shipment 10001A

does the Label resets to 1 for each shipment Number?

 

[ChrisMore]

can you upload your sample xlsx file instead or you can copy the range and paste it on this forum so it is easier to convert to excel.

It says xlsx files aren't supported so I'll have to paste it instead:

Pallet_ID	Order_Shipment_Number	Ordered_Item_ID	Pallet_Number	PalletCount	Label_Number	Pallet_Item_Quantity
0	0	0
1	10001A	100	1	1	1	10
2	10001A	101	1	2	1	10
3	10001A	102	1	3	1	10
4	10001A	105	1	4	2	1
5	10001A	100	2	1	3	10
6	10001A	101	2	2	3	10
7	10001A	102	2	3	3	10
8	10001A	100	3	1	4	10
9	10001A	101	3	2	4	10
10	10001A	102	3	3	4	10
11	10001A	100	4	1	5	10
12	10001A	101	4	2	5	10
13	10001A	102	4	3	5	10
14	10001A	106	4	4	6	1
15	10001A	107	4	5	6	1
16	10001A	108	4	6	6	1
17	10001A	109	4	7	7	1
18	10001A	103	5	1	8	15
19	10001A	104	5	2	8	15
20	10001A	100	6	1	9	10
21	10001A	101	6	2	9	10
22	10001A	102	6	3	9	10
23	10001A	100	7	1	10	10
24	10001A	101	7	2	10	10
25	10001A	102	7	3	10	10
26	10001A	105	7	4	11	1
27	10001A	100	8	1	12	10
28	10001A	101	8	2	12	10
29	10001A	102	8	3	12	10
30	10001A	103	9	1	13	15
31	10001A	104	9	2	13	15

 

[ChrisMore]

does the Label resets to 1 for each shipment Number?

Yes, each shipment has its own set of label numbers and pallet numbers starting from 1

 

[ChrisMore]

I can see a sensible pattern to work with but what I can't see is the source data.
In these circumstances it is often better to have two samples - The data you have to start with and the secondly the desired output.
So ideally we need two excel sheets - starting data, and expected final output.

The sample should include enough variations to cover all possible scenarios.

I feel like there is starting data and expected final output, unless I am misunderstanding you. The column headers highlighted in blue are starting data and the green column is the expected final output.

 

[arnelgp]

It says xlsx files aren't supported so I'll have to paste it instead

ok, i manually entered some data to this sample db.
open MainForm form and click "Generate Label" button.

 

[ChrisMore]

ok, i manually entered some data to this sample db.
open MainForm form and click "Generate Label" button.

Thanks, that's an amazing start! The trouble I can see is the 'PalletCount' field doesn't exist in my database's Pallets table. This was only a helper column for the excel formula to work. Therefore, I would need to add this field to the Pallets table and your code would also need to build that data.

The formula I used for the PalletCount column in my sample spreadsheet is:
=IF(D3>D2,1,E2+1)

 

[Gasman]

It says xlsx files aren't supported so I'll have to paste it instead:

Pallet_ID	Order_Shipment_Number	Ordered_Item_ID	Pallet_Number	PalletCount	Label_Number	Pallet_Item_Quantity
0	0	0
1	10001A	100	1	1	1	10
2	10001A	101	1	2	1	10
3	10001A	102	1	3	1	10
4	10001A	105	1	4	2	1
5	10001A	100	2	1	3	10
6	10001A	101	2	2	3	10
7	10001A	102	2	3	3	10
8	10001A	100	3	1	4	10
9	10001A	101	3	2	4	10
10	10001A	102	3	3	4	10
11	10001A	100	4	1	5	10
12	10001A	101	4	2	5	10
13	10001A	102	4	3	5	10
14	10001A	106	4	4	6	1
15	10001A	107	4	5	6	1
16	10001A	108	4	6	6	1
17	10001A	109	4	7	7	1
18	10001A	103	5	1	8	15
19	10001A	104	5	2	8	15
20	10001A	100	6	1	9	10
21	10001A	101	6	2	9	10
22	10001A	102	6	3	9	10
23	10001A	100	7	1	10	10
24	10001A	101	7	2	10	10
25	10001A	102	7	3	10	10
26	10001A	105	7	4	11	1
27	10001A	100	8	1	12	10
28	10001A	101	8	2	12	10
29	10001A	102	8	3	12	10
30	10001A	103	9	1	13	15
31	10001A	104	9	2	13	15

Just change the extension and say what the original was.

 

[arnelgp]

here is the change and i remove PalletCount from the table.

 

[ChrisMore]

here is the change and i remove PalletCount from the table.

Amazing, thanks so much. I'm just wondering, can the below part of the code be ordered by Pallet_Number?

            Set rs2 = db.OpenRecordset("select pallet_number, label_number from table1 " & _
                        "where ((order_shipment_number) = '" & ![order_shipment_number] & "') and " & _
                        "((label_number) is null) order by pallet_id asc;", dbOpenDynaset)

Reason is, if a Pallet_ID is added with a pallet number out of sequence to the rest, will the label number still be accurate? Pallet_ID 32 in the below table shows this not working correctly with my formula in this scenario.

Pallet_ID	Order_Shipment_Number	Ordered_Item_ID	Pallet_Number	PalletCount	Label_Number	Pallet_Item_Quantity
0	0	0
1	10001A	100	1	1	1	10
2	10001A	101	1	2	1	10
3	10001A	102	1	3	1	10
4	10001A	105	1	4	2	1
5	10001A	100	2	1	3	10
6	10001A	101	2	2	3	10
7	10001A	102	2	3	3	10
8	10001A	100	3	1	4	10
9	10001A	101	3	2	4	10
10	10001A	102	3	3	4	10
11	10001A	100	4	1	5	10
12	10001A	101	4	2	5	10
13	10001A	102	4	3	5	10
14	10001A	106	4	4	6	1
15	10001A	107	4	5	6	1
16	10001A	108	4	6	6	1
17	10001A	109	4	7	7	1
18	10001A	103	5	1	8	15
19	10001A	104	5	2	8	15
20	10001A	100	6	1	9	10
21	10001A	101	6	2	9	10
22	10001A	102	6	3	9	10
23	10001A	100	7	1	10	10
24	10001A	101	7	2	10	10
25	10001A	102	7	3	10	10
26	10001A	105	7	4	11	1
27	10001A	100	8	1	12	10
28	10001A	101	8	2	12	10
29	10001A	102	8	3	12	10
30	10001A	103	9	1	13	15
31	10001A	104	9	2	13	15
32	10001A	106	1	3	13	1

 

[arnelgp]

i added Order on the subform, Order by:

order_shipment_number, pallet_number

the same order i added to the vba code.

 

[ChrisMore]

i added Order on the subform, Order by:

order_shipment_number, pallet_number

the same order i added to the vba code.

Thanks very much for your assistance, I shall attempt to implement this into my database set up

 

[ChrisMore]

i added Order on the subform, Order by:

order_shipment_number, pallet_number

the same order i added to the vba code.

When I run the code it is updating the Label_Number field for all rows in the Pallets table.

How do I amend the code so only the rows are updated for a specific Order_Shipment_Number based on a combo box selection?

 

[Gasman]

Use that number as criteria in what gets updated?

 

[arnelgp]

i added a combobox on the Mainform for you to select.