gogetsome
asked on
Populate report with OpenArgs with Grouping
Hello,
I pass a SQL statement to a report like this:
And on the report I do this:
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If
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?
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
End If
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?
ASKER
Thank you mbizup for your assistance. Everything seems logical and works up to the last change.
these controls are not on the report and are on another form
strSQL = "[Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#"
these controls are not on the report and are on another form
strSQL = "[Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#"
Ok - that block of code should go on the form that opens the report (not in the report itself)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, the code works perfectly and much better than how I was rendering the report. Now I can take advantage of the sorting and grouping and keeptoghether. AWESOME!
One more question, I have sorting and grouping by supplier, but each row in the result set get it's own page. I need to have a page for each supplier. Can you see by my setting what I did wrong? OR is there another place I need to adjust to accomplish my goal?
One more question, I have sorting and grouping by supplier, but each row in the result set get it's own page. I need to have a page for each supplier. Can you see by my setting what I did wrong? OR is there another place I need to adjust to accomplish my goal?
ASKER
I figure it out! Thank you so much for your help.
Off hand, the settings look okay. Can you post a sample database, with any sensitive data removed or masked?
I will be out for several hours, but if you need a quicker response, go ahead and post that as a new question.
I will be out for several hours, but if you need a quicker response, go ahead and post that as a new question.
Good for you! Can you post what you did?
ASKER
By the image above I added a group header then selected the group header and set the Force New Page to Before Section in the properties window. Works like a champ.
Set the recordsource of your report to this query (no criteria)
Select [Date], [DateClosed],[MDR], [ShipperId], [ScrapCode], [Program], [PartNumber], [PartName], [QTY],[PartCost],[EXTCost]
Then set the grouping and sorting in your report as needed
Remove the reports Open Event code that sets the recordsource based on Open Args.
And change the code that opens the report to this:
Open in new window