Sorting within a form

[Jessica]

Greetings!

I want to create a form where the user can sort the
records by clicking on label for each column (one click
sorts ascending, clicking again sorts descending). How do
I go about this?

If this is not possible I would like to have recreate the
sorting buttons from the menu bar on the form. So that
when the user clicks the button it will sort the form
based on the current column.

Any help would be greatly appreciated.

Thanks!
Jessica

 

[Allen Browne]

Set the OrderBy property of your form, depending which label/button was
clicked.

Paste the function below into a module, and save. You can then call it from
any form. For example, set the On Click property of the label over the
Surname column to this:
=SortForm([Form], "Surname")
(Note: The "[Form]" bit stays exactly like that.)


Public Function SortForm(frm As Form, ByVal sOrderBy As String)
'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")

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
End If
End Function