Sorting data in forms and lists

Tested in Access 2007 and 2010.  Untested, but should work in 2003 and 2013.

Background:
I recently had a client ask if there was a way they could sort the columns of a listbox.  In this particular application, we had developed a number of forms which allowed them to view issues (bad or missing data) within various tables in their database.  These issues arose because of their desire to enter some basic information about each of their oil/gas wells, and fill the rest in later.  What they found, however, was that it was difficult to keep track of which wells were missing information, so we developed a series of forms that allowed them to view only those wells with a specific issue and then quickly navigate to the offending records.  During this process, they also discovered that different users process information differently, so they asked me if each user could define their sort orders at run time.

Sorting a list:
Most developers have had an application where users needed to be able to sort by a particular column in a listbox.  To do this with a single column, they generally add some code to the Click event of one or more column headers.  This is a relatively simple process of modifying the RowSource property of the listbox by adding an Order By clause to the SQL string.  

In this instance I had multiple forms with list boxes containing multiple columns.  I felt I needed to create a more universal solution than the one mentioned above.  This article describes how to implement my solution and allow users of your application to sort data in a list box by multiple fields, define the precedence of the selected fields, and then identify the direction, ascending or descending, for each of the selected fields.

The sample database (end of article) includes several forms which display four columns of data from a small table; the image below shows the startup form 'frm_Sort_List' and displays the data in a list box. The first step in any sort process is to identify which columns(s) to sort by.  To initiate this process, Click the cmd_Sort command button in the upper right corner of the form.  This will load the field names (and captions if they exists) from the lists RowSource into tbl_Sort.And will then open the sort form.The sort form displays all of the fields from the RowSource of the list.  Each record in the sort form has three key properties (Sort On, Sort How, and Sort Order) which must be assigned by the user.
Once satisfied with the columns, order, and precedence of the sort fields, click the "Sort" button to implement the sort or Cancel to cancel the process.


Implementing this process in your forms
In order to use this functionality in your projects, you simply need to:

Private Sub cmd_Sort_Click()
                      
                          Static strSource As String
                          
                          If strSource = "" Then strSource = Me.lst_Numbers.RowSource
                          Call SortThis(Me.lst_Numbers, strSource)
                          
                      End Sub

Select all Open in new window

This code stores the original RowSource of the listbox, prior to the first call of the subroutine, in a static variable, which will retain its value after the first call.  This will allow users to sort the list over and over again based on the original RowSource of the list.  

A more limited sort:
If you don't want your users to be able to sort by all of the fields in the lists RowSource, there is a solution for that as well.   You may have included hidden fields, in your list, which you don't want your users to be able to include in the sort process.  If that is the case, you can pass the SortThis( ) subroutine an optional parameter array which contains the names of the fields you want to allow the user to see, and an associated caption (what they will see displayed in the Field Name column of frm_Sort).  The syntax for this is:

Call SortThis(Me.lst_Numbers, strSource, _
                             "DateField", "Date field caption", "TestText", "Text Field Caption")

Select all Open in new window

If you enter field names that are not valid, you will receive an error message.

Sorting form recordsets
You may also extend this functionality to your forms and subforms as well.  Simply change the code behind the Sort button on the form so that it passes the form (or subform) as the object, and the forms RecordSource instead of the lists RowSource as the Source string.

Private Sub cmd_Sort_Click()
                      
                          Static strSource As String
                          
                          If strSource = "" Then strSource = Me.RecordSource
                          'for subforms
                          'if strSource = "" then strSource = Me.subFormControl.Form.RecordSource
                      
                          Call SortThis(Me, strSource)
                          
                      End Sub

Select all Open in new window

How it works
The process and code is really quite simple.  Identify the list or form to be sorted, display the list of fields for the user to select, and modify the RowSource of the list or the OrderBy and OrderByOn properties of a form.  The code is thoroughly documented so if you need to figure out what is going on behind the scenes, just review the code.

Sample database
The attached database is configured to open to frm_List_Sort when you open the application.  You can switch to the Form version of the sort (and back) by clicking the button in the upper left corner of either form.  Both of these forms contain two Sort buttons; the one on the left allows you to sort on all of the columns in the record/row source; the one on the right allows you to sort on a limited set of the fields.
Sort.mdb