<

Sorting data in forms and lists

Published on
11,092 Points
3,692 Views
4 Endorsements
Last Modified:
Approved
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
Comment
Author:Dale Fye
12 Comments
LVL 47

Expert Comment

by:aikimark
@fyed

In the final iteration example, where you have column (header) labels and associated data field names, I don't see that reflected in the example you showed for the date field.  It looks like the date field text values are switched.

Also, I would expect to see data fields delimited with "[" and "]".

The trick with hidden fields is that sometimes we want to be able to sort or filter by that hidden column and not the visible column.
0
LVL 50

Author Comment

by:Dale Fye
Thanks, Mark.  I'll take those recommendations and tighten it up a bit.

1. "I don't see that reflected..."
Ans: actually, the code will accept it either way  (Field, Caption, Field, Caption) or (Caption, Field, Caption, Field).  It searches to determine which (1st or 2nd element of the first pair matches a field name in the recordset, and usese the appropriate element of the pair).  Probably need to tighten that up so that it only accepts in a one of those two methods, and checks to make sure that all of the elements are present in the recordset.

2.  "I would expect to see data fields delimited with "[" and "]"
Ans: Great point, the Order By clause generated in the code includes those, but I could also tighten that up so that if the user wrapped the field in brackets, it would replace "[[" with "[" and "]]" with "]".  

3.  "The trick with hidden fields ..."
Ans:  Up to the developer and the client.  You can set it up to do either way.  For example, I really don't want to present my autonumber PK (which is usually the first and hidden element of my lists) to the user.
0
LVL 47

Expert Comment

by: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


======
You might also consider alternative parameter configurations:
* two delimited string list (name and header text) parameters
* each parameter is a two-item delimited string
* use the field number instead of its name for the Order By clause reference

======
When you display the information to the user, you can put both the header and field name in one string.
Examples:
"Date field (DateField)"
"DateField"
"Start Date [DateField]"
"End Date - [DateField]"
Note: In the second example, you might not need to display both data if they are identical values.

======
While this works for table/query-based row sources, what do you do for value-based row sources?

Are you going to extend this to listview controls?

======
Are you creating user controls?
Do you add a 'sort' button for each listbox/combobox control on your form or reuse the command button?

======
Have you considered facilitating the user's clicking of a column header to cause the sort action?
I did a test, using the MouseUp() event for your listbox and can determine which column is being clicked and whether I am clicking in the header row or not.
Private Sub lst_Numbers_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Dim strParsed() As String
    Dim lngWidths() As Long
    Dim lngLoop As Long, lngCumulativeWidth As Long
    
    If Y > 285 Then Exit Sub
    
    strParsed = Split(lst_Numbers.ColumnWidths, ";")
    ReDim lngWidths(0 To UBound(strParsed))
    
    For lngLoop = 0 To UBound(lngWidths)
        lngWidths(lngLoop) = CLng(strParsed(lngLoop))
        Select Case X
            Case lngCumulativeWidth To (lngCumulativeWidth + lngWidths(lngLoop))
                Debug.Print "User clicked on Column: " & lngLoop
                Exit For
            Case Else
                lngCumulativeWidth = (lngCumulativeWidth + lngWidths(lngLoop))
        End Select
    Next
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LVL 50

Author Comment

by:Dale Fye
Have reorganized the article some, and made some modifications to the code to make it backwards compatible to 2003 (removed use of TempVars).

Mark,  I wanted to keep this relatively simple, so I did not extend it to include some of the ideas you proposed.  Thanks for the feedback.
0
LVL 47

Expert Comment

by:aikimark
Feedback:
* Your code complies cleanly

* The background color for your forms is black and does not offer sufficient contrast to read the text on the controls.

* When I opened the database, the properties window is open

* The text on the command buttons on the start-up form is truncated

* The text on the command buttons on the start-up form do not indicate their function.  The text should be different.  If you are short on space, you can use tool tip text to explain their similar functions.
0
LVL 50

Author Comment

by:Dale Fye
Thanks Mark, I modified the background color, the did not show up as black on my screen.  But now it is back in for review...
0
LVL 47

Expert Comment

by:aikimark
the black background color is on ALL your forms, except the one you (partially) fixed.  On the 'fixed' form, the form footer is still black, but that doesn't affect readability.

The properties window is still open.
0
LVL 50

Author Comment

by:Dale Fye
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?
0
LVL 54

Expert Comment

by:Mark Wills
What I see in the MDB is the same as in the Article...

Good Article too :)
0
LVL 47

Expert Comment

by:aikimark
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
0
LVL 50

Author Comment

by:Dale Fye
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.
0
LVL 1

Expert Comment

by:MarvinM80
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.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month