Sorting a Report

[AndrewWolford]

Here's a fairly general question:

Does anyone know of a way (if possible) to change how a report is sorted once it has been ran? In other words, I want to be able to change what fields the report is sorted on dynamically. For example if I had a report that had DATE and CUSTOMER ID as field headings. Is it possible to switch between the report being sorted on DATE and then being sorted on CUSTOMER ID without having to create 2 seperate reports that are identical except for how they are sorted...I'm envisioning the report working similarly to how you can change how files are listed in Windows Explorer. By clicking on the heading you can see the files listed by type, size, name, etc... is this possible to do in an access report? Thanks for any guidance!

 

[evalesthy]

Yes it can be done. The following function is the heart of the code:

Private Sub SortOpenWorkOrderReport()
On Error Resume Next
Dim strOrderBy As String

'NOTE in report you are opening the report property 'Order On By' property_
'must be set to yes and there must be no design level sorts either in query_
'or in Sorting_and_Grouping of report as they will override this code.
With Reports("Open Work Orders&quot
If Me.FrameSortBy.Value = 1 Then
strOrderBy = "ZipCode"
ElseIf Me.FrameSortBy.Value = 2 Then 'if Option Group 2 is selected
strOrderBy = "Name" 'make report OrderBy property = ZipCode
ElseIf Me.FrameSortBy.Value = 3 Then 'if Option Group 3 is selected
strOrderBy = "Account" 'make report OrderBy property = AccountNumber
Else
strOrderBy = "Account"
End If
.OrderBy = strOrderBy
.OrderOn = True
End With

End Sub

Then when you open the report (DoCmd.openreport) etc. just call this function - you will see the report resort itself (which can be a little disconcerting to user by the way).
If you need more information let me know.