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

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Darrell Childress" <drc_NO@drcSPAMfire.com> wrote in message
news:46eaa876$0$32488$baae4c71@news.mindlink.net...
>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 

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

Allen Browne wrote:
> 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
>