Multiple sort options for report

[TomR]

I have a report that customers would like to have the
option of sorting different ways. Is there an easy way
without having to create multiple reports with different
sort seq to give the customer a sort window for them to
choose the sort seq they want?
Tom

 

[Joe Fallon]

Here are some ideas:
=================================================
These 3 procedures show a simple way to sort a form using "raised" field
name labels as if they were buttons. Labels have a Click event.

The first procedure shows how to call the generic procedure called ReSort.

The second procedure assumes there are 3 labels on the form that can be
clicked.
It changes the color of the clicked label to blue and sets the rest to
black.
A second click of the same label reverses the sort order.

The 3rd procedure is placed in a report's open event. It simply looks at the
open form and uses the same sort order.

Private Sub lblLastName_Click()
Call ReSort(Me![lblLastName])
End Sub

Private Sub ReSort(ctl As Control)
DoCmd.Hourglass True
Me![lblLastName].ForeColor = 0
Me![lblFirstName].ForeColor = 0
Me![lblMiddleName].ForeColor = 0
ctl.ForeColor = 16737843

Me.OrderByOn = True
If Me.OrderBy = Mid$(ctl.Name, 4) Then
Me.OrderBy = Mid$(ctl.Name, 4) & " Desc"
Else
Me.OrderBy = Mid$(ctl.Name, 4)
End If

DoCmd.Hourglass False
End Sub

Private Sub Report_Open(Cancel As Integer)
Me.OrderByOn = True
Me.OrderBy = Forms![FormName].OrderBy
End Sub