Ordenar formulario de acceso por columna combinada NO en la fuente del registro

VBA detrás del formulario

En los ejemplos se utilizan diferentes técnicas, por lo que el código no es coherente de forma intencionada.

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: 
' Contact databases: 
'-------------------------------------------------------------------------------
' 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 *******************************************************