Sorting listboxes at runtime

I recently changed oversubforms to listboxes in a database because I needed to have a multi-select ability. Unfortunately the listbox control does not include a method for sorting on different columns at runtime as the subforms did.

I'm thinking of having a button for each column that would create the "ORDER BY" clause of the SQL statement that refreshed the rowsource property.

I figured before I tried to re-invent the wheel I would see if there were any methods to achieve this result.
LVL 1
wipnavAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I've done this with Labels, and running code on the Click or DoubleClick event of the labels. Command buttons would work as well.
0
 
McOzCommented:
Just add the ORDER BY to the rowsource property of each listbox field

This has worked for me - good luck!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There's no builtin functionality for that. You'll have to buildup your ORDER BY clause and reset the RowSource of your Listbox.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
wipnavAuthor Commented:
LSM, that is what I was thinking, but I would need to add a command button for each sortable column though?
0
 
Rey Obrero (Capricorn1)Commented:
<I recently changed oversubforms to listboxes in a database because I needed to have a multi-select ability. >

you could have added a checkbox field for use in multi selection of items, and still have the sorting capabilities.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can also use an Option Group to make the selection, using the AfterUpdate event of the option group to determine with 'sorting' option was selected.

Private Sub frame1_AfterUpdate()
    Select Case 1
         Me.YourListBoxName.RowSource = "SELECT Table1.* FROM Table1 ORDER BY Table1.FIELD1"
    Select Case 2
         Me.YourListBoxName.RowSource = "SELECT Table1.* FROM Table1 ORDER BY Table1.FIELD2"
    Select Case 3
         Me.YourListBoxName.RowSource = "SELECT Table1.* FROM Table1 ORDER BY Table1.FIELD3"
 ' and so on
   End Select

End Sub

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
minor correction: (not enough sleep last night)

Private Sub Frame1_AfterUpdate()
    Select Case Me.Frame1
       Case 1
         Me.YourListBoxName.RowSource = "SELECT Table1.* FROM Table1 ORDER BY Table1.FIELD1"
       Case 2
         Me.YourListBoxName.RowSource = "SELECT Table1.* FROM Table1 ORDER BY Table1.FIELD2"
       Case 3
         Me.YourListBoxName.RowSource = "SELECT Table1.* FROM Table1 ORDER BY Table1.FIELD3"
 ' and so on for the number of columns ...
   End Select

End Sub

mx
0
 
wipnavAuthor Commented:
Thanks LSM, out of all the solutions, this one seems to work the best. A sample that I used was from this tutorial: http://www.comboprojects.com/articles/accSortingList/accSortingListbox.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.