sort using cbo box filter on a form

[mlrucci]

Ok guys. I have been racking my brain. I have a continuous form. This has over 5000 records. I my recordsource is a query from one table. I am using the query as I need the ability to add sort, filer and search. I have a unbound field [cboSort] that I have rowsource as "Ascending";"Descending" I have an unbound combo box with all the different fields within the continuous form. I would like to have the end user to select the field they want and sort ascending and descending. Pretty basic and not problem...unless the field they are sorting a field that is a combo box (txt works great). When I sort the fields that are combo boxes, they are using the primary key for the sort (column (0)). I would like to sort based on column(1) from the rowsource of the combo box. Below is the basic code of what I am trying to do. There are 2 combo boxes within my form. LM Platform and Servicing Platform. Those are the two I am struggling with.

Private Sub cboSort_AfterUpdate()
     If IsNull([cboField]) Then
          MsgBox "Please select a field to sort"
     Else
          If [cboSort] = "Ascending" And [cboField] = "Servicing Platform" Then
               DoCmd.GoToControl [cboField]
               'Me.OrderBy = "[Servicing Platform].column(1) ASC"
               Me.OrderBy = "[Servicing Platform] ASC"
          Else
               If [cboSort] = "Descending" And [cboField] = "Servicing Platform" Then
                    DoCmd.GoToControl [cboField]
                    Me.OrderBy = "[Servicing Platform] DESC"
               Else
                    If [cboSort] = "Ascending" And [cboField] = "LM Platform" Then
                         DoCmd.GoToControl [cboField]
                         Me.OrderBy = "[LM Platform] ASC"
                    Else
                         If [cboSort] = "Descending" And [cboField] = "LM Platform" Then
                              DoCmd.GoToControl [cboField]
                              Me.OrderBy = "[LM Platform] DESC"
                               'Me.OrderBy = "[LM Platform].column (1) DESC"
                         Else
                              If [cboSort] = "Ascending" Then
                                   DoCmd.GoToControl [cboField]
                                   Me.OrderBy = "[" & [cboField] & "] ASC"
                              Else
                                   If [cboSort] = "Descending" Then
                                        DoCmd.GoToControl [cboField]
                                        Me.OrderBy = "[" & [cboField] & "] DESC"
                                   End If
                              End If
                         End If
                    End If
               End If
          End If
     End If
End Sub

I tried to use the below, but as I stated, it will sort the fields that are combo boxes based on the ID.

                              If [cboSort] = "Ascending" Then
                                   DoCmd.GoToControl [cboField]
                                   Me.OrderBy = "[" & [cboField] & "] ASC"
                              Else
                                   If [cboSort] = "Descending" Then
                                        DoCmd.GoToControl [cboField]
                                        Me.OrderBy = "[" & [cboField] & "] DESC"

I even played around with adjusting the rowsource so that the Name I wanted sorted would be column 0 and then changed the bound column to 3. Still sorting by the ID. I am assuming that this is due to the fact that the field is PlatformID and using rowsource for the combo box. I know this would be easier if I used a DataSheet, but the headers are too big to fit and need the flexibility to show the headers wrapped. Any insight would be appreciated.

[June7]

Exactly what is the combobox RowSource? Why would a combobox that lists field names have multiple columns? Exactly what data type are these fields and what data do they contain?

If you want to provide db for analysis, follow instructions at bottom of my post.

BTW, a form bound to table will sort/filter/search just fine.

[moke123]

I made change to field names to eliminate spaces but set combo to show the names as you have them. Also used option group but you should get the idea.
The code is a wee bit shorter than yours

Private Sub Combo0_AfterUpdate()
    SetSort
End Sub

Private Sub Frame5_AfterUpdate()
    SetSort
End Sub

Sub SetSort()

    Select Case Me.Frame5

        Case 1
            Me.OrderBy = Me.Combo0.Column(1) & " Asc"
        Case 2
            Me.OrderBy = Me.Combo0.Column(1) & " Desc"
    End Select

End Sub

[mlrucci]

So, this was such a stupid thought process and had to go back and look at things. First, I know you shouldn't have spaces in the field names. I was working to look at using a field list for combo box. Visually, for the end user, that's not so pretty. First mistake. Fixed that by just using a value list for the drop down. Second, the fields on the form are based on the ID "Primary Key". hence, was sorting based on the ID. Doing what it is supposed to do. Working with the .column idea didn't work either. I stepped back and rethought out the process. Got it working by adding an additional field to the form for the FieldName that is associated with the FieldID. DUH...Then just used that field for the sort and made it visible=False. Below is the code I wound up doing and made it simple stupid. Sometimes, after working too long and getting tunnel vision, you don't think straight. Thanks for the help everyone. Hope this helps someone else.

Private Sub cboSort_AfterUpdate()


     If IsNull([cboField]) Then
          MsgBox "Please select a field to sort"
          Me.cboSort = ""
          Me.cboField.SetFocus
     Else
          If [cboSort] = "Ascending" Then
               Me.cboField.SetFocus
               Me.OrderBy = "[" & [cboField] & "] ASC"
               Me.cboSort = ""
          Else
               If [cboSort] = "Descending" Then
                    Me.cboField.SetFocus
                    Me.OrderBy = "[" & [cboField] & "] DESC"
                    Me.cboSort = ""
               End If
          End If
     End If
End Sub

[Welshgasman]

The trouble with Value Lists is, if you need a new one, code modification is needed, unless you read a table and build the list that way?
Why not have a Descripton for your combo entries, more useful to the user and show that instead of the actual value?

I did just that for reports.
Whilst a report might be called RptOutBalance, the descriptive name mght be Outstanding Balances to the user.

[mlrucci]

Nice..Need to think about that...Thanks for the insight