I pass a SQL statement to a report like this:
"Select [Date], [DateClosed],[MDR], [ShipperId], [ScrapCode], [Program], [PartNumber], [PartName], [QTY],[PartCost],[EXTCost], [Supplier], [MDRClosed] from tblSupplierDebitSummary Where [Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "# GROUP BY [Supplier], [MDR], [Date], [DateClosed], [ShipperId], [ScrapCode], [Program], [PartNumber], [PartName], [QTY],[PartCost],[EXTCost], [MDRClosed] ORDER BY Suppler;"
DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, , , , strSQL
And on the report I do this:
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
This all works great. Now I need to Group By Supplier and MDR and page each distinct supplier to a its own page. Hope that makes sense.
I have tried this approach but it does not work:
I have seen that you can right click on the report detail and choose sorting and grouping. I have tried that but get and error that supplier is invalid. I presume this is because the report does not know about the data?
Is there a way to do grouping, sorting and paging in VBA?