Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 38

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

  1. #16
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    I was able to reproduce this on my database with basic information then it goes south when I try to elaborate to real life.

    First problem, ItemID is a short text of 11 digits with dashes such as 12-3456-7890-1. The code would not copy the string into tblBaseReport, but actually calculate it, so my example would become "-11335"

    Then it got worst when I tried to add other fields that I need, such as VendorID, which is also a short text.

    I need to add VendorID, ItemDescription, QtyToOrder [this is the pallet equivalent in individual units], NeededDate and LeadTimeDate.

    The field QtyToOrder would have to split equivalent to the pallet qty (OrderQty) split or instead I could calculate it back using the pallet qty X Multiples, then I would just need to add the "Multiples" field in both tables instead of "QtyToOrder".

    Here's how I modified the code with just VendorID.



    Click image for larger version. 

Name:	Capture.JPG 
Views:	23 
Size:	49.2 KB 
ID:	38471

    When I run it it gives me the following error (what is grayed out is the VendorID string)
    Click image for larger version. 

Name:	Capture.JPG 
Views:	23 
Size:	20.4 KB 
ID:	38472

    The other strange thing, if I revert the changes by deleting what I added and save, it still won't work unless I delete all, copy and paste your original code again.

  2. #17
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    item 1, change the data type in tblBaseReport so all the field types match your source data and all these problems should be moot.

    Any changes of data type will also have to be altered in the SQL statement builder to include ' or " marks

    Example:

    sSQL = "INSERT INTO tblTEST (TextField, NumberField, DateField) VALUES (""" & TextValue & """," & NumberValue & ",#" & DateValue & "#)"

    or, if your text strings never have a ' mark in them

    sSQL = "INSERT INTO tblTEST (TextField, NumberField, DateField) VALUES ('" & TextValue & "'," & NumberValue & ",#" & DateValue & "#)"

    I have no idea what you mean by:

    The field QtyToOrder would have to split equivalent to the pallet qty (OrderQty) split or instead I could calculate it back using the pallet qty X Multiples, then I would just need to add the "Multiples" field in both tables instead of "QtyToOrder".

  3. #18
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    My field types were ok but the error was coming from the SQL statement, VendorID worked after correcting per your instructions, I'll add the rest.

    Here's what I mean

    Data is as following

    itemID / QtyToOrder / Pallet Count / Sum of Pallets

    item01 / 10,000 / 2 / 2
    item01 / 5,000 / 1 / 3
    item02 / 30,000 / 30 / 33

    So where it splits at item02 = 23 (23+3=26) on page 1 and = 7 (balance) on page 2, it also has to split the amount of units it represents (QtyToOrder), therefore 23,000 on page 1 and 7,000 on page 2, but that would require complicating the code so instead I'm thinking of simply adding the field "Multiples", which I have in my DB, so I can just calculate it back like

    QtyToOrder=[Pallet Count] x [Multiples], so here 23 x 1,000 = 23,000 and 7 x 1,000 = 7,000

  4. #19
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    You don't need to do that. You can just take a percentage of the qtytoorder and apply that amount to your temp table as well.

    In other words your palletcount equates to the field orderqty in my example
    Just add another field to the temp table to hold the actual quantity of items something like 'Itemqty' then calculate how many items are in the group based on the number of pallets

    i.e. (30,000/33) = items per pallet
    items per pallet * 30 = number of items in the pallet in truck 1

  5. #20
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    That's another way but bringing the "Multiples" field in the table worked fine too.

    Everything works on the dummy report now and I added the fields I needed. I changed the "ORDER BY [IdemID]" with ORDER BY [NeededDate]". Sorting by ItemID was not pulling the trucks in the correct order of due dates (sooner first) but NeededDate does the trick. Except I have not yet figured out the "PageNum Footer", I see the grouping but not how to insert the footer yet.

    I have one more question, if I wanted to change the button with a combo box, how would I tell the combo box to trigger the event? As I am building these reports they will possibly grow to an uncomfortable level to keep buttons on the switchboard form so I'm thinking about triggering reports by selecting them from combo boxes instead.

    I found this thread on the subject
    https://www.access-programmers.co.uk...d.php?t=295138

    Thank you

  6. #21
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I posted screen shots of how you get the page break inserted into your report. Reference that to force a new page, if you can't find it in your ribbon bar do a web search for 'MS access <version number> insert page break"

    You have not mentioned any reports except this one, there is no need to have a button for every report, you just have to have a selection window for the order you want to print, in other words one combo box (to pick the order) one button to produce the report. You just have to change the criteria in the query


    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblBase WHERE [ORDERID] = " & forms!frmreport!cboOrderSelector & " ORDER BY [ItemID]")

  7. #22
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    I know how to insert a break and saw your screen shot but I am missing the footer for some reason. I have "PageNum Header" but do not have "PageNum Footer", can't figure out why yet.

    Yours
    Click image for larger version. 

Name:	Capture2.JPG 
Views:	23 
Size:	15.6 KB 
ID:	38475

    Mine
    Click image for larger version. 

Name:	Capture.JPG 
Views:	23 
Size:	14.0 KB 
ID:	38476

  8. #23
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    right click on your report
    choose 'sorting and grouping'
    a little window will appear at the bottom of your screen
    choose the group

    you'll have a little menu directly to the right where you can choose to show/hide group headers and footer.

  9. #24
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Found it, the selection was "without footer selection".

    The report is working like I needed which will save me time going forward and I learned a lot in the process. I have not tried figuring out the menu part yet, I'll have to finish my reports before working on that.

    Thank you so much for your generous help, I very much appreciate it.

  10. #25
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    mark the thread solved!

  11. #26
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by rpeare View Post
    mark the thread solved!
    This is working very well, I was able to tweak everything to my db, thank you again.

    This being said I do not fully understand how this code actually works and it bothers me... if you have time, would you mind describing your code?

    TIA

  12. #27
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    @ rpeare, today I came across a strange issue with this code. The loop does not seem to work if any line item is > 52 (2x iPalMax). It usually does not happen, except it did today because an item was not properly set up and came in with a huge qty to order, 1344 pallets.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	19.0 KB 
ID:	39694

    So in tblBase I have that 1 line with a 1344 order quantity and on the report table tblBaseReport, it introduced negative quantities in [OrderQty]

    The first page works, 18 pallets of item 1 then 8 of that "bad" item, 18+6 = 26.

    From there it's assigning 1336 to page (PageNum) 2 (1344-8) then -1310 to the second item because 1336-1310 = 26 while it should in fact split the remainder 1336 into multiples of 26 on each subsequent page.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	14.5 KB 
ID:	39693

    Here's the current full code

    Code:
    Private Sub Vendor_NameReport()
     
    Dim rst
    Dim iPalMax, iPalCurr, iQty, iPage As Long
     
    DoCmd.SetWarnings False
     
     
    CurrentDb.Execute "DELETE * FROM tblVendor_NameBase"
    DoCmd.OpenQuery "Vendor_NameTotblBase"
     
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblVendor_NameBase WHERE [ORDERID] = 1 ORDER BY [LatestNeededDate]")
    iPalMax = 26
    iPalCurr = 0
    iPage = 1
     
    CurrentDb.Execute "DELETE * FROM tblBaseVendor_NameReport"
     
     
    Do While rst.EOF <> True
        iQty = rst!OrderQty
        If iPalCurr + iQty <= iPalMax Then
            CurrentDb.Execute "INSERT INTO tblBaseVendor_NameReport (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 tblBaseVendor_NameReport (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)
            iPage = iPage + 1
            CurrentDb.Execute "INSERT INTO tblBaseVendor_NameReport (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 = iQty
        End If
        rst.MoveNext
    Loop
     
    DoCmd.SetWarnings True
     
    DoCmd.OpenReport "rptVENDOR_NAME", acViewReport
     
    End Sub

  13. #28
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    if you can post a sample of the database with something replicating this problem I will take a look but honestly I do not keep any of the stuff I write for this forum with some rare exceptions.

  14. #29
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    I can and I have that file ready, however I am unable to remove the file's personal information from it while at work, I'll see if I can do it from home tonight.

  15. #30
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,276
    There is a post in code repository here for randomizing data. You might be able to make use of it. On my phone now so can't look for a link.
    EDIT

    https://www.accessforums.net/showthread.php?t=77482
    Last edited by Micron; 09-10-2019 at 09:12 PM.

Page 2 of 3 FirstFirst 123 LastLast
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