sort

[Guest]

I use the method below, from one of the MVP sites, to change the sort order
of records being displayed. This works find in fields containing text value
but i need a method which will allow me to sort the data by the text held in
a combo, using this method on a combo sorts by the value of the box which is
usually a number and not the text.

Anyone help ?

Private Sub txtOrderID_DblClick(Cancel As Integer)
Call ChangeSort("OrderID")
End Sub

Private Sub ChangeSort(FieldName As String)
If Me.OrderBy <> FieldName Then
Me.OrderBy = FieldName
Else
Me.OrderBy = FieldName & " DESC"
End If
Me.OrderByOn = True
End Sub

 

[Allen Browne]

Paul, the most efficient way to sort by the text value of the combo is to
include its table in the RecordSource of the form.

1. If the form is currently based on a table, create a query. Include this
table, and also the table that feeds the combo's RowSource. Double-click the
join-line between these 2 tables: Access offers 3 options. Choose the one
that says:
All record from the main table, and any matches from the combo's table.
Add the text field from the combo's table to the query grid.
Save the query.

2. Open the form in design view.
Change its RecordSource property to the query.

3. Now that the combo's text field is in the form's source query, you can
sort on that field.

There is one quirk you may need to avoid. If Access complains that it is
unable to add a new record in this form, open the combo's table in design
view, and remove anything in the Default Value of its fields. Access can
misunderstand this default value, and tries to add a new record to the
lookup table.

 

[Guest]

Thanks for that Alan I have managed to get that working fine.