Read the code in the ON CLICK event of the 'view report' button.
Note, this code will break down if it's possible for an item to fill more than 2 trucks
i.e. 28 pallets (1 in truck a, 26 in truck b, 1 in truck c)
Code:
Private Sub cmdReport_Click()
Dim rst
Dim iPalMax, iPalCurr, iQty, iPage As Long
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblBase WHERE [ORDERID] = 1 ORDER BY [ItemID]")
iPalMax = 26
iPalCurr = 0
iPage = 1
CurrentDb.Execute "DELETE * FROM tblBaseReport"
Do While rst.EOF <> True
iQty = rst!orderqty
If iPalCurr + iQty <= iPalMax Then
CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum) VALUES (" & rst!OrderID & "," & rst!itemid & "," & iQty & "," & iPage & ")"
iPalCurr = iPalCurr + iQty
If iPalCurr = iPalMax Then
iPage = iPage + 1
iPalCurr = 0
End If
Else
CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum) VALUES (" & rst!OrderID & "," & rst!itemid & "," & iPalMax - iPalCurr & "," & iPage & ")"
iQty = iQty - (iPalMax - iPalCurr)
iPage = iPage + 1
CurrentDb.Execute "INSERT INTO tblBaseReport (OrderID, ItemID, OrderQty, PageNum) VALUES (" & rst!OrderID & "," & rst!itemid & "," & iQty & "," & iPage & ")"
iPalCurr = iQty
End If
rst.MoveNext
Loop
DoCmd.OpenReport Replace(Screen.ActiveControl.Name, "cmd", "rpt"), acViewPreview
End Sub