How to Change sort order on the fly

  • Thread starter Thread starter Darrell Childress
  • Start date Start date
D

Darrell Childress

I have a query which shows all orders for a salesperson. I have the
query sorted by Customer name. Some of the salespeople would prefer to
have the report in order by Order Number. Is there a way that when the
report is run, the user can specify which field to sort by?
Thanks,
Darrell
 
The easiest thing to do is to have two queries for the report. Make a copy
of the existing query and in the copy sort by OrderNumner. Next create a
small popup form with an option group. Label the options "Sort By Customer
Name" and "Sort By Order Number". Finally, put the following code in the
Open event of the report:
DoCmd.OpenForm "NameOfPopUpForm",,,,,acDialog
If IsLoaded("NameOfPopUpForm") Then
If Forms!NameOfPopupForm!NameOfOptionGroup = 1 Then
Me.Recordsource = "NameOfQuerySortByCustomerName"
Else
Me.Recordsource = "NameOfQuerySortByOrderNumber"
End If
DoCmd.Close acForm, "NameOfPopUpForm"
End If

Note - You will find the IsLoaded function in a standard module in
Northwind.
 
One other thing ---

Add the following code to the AfterUpdate event of the Option Group on the
popup form:
Me.Visible = False
 
I implemented this in my testdatabase but not get it sorted?
Here is the code of the event.
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "sortpopup", , , , , acDialog
If IsLoaded("sortpopup") Then
If Forms!sortpopup!Frame0 = 1 Then
Me.RecordSource = "esc-tarea by grupo"
Else
Me.RecordSource = "esc-tarea by name"
End If
DoCmd.Close acForm, "sortpopup"
End If

End Sub

Is there something wrong in the code?

Herman
 
I understand this. I would change the sort order in the grouping on the
report instead of using 2 queries?. That's why I not understand the question
from the original post and the answer on it.

herman
 
The recommended method changes the ControlSource property of the GroupLevel.
This allows you to create the report with only one query.
 
I was not able to get it to work either. No matter which I chose, the
sort order did not change. I will look at the code at the site below.
Thanks,
Darrell
 
Back
Top