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