Change Sort Order of Combo Box "On-The-Fly"

[JoeM]

I have a Combo Box used on one of my Forms for record selection. It shows four fields, though only the first one is bound:
1. Company Code
2. Plan Code
3. Plan Name 1
4. Plan Name 2

It is currently set to sort by the Company Code field, which is the bound field.

Over the years, some companies have changed their plan names, and now our users would like the ability to look up by the Plan Name 1 field.
So what I would like to do is somehow give them the ability to toggle how the Combo Box entries are sorted (either by the first or third fields).

How can I do that?

Thanks

[Bob Fitz]

You could use an option group control or another combo box for the user to select the sort order. In it's after Update event put some code to determine the selection and then change the Row source property of the combo box.

[JoeM]

Thanks Bob! That got me where I need to go! I added another combo box for them to choose which sort option, and then added the code to the AfterUpdate event, like you recommended.

In case this helps anyone in the future, I am posting my code below.

Private Sub cboOrder_AfterUpdate()
'   Change order of plan selection combo box
    
    Dim mySQL As String
    
'   Build beginning of SQL code for plan selection combo box
    mySQL = "SELECT strClientCode, strEngageNum, strPlanName1, strPlanName2 FROM [tbl Plan Info]"
    
'   Determine how to sort and update SQL code
    Select Case cboOrder.Value
        Case "Company Code"
            mySQL = mySQL & " ORDER BY 1, 2;"
        Case "Plan Name 1"
            mySQL = mySQL & " ORDER BY 3, 4;"
        Case Else
            mySQL = mySQL & " ORDER BY 1, 2;"
    End Select
        
'   Apply SQL code to plan selection combo box
    Me.cboPlan.RowSource = mySQL
            
End Sub

[Bob Fitz]

Glad to help. Can you mark it as solved.

[JoeM]

Can you mark it as solved.

I already marked it as solved over an hour ago!

[Bob Fitz]

I already marked it as solved over an hour ago!

My apologies.

[JoeM]

No worries! I appreciate the help!