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

Quick Jump

Sort by Anything on a Form

Video

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

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.

Here is the field list for the form.

Relationships

Here is the Relationships Diagram for the sample database.

Download ACCDB

Download Form_SortComboColumn__ACCDB_strive4peace.zip (18Kb)

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 *******************************************************

Reference

Form.OrderBy property (Access)

Help: Form.OrderBy property (Access)

Form.OrderByOn property (Access)

Help: Form.OrderByOn property (Access)

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