Access is smarter than we are (or at least it thinks so). One of the things it does to "help" us is to completely rewrite the RowSource query for your report. I does this to remove any selected columns that are not bound to controls and it also eliminates sorting because the report interface itself provides Sorting & Grouping options which it expects you to use.
Bottom line - modify the report's Sorting & Grouping options and don't bother with the query.
No. It means you need to use the built in Sorting and Grouping feature in the report itself. This feature is actually more powerful than sorting in a form
I want my user to filter and sort the data on the form. Press a button and for it to produce a report with the same data in the same order (what ever their selection is) !!
I can get the report to filter correctly I'm struggling with sorting the data in the same way that it is on the form.
I do not want the user to have to start sorting on the report.
The user doesn't sort it on the report.
YOU do that in the report design and the order then 'sticks' for all users
For most purposes, this is far better though in your case, perhaps not.
If you have several possible sort orders selected in the form, use a different OpenArgs setting for each of these or different versions of the report if you find that easier.
Yes it CAN be done and I've already mentioned 2 ways of doing it.
You need to remember that Access will only do what you tell it to do.
It will filter the report IF you tell it to do so.
Similarly you have to tell it how to sort the report
On your form you clearly have a method of deciding how your data is sorted
e.g. combobox ; option group ; click on column headers
Say you have 5 sort order choices
1. Sort by ID
2. Sort by surname (ascending)
3. Sort by surname (descending)
4. Sort by income data (ascending)
5. Sort by income data (descending)
Apply the form sort then save that info in some way e.g. as an integer variable intSort (values 1 - 5)
The easiest (but least efficient) approach is then to have 5 versions of your report rptIncomeData1 => rptIncomeData5
In the print button code, use code like this
Code:
Private Sub cmdPrint_Click
Select Case intOrder
Case 1
DoCmd.OpenReport "rptIncomeData1", acViewPreview, , Me.Filter
Case 2
DoCmd.OpenReport "rptIncomeData2", acViewPreview, , Me.Filter
...
End Select
End Sub
That will work but is inflexible. If a new sort order is added, you would need a new report. Also if you modify the report layout, you need to do that in EACH report
A much better approach is to have ONE report but use OpenArgs based on the intSort order
Code:
Private Sub cmdPrint_Click
DoCmd.OpenReport "rptIncomeData1", acViewPreview, , Me.Filter,,,intSort
End Sub
Then in your Report_Open event code you need to specify the SQL for each choice
Code:
Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String
Select Case Me.OpenArgs
Case 1
strSQL = enter full SQL for case 1 here including ORDER BY code
Case 2
strSQL = enter full SQL for case 2 here including ORDER BY code
.... etc
End Select
Me.RecordSource = strSQL
End Sub
NOTE: with the latter approach you can either have a default record source for when the report is opened without using OpenArgs OR leave the record source blank & do it all using code
OK. I figured out the trick. When you define the report - do NOT specify any sorting. You can specify grouping though. I took a simple list of members. Grouped by audit. Then I changed the members to sort by memberID or lastname.
In the FORM:
Code:
Private Sub cmdRPT_Click()
Dim vSort As Variant
vSort = Me.OrderBy
DoCmd.OpenReport "rptTESTsorting_Members", acViewPreview, , , , vSort
End Sub
In the REPORT:
Code:
Private Sub Report_Load()
If IsNull(Me.OpenArgs) Then
Else
Me.OrderBy = Me.OpenArgs
Me.OrderByOn = True
End If
End Sub
My approach using open args in post 10 isn't quite as concise as Pat's.
However, it definitely does work for both sorting and filtering. Did you try that?
There is just one issue.
When I close and reopen the database. If I do not select any filters or sorting and press open report then the report reverts to the last sort.
Is there away to "un-sort" the report on close so that it is fresh.
It's as though it doesn't recognise any filters or sorts if you don't do any ?
After opening your report, change it to design view and look at the property sheet. If you have filter and order by entries you will need to clear them using code before the report is closed.
Or add code to set Me.Filter="" unless a choice is made
Alternatively, use my version which doesn't have that issue.
The filtering & ordering is done explicitly for each choice made.
If no filter chosen explicitly, the report opens unfiltered
