databasedev.co.uk - database solutions and downloads for microsoft access

Sorting Columns in a Microsoft Access Listbox

When using a Microsoft Access Listbox, there may be times when the list is long and the data in this list is not sorted as you would like.

Obviously the list can be sorted in the first case, by basing the list on a query or SQL row source and setting a sort order on one of the columns in Ascending or Descending order. This list may, however, contain multiple columns that the user may wish to sort on and this is what we will describe in this article.

The sort options are set up by placing command buttons above each column. The example below shows the command buttons (Order By:) in place - there are actually six buttons included on the form, each containing a sort order of either Ascending (ASC) or Descending (DESC) and are hidden on top of each other.

Image showing the form containing the Order By buttons above each column in the listbox

The code behind each button at the top of the listbox recreates the SQL of the RowSource, setting the ORDER BY clause to match the button. It makes the button underneath it visible. It also changes the Caption of the buttons to reflect which direction the sort is happening. Then the listbox is requeried.

The sort function has been removed to a separate function common to all columns and is called by each of the buttons. There is also a function which clears all the captions of the arrow in preparation for setting the arrows on the appropriate button.

The code below shows the SQL that is generated by clicking on any of the command buttons:

'--------------- Code Start ---------------


Private Function basOrderby(col As String, xorder As String) As Integer
    Dim strSQL As String

    'Clear captions from command buttons
    ClearCaptions

    'Set row source for list box
    strSQL = "SELECT DISTINCTROW strSalespersonID, " & _
    "strSalespersonFirstName,strSalespersonLastName,strRegion "
    strSQL = strSQL & "FROM tblSalespersonContact "
    strSQL = strSQL & "ORDER BY " & col & " " & xorder
    Me!lstSearch.RowSource = strSQL
    Me!lstSearch.Requery

End Function

'---------------- Code End ----------------

When a column heading is clicked upon, the captions of the command button also change as shown below:

Clicking on the column heading orders the listbox column

To check out the Microsoft Access 2000 database example, and view the code contained behind each command button please download this from either the Microsoft Access Forms page or the Microsoft Access Downloads page.