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
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
There is no save option once the user has viewed the report. They either print/save to file or close.
I'm a little unsure how to implement the where argument in regard to opening the report.
My current get around is to have two buttons:
If no filters or sorts are selected then:
Button1 opens a report1 with pre defined sort criteria that mimics the underlying query of the form.
Button opens report2 (your solution).
If only I could get your solution to print in the same way as my button1 (when nothing is filtered).
I tried to default report2 with sorting but this overides your solution
I also embarked on creating an If statement that was based on a sub form counting the records. If the number of records is the same as the number in the form then report1 else report2 but this still doesnt address the problem if the user simply resorts all of the data.
Sorting report records at runtime
Access reports do their own sorting based on the sort fields you specify in the Sorting and Grouping dialog of the report. The recordsource Order By clause is ignored.
Microsoft has a knowledgebase article that explains a technique for using setting the OrderBy property of a report by opening the report in design view (Article ID: Q146310).
I have always preferred to programmatically set the group levels of the report, with code like this in the Open event of the report:
Select Case Forms!frmChooseSort!grpSort
Case 1 'Name
Me.GroupLevel(0).ControlSource = "LastName"
Me.GroupLevel(1).ControlSource = "FirstName"
Me.GroupLevel(2).ControlSource = "Company"
Case 2 'Company
Me.GroupLevel(0).ControlSource = "Company"
Me.GroupLevel(1).ControlSource = "LastName"
Me.GroupLevel(2).ControlSource = "FirstName"
End Select
To make this work, you just need to make sure that you have set up the right number of grouping levels in the report's grouping and sorting dialog.
Attached is an example showing how to setup sort & filter options for a report from a form.
There are 3 sort options & 5 filter options in this example so 15 possible outcomes from one report
Its hopefully fairly simple both to understand & then transfer the ideas to your own database.
There is no limit to the number of options you can do by this method.
For example, I have another report based on a crosstab query with 6*7= 42 possible sort & filter options
I had already done this before seeing your last post so haven't referred to that
