Dynamically sort combo box data

[djayam]

Hello again all,

I have a combo box Combo_PreviousOrders that gets its data from a query:

SELECT qry_FirstOrderPartDescr.OrderID, qry_FirstOrderPartDescr.PONumber, qry_FirstOrderPartDescr.OrderType, qry_FirstOrderPartDescr.OrderDate, qry_FirstOrderPartDescr.Supplier, qry_FirstOrderPartDescr.Customer, qry_FirstOrderPartDescr.FirstOrderPart FROM qry_FirstOrderPartDescr ORDER BY [OrderID] DESC

I have an option group on the same form that I would like to allow the user to select how to sort the combo box.

What do I need to enter in the query to get this to happen? I tried:

SELECT qry_FirstOrderPartDescr.OrderID, qry_FirstOrderPartDescr.PONumber, qry_FirstOrderPartDescr.OrderType, qry_FirstOrderPartDescr.OrderDate, qry_FirstOrderPartDescr.Supplier, qry_FirstOrderPartDescr.Customer, qry_FirstOrderPartDescr.FirstOrderPart FROM qry_FirstOrderPartDescr ORDER BY IIF(Option_SortOrders=1,"qry_FirstOrderPartDescr.Supplier","qry_FirstOrderPartDescr.Customer") ASC, [OrderID] DESC

but it doesn't work.

Any ideas?

Thanks,

Jason

 

[djayam]

It's alright - I sorted it!

In the On Change event of the option group:

Dim vSort As String
    If Me.Option_SortOrders = 1 Then vSort = "qry_FirstOrderPartDescr.Supplier"
    If Me.Option_SortOrders = 2 Then vSort = "qry_FirstOrderPartDescr.Customer"
    
    Me.Combo_PreviousOrders.RowSource = "SELECT qry_FirstOrderPartDescr.OrderID, qry_FirstOrderPartDescr.PONumber, qry_FirstOrderPartDescr.OrderType, qry_FirstOrderPartDescr.OrderDate, qry_FirstOrderPartDescr.Supplier, qry_FirstOrderPartDescr.Customer, qry_FirstOrderPartDescr.FirstOrderPart FROM qry_FirstOrderPartDescr ORDER BY " & vSort & " ASC, [OrderID] DESC"

Cheers,

Jason

 

[TheAceMan1]

djayam . . .

Have a look at the [blue]OrderBy[/blue] & [blue]OrderByOn[/blue] properties of a form!

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[djayam]

That won't effect the sort order of the Combo Box surely.

 

[TheAceMan1]

Woops . . .

Sorry about that . . . wrong thread! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884: