Create button to sort records

[Darrell Childress]

I have a form that lists inventory parts. By default it lists them in
alphabetical order by PartNo. There are many times when the user needs
to see them in order by Description instead. Is there a way that I can
create a button to do this, and then a button to put it back in order by
PartNo. I know that the user can right-click on the field and click
"Sort Ascending", but some users aren't comfortable with that.
Thanks,
Darrell

 

[Allen Browne]

Paste the function below into a standard module.
You can then put a button on your form, and sort by your [Description] field
by setting its On Click property to:
=SortForm([Form], "[Description]")

For example, if you palce a button (or label) above each column of a
continuous form (in the Form Header section), the user can click that button
to sort by that column. If it is already sorted on that column, it reverses
the sort.

Public 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 Err.Description
Resume Exit_SortForm
End Function

 

[Darrell Childress]

Thanks so much Allen, works beautifully. I'm very thankful for these
newsgroups and the help offered!
Darrell