By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,035 Members | 1,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,035 IT Pros & Developers. It's quick & easy.

Sorting Multi-Column List Box - Access 2003

P: 11
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
Aug 26 '08 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,628
Assuming your Table Name is Employees, and your Command Button Names are cmdFirstName, cmdLastName, and cmdAge:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFirstName_Click()
  2. Me![List10].RowSource = "Select Employees.FirstName, Employees.LastName, Employees.Age " & _
  3.                         "FROM Employees Order by Employees.FirstName;"
  4. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLastName_Click()
  2. Me![List10].RowSource = "Select Employees.FirstName, Employees.LastName, Employees.Age " & _
  3.                         "FROM Employees Order by Employees.LastName;"
  4. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAge_Click()
  2. Me![List10].RowSource = "Select Employees.FirstName, Employees.LastName, Employees.Age " & _
  3.                         "FROM Employees Order by Employees.Age;"
  4. End Sub
Aug 27 '08 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Aug 27 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.