Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Populate report with OpenArgs with Grouping

Posted on 2013-11-12
9
Medium Priority
?
322 Views
Last Modified: 2013-11-12
Hello,

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

Open in new window




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?
0
Comment
Question by:gogetsome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39642290
Try this...

Set the recordsource of your report to this query (no criteria)

Select [Date], [DateClosed],[MDR], [ShipperId], [ScrapCode], [Program], [PartNumber], [PartName], [QTY],[PartCost],[EXTCost], [Supplier], [MDRClosed] from tblSupplierDebitSummary ORDER BY  [Supplier], [MDR],  [Date], [DateClosed], [ShipperId], [ScrapCode], [Program], [PartNumber], [PartName], [QTY],[PartCost],[EXTCost], [MDRClosed]

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:

Dim strSQL as string
 strSQL =  "[Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#"
DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, WhereCondition := strSQL

Open in new window

0
 

Author Comment

by:gogetsome
ID: 39642336
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 & "#"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39642352
Ok - that block of code should go on the form that opens the report (not in the report itself)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39642364
Commenting the code so that it is clearer (it should be in something like a button click event on the form where the user picks the dates):


Dim strSQL as string
' Define the criteria, based on the dates the user has chosen on this form
 strSQL =  "[Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#"

' Open the report, based on the above criteria (the WhereCondition parameter)
DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, WhereCondition := strSQL

Open in new window

0
 

Author Comment

by:gogetsome
ID: 39642392
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?sorting and grouping settings
0
 

Author Closing Comment

by:gogetsome
ID: 39642519
I figure it out! Thank you so much for your help.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39642520
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39642522
Good for you!  Can you post what you did?
0
 

Author Comment

by:gogetsome
ID: 39642539
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.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question