Yes you can! sort Access form by combo column Ms Access Gurus

Support this site by donating to help with costs, thank you.

Sort an Access Form by columns of combobox NOT in the Record Source!

If you think that it isn't possible to sort a form in Access by a field or calculated fields that aren't in the form RecordSource, then read on. It IS possible ... you can sort by anything you see in a combo box (and, presumeably, also a listbox).

Here's the trick: preface the reference with "Lookup_" ... and it works!

Preface control name with LOOKUP_ to reference a column name

Quick Jump

Sort by Anything on a Form

Sort form by fields not in the record source

Goto Top  


Video

Watch on YouTube: Sort Access Form by Combobox Column (19:21)

Goto Top  

Design View of Form

Here is the design view of the form. As you can see, combo boxes are used for foreign keys. The only fields in the record source come from the c_ContactCategory cross-reference table.

combo boxes used for foreign keys in design view of the form

Here is the field list for the form.

Field List for the Contact Category cross-reference table

Relationships

Here is the Relationships Diagram for the sample database.

Contact and Category and cross-reference table Relationships Diagram

Goto Top  


Download ACCDB

Download Form_SortComboColumn__ACCDB_strive4peace.zip (18Kb)

Goto Top  


VBA behind the form

Different techniques are used in the examples, so the code isn't consistent, intentionally.

Option Compare Database 
Option Explicit 

'*************** Code Start *****************************************************
' code behind form: f_Contact_CATEGORY
'-------------------------------------------------------------------------------
' Purpose  : Sort form by a column of a combo box
'            that's not in the RecordSource
'            and specify multiple sorts
' Author   : crystal (strive4peace)
' Code List: www.msaccessgurus.com/code.htm
' This Code: https://msaccessgurus.com/VBA/Form_SortByComboColumn.htm
' Contact databases: https://msaccessgurus.com/tools.htm#Contacts
'-------------------------------------------------------------------------------
' LICENSE
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Use at your own risk.
'-------------------------------------------------------------------------------
 
'-------------------------------------------------------------------------------
'           cmd_SortContact_Click
'-------------------------------------------------------------------------------
Private Sub cmd_SortContact_Click() 
'strive4peace
'  this is the macro way:
'   Me.ContactID.SetFocus
'   DoCmd.RunCommand acCmdSortAscending
   With Me 
      'Sort Main Name, Order
      .OrderBy =  "[Lookup_ContactID].[LastFirst], OrdrCC"
      .OrderByOn = True 
   End With 
End Sub 

'-------------------------------------------------------------------------------
'           cmd_SortContact2_Click
'-------------------------------------------------------------------------------
Private Sub cmd_SortContact2_Click() 
'strive4peace
'  sort by any column of a combobox
   With Me 
      .OrderBy =  "[Lookup_ContactID].[FirstLast], OrdrCC"
      .OrderByOn = True 
   End With 
End Sub 

'-------------------------------------------------------------------------------
'           cmd_Category_Click
'-------------------------------------------------------------------------------
Private Sub cmd_Category_Click() 
'strive4peace
   Dim sOrderBy As String 
   With Me 
      sOrderBy =  "[Lookup_CategoryID].[Category]"
      'see if the sort will be Descending
      If Left(.OrderBy,Len(sOrderBy) + 1) _ 
         = sOrderBy &  "," Then 
         sOrderBy = sOrderBy &  " DESC"
      End If 
      sOrderBy = sOrderBy &  ", "
   
      'Sort Category and whatever is currently the name sort
      If InStr(.OrderBy &  "", "FirstLast") > 0 Then 
         sOrderBy = sOrderBy &  "[Lookup_ContactID].[FirstLast]"
      Else 
         sOrderBy = sOrderBy &  "[Lookup_ContactID].[LastFirst]"
      End If 

      .OrderBy = sOrderBy 
      .OrderByOn = True 
   End With 
End Sub 

'-------------------------------------------------------------------------------
'           cmd_SortCategoryCount_Click
'-------------------------------------------------------------------------------
Private Sub cmd_SortCategoryCount_Click() 
'220719 strive4peace
   Dim sOrderBy As String 
   With Me 
      sOrderBy =  "[Lookup_CategoryID].[#Contacts]"
      'see if the sort will be Descending
      If Left(.OrderBy,Len(sOrderBy) + 1) _ 
         = sOrderBy &  "," Then 
         sOrderBy = sOrderBy &  " DESC"
      End If 
   
      'next is category
      sOrderBy = sOrderBy &  ", [Lookup_CategoryID].[Category], "
      
      'then by whatever is currently the name sort
      If InStr(.OrderBy &  "", "FirstLast") > 0 Then 
         sOrderBy = sOrderBy &  "[Lookup_ContactID].[FirstLast]"
      Else  'default
         sOrderBy = sOrderBy &  "[Lookup_ContactID].[LastFirst]"
      End If 

      .OrderBy = sOrderBy 
      .OrderByOn = True 
   End With 
End Sub 


'-------------------------------------------------------------------------------
'           cmd_Ordr_Click
'-------------------------------------------------------------------------------
Private Sub cmd_Ordr_Click() 
   'Sort Order of importance and then Category -- Ascending or Descending
   With Me 
      If Left(.OrderBy &  "",Len( "OrdrCC,")) =  "OrdrCC," Then 
         .OrderBy =  "OrdrCC DESC, [Lookup_CategoryID].[Category]"
      Else 
         .OrderBy =  "OrdrCC, [Lookup_CategoryID].[Category]"
      End If 
      .OrderByOn = True 
   End With 
End Sub 


'*************** Code End *******************************************************
' Code was generated with colors using the free Color Code add-in for Access.

Goto Top  

Reference

Form.OrderBy property (Access)

Help: Form.OrderBy property (Access)

Form.OrderByOn property (Access)

Help: Form.OrderByOn property (Access)

Goto Top  

Backstory

I used to know this, and a recent forum question reminded me ... how incredibly useful this is! No need to add more tables to the record source. Amazing, but it seems that you can sort by anything you can display in the column of a combo box ... and even name it poorly.

How many times have you wanted to sort a form by a field not in it's record source? or a calculated field? Now you know :)

If you like this page, please help with costs , thank you.

Share with others

here's the link for this page in case you want to copy it:
https://msaccessgurus.com/VBA/Form_SortByComboColumn.htm

Get Help with Access

Let's connect and team-develop your application together. I teach you how to do it yourself. As needed, while we build something great together, I'll pull in code and features from my vast libraries, cutting out lots of development time.

Do you want to take your forms to greater heights? I'd be happy to help you. For training and programming, email me at training@msAccessGurus.com

Donations are always appreciated (smile).

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top