Changing sorting and grouping on the fly

[SiE]

I have generated a report that gives me the top 100 products sold in the company it also states what qty and part code plus the position in regards to the 100.

I want to be able to put a combo box on a form and use it to decide what field to sort on ie top 100 by spend or top 100 by qty sold or top 100 by alpha on the prod code. It sounds really simple but I cant seem to find out how to do it. I could generate seperate reports for each type of sort but thats really untidy and there must be a really easy way of puting dynamic sorting on to a report.

 

[Capilano]

Hi... The simplest way is have an ON OPEN Event Procedure for your Form:
Me.OrderBy = Forms![NameofYourForm]![NameOfYourComboBox]

Two Points:
Make certain that your Report's 'Order By On' property is set to YES. Second, this approach works well PROVIDED that the choices offered in your combo box match the names of your fields in the Table/Query that is providing the record source for the Report. If they are different, you might want to follow this approach and use a Select Case Statement.

Private Sub Report_Open(Cancel As Integer)

Select Case Forms![NameofYourForm]![NameOfYourComboBox]
Case "Cloice1"
Me.OrderBy = "Choice1 Asc"
Case "Choice2"
Me.OrderBy = "Choice2 Asc"
Case "Choice3"
Me.OrderBy = "Choice3 Asc"
Case Else
End Select
End Sub

This approach works best when you plan to offer your useser a 'static' number of choices.

Good Luck

Pat