You can sort a form by setting its OrderBy property, remembering to turn on
OrderByOn as well.
If you paste the function below into a module, you can use it in any form.
For example, to sort by the City field, add this line of code to the Click
event procedure of your button:
Call SortForm(Me, "City")
If the form was already sorted by this field, it swaps direction (z to a.)
Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
On Error GoTo Err_SortForm
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Return: True if success.
'Usage: Command button above a column in a continuous form:
' Call SortForm(Me, "MyField")
Dim sForm As String ' Form name (for error handler).
sForm = frm.Name
If Len(sOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.OrderBy = sOrderBy) Then
sOrderBy = sOrderBy & " DESC"
End If
frm.OrderBy = sOrderBy
frm.OrderByOn = True
' Succeeded.
SortForm = True
End If
Exit_SortForm:
Exit Function
Err_SortForm:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_SortForm
End Function
