Viewer Question: Sort a Continuous Form by Clicking on the Column Label of a Combo Box

Over on my YouTube channel, a viewer asked the following question about my NewSort() function:

Hi, this is great.  I was wondering how this would work with a combo box.  It just references the id number, but I'd like to sort by the displayed text.
-@kathymatossian

Great question, Kathy!

Here's an excerpt from my original NewSort article for reader context:

Usage

There are usage notes in the code comments, but here is the high-level overview:

1. Create Label controls in the Form Header section to serve as column headers
2. Set the On Click event for each label to =NewSort([Form], "MyColumnName")
3. [OPTIONAL] Set the On Mouse Move event to =UseHand()
4. [OPTIONAL] Set the form's On Load event to =NewSort([Form], "MyFirstColNameToSortBy", "MySecondColNameToSortBy") (passing multiple field names resets the form's Order By property)

A Simple Example

Let's create a new form based on a Person table with the following structure:

The PositionID field is a foreign key to the Position table, shown below:

To begin, we'll create a very simple continuous form bound directly to the Person table (i.e., the form's RecordSource property is Person):

I've included three controls on this simple form:

1. Text box bound to FirstName
2. Text box bound to LastName
3. Combo box bound to PositionID

I added calls to the NewSort() function in the On Click event for the two text box labels as follows:

• First Name label On Click: =NewSort([Form], "FirstName")
• Last Name label On Click: =NewSort([Form], "LastName")

Clicking on either label will sort the records in ascending order for the respective field.  Clicking a second time will switch the order to descending.  See the original article and/or YouTube video linked above for more details.

So far, so good.  

Combo Box Challenges

We're left now with the Position combo box.

Here's the RowSource for the combo box:

SELECT PositionID, PositionAbbrev, PositionName 
FROM [Position] 
ORDER BY Seq; 

This is a typical example of an Access combo box where the control is bound to a Long Integer foreign key field.  The combo box itself is populated with data from the foreign lookup table.  The bound column is hidden from the user by setting the first column width to 0".  The last visible column is set to 0.1" to prevent Access from displaying a horizontal scroll bar if I don't set the List Width property wide enough.

Since the PositionID is a surrogate key, it is meaningless to the user.  It makes no sense to sort by this field.  However, if we look at the current field list (via Form Design > Add Existing Fields) we see that PositionID is the only field related to the Position table in our form's current record source:

As Kathy alluded to in her comment, there's no way to get at the description fields from the combo box.  

So, how do we allow the user to sort the form by the PositionName field?

Sorting by Combo Box Descriptions

The short answer is that we need to include any columns we want to sort by in the form's RecordSource.

In other words, we need to change the form's RecordSource from Person to:

SELECT Pe.PersonID, 
       Pe.FirstName, 
       Pe.LastName, 
       Pe.PositionID, 
       Po.PositionAbbrev
FROM Person AS Pe 
  LEFT JOIN [Position] AS Po ON Pe.PositionID = Po.PositionID;

Note that we use a Left Outer Join to show all the Person records even if they do not yet have a PositionID value.

Now that PositionAbbrev is one of the fields in the form's RecordSource, we simply set the Position Label's On Click event to:

=NewSort([Form], "PositionAbbrev")

The screenshot below shows the form when first loaded (Left), after clicking the Position label once (Middle), and after clicking it a second time (Right).

Sorting Memory

The screenshots above also demonstrate the sort "memory" feature of the NewSort() function.  

Rather than replacing the existing sort, the NewSort function simply "demotes" the existing sort and prioritizes sorting by the most recently clicked field.  When initially loaded, the form above sorts by First Name.  If you look carefully in the two screenshots to the right, you will see that within position groups, the players continue to be sorted in First Name ascending order.

Referenced Articles/Videos