Sorting data in forms and lists

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Published:
Updated:
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. This is the image of frm_List_Sort in the attached databaseThe 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.data structure of tbl_SortAnd will then open the sort form.form frm_SortThe 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.
The check box (Sort On) determines whether the column/field is to be included in the sort.
The "Sort How" column determines the order (ascending or descending) that the column/field will be sorted.  The default for this is ascending; to change the default, select ASC or DESC in the Sort How dropdown.
The Sort Order is not displayed to the user except via the visual placement of the fields in the continuous form.  To change the precedence of a field in the sort, Click on that fields record selector, then click on the Up/Down arrow buttons on the right side of the form until the selected fields are displayed in the order you want them to appear in your list.
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:
Copy the following objects (tbl_Sort, frm_Sort, frm_Sort_Sub, and mod_Sort) to your project.
Add a command button (cmd_Sort) to your form
Copy the code from the code block (below)
Paste it into the Click event of that button
Change the name of the object you are passing to the subroutine and the value of strSource
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

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")

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

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
4
4,482 Views
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT

Comments (12)

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Mark, this is strange.  The images in the article are the exact images I see on my screen.  Can you post a screen-shot of what you are seeing?
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
What I see in the MDB is the same as in the Article...

Good Article too :)
aikimarkSocial distance; Wear a mask; Get vaccinated
CERTIFIED EXPERT
Top Expert 2014

Commented:
I took a screenshot of the forms.  I closed the property window and minimized the database window prior to taking the screenshot.

Since this may be a limitation of the version of Access you are using, I can change these with my Access2003 version and upload the database for you to post.  Let me know what you would like to do.
Screenshot-for-fyed.jpg
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
aikmark,

I don't have access to my machine with 2003 installed at the moment, and won't until tomorrow, but I see what you mean.  Very strange that it appears one way to you and not to me or mark_willis.
aikimark,

I'm getting a compile error in the code on the following line of the SortThis() routine:
FormOrList.OrderBy = TempVars("OrderBy") 

Open in new window

I love using TempVars instead of global variables. As for your compile error, I usually use the exclamation point notation, i.e. TempVars!OrderBy. I know that there are different acceptable formats, but I have found that sometimes changing the format will get through the compiler. That may only be worth 2 cents, but maybe it's worth a try.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community