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.
Code:
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.
Code:
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.