Here's a sample that recreates the error, just click on the form button, it will open the report table where you can see it bugs out starting line 3 as no record per page should be larger than 26 in [OrderQty]
TEST_truckload.accdb
Thank you!
Here's a sample that recreates the error, just click on the form button, it will open the report table where you can see it bugs out starting line 3 as no record per page should be larger than 26 in [OrderQty]
TEST_truckload.accdb
Thank you!
the code assumes you're never going to have more than 2 trucks holding the same order ID so you have to provide for the case where it's not. you can do that simply by inserting a looping mechanism in the ELSE loop
All this does is after you've appended a partial fill on the first truck, check to see if the remaining quantity is greater than the maximum pallet load, if it is, just cycle through until it's not.Code:Do While rst.EOF <> True iQty = rst!OrderQty If iPalCurr + iQty <= iPalMax Then CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iQty & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)" iPalCurr = iPalCurr + iQty If iPalCurr = iPalMax Then iPage = iPage + 1 iPalCurr = 0 End If Else CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iPalMax - iPalCurr & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)" iQty = iQty - (iPalMax - iPalCurr) Do While iQty > iPalMax iPage = iPage + 1 CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iPalMax & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)" iQty = iQty - iPalMax Loop iPage = iPage + 1 CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum, VendorID, ItemDescription, Multiples, LatestNeededDate, LeadTimeDate, RefreshDate) VALUES (" & rst!OrderID & ",""" & rst!ItemID & """," & iQty & "," & iPage & ",""" & rst!VendorID & """, '" & rst!ItemDescription & "', " & rst!Multiples & ", #" & rst!LatestNeededDate & "#, #" & rst!LeadTimeDate & "#, #" & rst!RefreshDate & "#)" If iQty = iPalMax Then iPage = iPage + 1 iPalCurr = 0 End If End If rst.MoveNext Loop
Perfect, thank you so much!
Actually... this is not quite working yet.
What happens now is that it is does not always create multiples of 26 pallets when an item spreads over multiple pallets but not more than two pallets.
I'll post a revised version of the sample later tonight with data that illustrate that issue.
As you can see below the first pal is ok. Between PageNum 1 and 3, the common item is "item-2" and it causes page 2 to only take 25.
Similarly "item-1 " is common to page 2 and 3 and causes page 3 to only take 8 pallets.
When a single item spreads over 2 pages, it skips counting to 26 on the subsequent page containing the remainder of that item spread.
Here's a copy with the data set that reproduces this error. TIA.
TEST_truckload1.accdb
I went back and looked through this thread.
There's never an explicit statement that each item needs to be evaluated separately. For instance you may have 3 or 4 instance of a single item totally 5 pallets. Do you just care that the number of pallets of an item are covered for a specific item?
So let's say you have 2 items in your order
Item A 5 Pallets
Item B 21 Pallets
Item A 11 Pallets
Item B 2 Pallets
Truck 1 - 5 pallets Item A, 21 Pallets Item B
Truck 2 - 11 Pallets Item A, 2 Pallets Item B
In my code it may process them in the order above
But you could also evaluate them like
Item A 16 Pallets
Item B 23 Pallets
Truck 1 - 16 Pallets Item A, 10 Pallets Item B
Truck 2 - 13 Pallets Item B
if the second method is acceptable I think I would organize the code differently.
Sorry was was not clear enough.
I need to fill trucks by order of due date of each item in that order, they always have to be full (except the last one possibly)
The raw data is sorted by ascending due date so as the code loops down the list, it should split from top to bottom in multiple of 26, no matter what the item is and no truck should be less than 26 pallets but the last one. If an item at the top of the list is 275 pallets then the first 10 trucks will be of that item only and 15 pallets will be on truck 11, the rest filled with the following items down the list.
For example if my data sequence is as following:
item A = 58
item B =15
item C = 8
item A = 20
it would go like
Truck 1 = (58 >26) then 26 x item A
Truck 2 = (58-26 > 26) then 26 x item A
Truck 3 = (58 - 26 - 26 < 26) then 6 x item A + (6 + 15 < 26) then 15 x item B + (6 + 15 +5 =26) then 5 x item C
Truck 4 = (8-5 <26) then 3 x item C + (3+20 <26) then 20 x item A = 23, list is exhausted end of loop
The first code was actually better. It cracked when we had a very large amount on a single item but subsequent loads equaled 26. The second code generates many loads not equal to 26.