Page 3 of 3 FirstFirst 123
Results 31 to 36 of 36

Report, Calculate pallet qty to fill a truck load, page break and continue to next truck load

  1. #31
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    105
    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!

  2. #32
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    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

    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
    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.

  3. #33
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    105
    Perfect, thank you so much!

  4. #34
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    105
    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.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	6 
Size:	68.9 KB 
ID:	39746

  5. #35
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    105
    Here's a copy with the data set that reproduces this error. TIA.

    TEST_truckload1.accdb

  6. #36
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    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.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-24-2018, 04:45 PM
  2. Driver Paperwork/Truck DataBase
    By Daisy509th in forum Database Design
    Replies: 2
    Last Post: 03-22-2018, 08:47 AM
  3. Replies: 2
    Last Post: 02-04-2016, 08:13 AM
  4. truck loads and daily totals?
    By nickdixon14 in forum Access
    Replies: 12
    Last Post: 10-13-2015, 05:16 PM
  5. Truck transportation business orders
    By aurorist in forum Database Design
    Replies: 3
    Last Post: 01-12-2012, 05:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums