Sorting Multi-Column List Box - Access 2003

[KrazyKasper]

I have a Multi-Column List Box named List10 that is populated via a query (qry_OptimizeIt3). I’m looking to create three Buttons, one above each column that will re-sort the list in ascending order based on the specific column. For example:

FIRSTNAME LASTNAME AGE
John Doe 24
Bill Jones 43
Judy Smith 21
Sally Johnson 38

I thought I could have each button change the RowSource order of the List Box using simple VBA code; something like:

Private Sub SortOrder1_Click()
List10.RowSource = CurrentDb.QueryDefs(qry_OptimizeIt3a).SQL & " ORDER BY " & strFIRSTNAME
End Sub

However, this gives me the error message “Item not found in this collection” and points to the line List10.RowSource = CurrentDb.QueryDefs(qry_OptimizeIt3a).SQL & " ORDER BY " & strFIRSTNAME

I appreciate any input you can provide.


Krazy (Bill) Kasper

[ADezii]

Assuming your Table Name is Employees, and your Command Button Names are cmdFirstName, cmdLastName, and cmdAge:

Private Sub cmdFirstName_Click()
Me![List10].RowSource = "Select Employees.FirstName, Employees.LastName, Employees.Age " & _
                        "FROM Employees Order by Employees.FirstName;"
End Sub

Private Sub cmdLastName_Click()
Me![List10].RowSource = "Select Employees.FirstName, Employees.LastName, Employees.Age " & _
                        "FROM Employees Order by Employees.LastName;"
End Sub

Private Sub cmdAge_Click()
Me![List10].RowSource = "Select Employees.FirstName, Employees.LastName, Employees.Age " & _
                        "FROM Employees Order by Employees.Age;"
End Sub

[nico5038]

An easy alternative might be to use a datasheet subform as that can be sorted with the right-click options and the A>Z and Z>A buttons.
Moreover the user can also filter such a datasheet with the right click and re-arrange the columns to sort on multiple fields....

Nic;o)