?
Solved

MS Access: How Can I pass the Sort Order of a subform to a report

Posted on 2015-02-09
5
Medium Priority
?
404 Views
Last Modified: 2016-02-11
How Can I pass the Sort Order of a subform to a report?  Right now, i am able to open the report passing the filtered parameters from the form to the report, but i ll also like to sort. So it passes the filter but not the sort order. Is there a practical wasy to also pass the sort order from the form to the report? The user can chose to sort by any column. Here is the code:

Private Sub CmdPreviewReport_Click()
    Dim stDocName As String
    stDocName = "Report_Name"
    DoCmd.OpenReport stDocName, acPreview, , Me.MainWorkloadQueryForm.Form.Filter    
End Sub
0
Comment
Question by:peterslove53
[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
  • 2
  • 2
5 Comments
 
LVL 85
ID: 40599034
Not using that specific method, no - and sorting in a report is much different than a Form, since any groups you have in the report will take precedence.

You could use the OpenArgs method, which would allow you to pass in the Filter and Sort info, and you could then set those in the Report's Open event, but I'm not sure that'll get you what you want.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40599062
That's the OrderBy property you could pass in OpenArgs:

DoCmd.OpenReport stDocName, acPreview,,Me.MainWorkloadQueryForm.Form.Filter,,Me.MainWorkloadQueryForm.Form.OrderBy

Then - in the OnOpen event of the report - read and decode this.
It may not be that easy, as it can be, say:

     [CustomerName] Like "International*" And [ProductID] = 45

Sorting must be done in the report, not the source of the report as this will be ignored by the report.

/gustav
0
 

Author Comment

by:peterslove53
ID: 40599063
i used the OpenArgs but it ll sort by the column specify. I just want to carry the whole sort order to the report, Since users can sort by any order.
DoCmd.OpenReport stDocName, acPreview, , Me.MainWorkloadQueryForm.Form.Filter, "sort_column"

but it ll sort by the column you specify.
The problem is a user may sort by any column and the report should sort by the appropriate column
0
 

Author Comment

by:peterslove53
ID: 40599083
Thank you Gustav, it worked.

I used this code in the onclick event:
 DoCmd.OpenReport stDocName, acPreview,,Me.MainWorkloadQueryForm.Form.Filter,,Me.MainWorkloadQueryForm.Form.OrderBy

and the in the report open event, i put this code:
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim VarSorting As String

If Not IsNull(Me.OpenArgs) Then
VarSorting = Me.OpenArgs
End If

Me.OrderBy = VarSorting
Me.OrderByOn = True 'Apply the sort order

Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open
End Sub
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40599091
Great! I had forgotten about the OrderBy property of the report. That makes it easy.

/gustav
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 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