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?
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
All Courses

From novice to tech pro — start learning today.