Sort buttons on a form

[Andy Roberts]

Can I add buttons to a form to sort different fields ascending / descending

eg. I have a form with 3 fields -Last Name, First Name and Age. I would
like 2 buttons on each field to sort wither up or down on each field i.e. a
button to sort ascending on last name and another fordescending on last name
etc.

Andy

[Allen Browne]

Andy, add the code below to a standard module.

You can then set command button to sort by last name to:
=SortForm([Form], "[Last Name])
If it is already sorted by that field, it reverses the sort.

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:
Call LogError(Err.Number, Err.Description, conMod & ".SortForm()", "Form
= " & sForm & "; OrderBy = " & sOrderBy)
Resume Exit_SortForm
End Function

Use your own error handler, or grab this one:
http://allenbrowne.com/ser-23a.html

--
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.

"Andy Roberts" <an...@blue-bean.co.uk> wrote in message
news:W-idnSTbBNecz0nY...@bt.com...

[Andy Roberts]

Allen

Thanks for the response.

Ive (think) i've followed the instructions but it doesn't seem to work.

I have a module containing your code called SortForm1. I have a cmd button
with the onclick event set to =SortForm([Form], "[Last Name]). The command
button is in the header of the form not the detail section and I get an
error when I click it.

Andy

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message
news:eU%231BHQU...@TK2MSFTNGP02.phx.gbl...

[Allen Browne]

The module cannot have the same name as the procedure.

Rename either the module or the 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.

"Andy Roberts" <an...@blue-bean.co.uk> wrote in message

news:PKednUF_uLXb_UnY...@bt.com...

[Andy Roberts]

Allen

Still getting a problem.

The module is now called formsort and your code is left intact.

The onclick event is set to =SortForm([Form], [UserNo]) as this is the
field I want to sort

Andy

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message

news:eoV9uwQU...@TK2MSFTNGP02.phx.gbl...

[Ron2006]

Andy,

SortForm([Form], [UserNo])

SortForm is the Function name
[Form] is the name of the form that you have the button on
[UserNo] is the name of the field that you want to sort.

so perhaps it should be

=SortForm([FormName], [UserNo])

Ron

[Allen Browne]

No, the [Form] is literally that.
However, the field name needs to be in quotes, so:

=SortForm([FormName], "[UserNo]")

If that doesn't work open the code window, and choose Compile from the Debug
menu. If Access shows you any error, fix it, and repeat until it compiles
without error.

An alternative is to set the button's On Click property to:
[Event Procedure]
Then click the Build button (...) beside that.
Access opens the code window.
Between the Private Sub and End Sub lines, enter:
Call SortForm(Me, "[UserNo]")

You can also add breakpoints or put Stop in the code and then single step
through by pressing F8 to see what's going on.

--
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.

"Ron2006" <ronn...@hotmail.com> wrote in message
news:1171571161.7...@l53g2000cwa.googlegroups.com...

[Crane]

Allen,

This worked great for a form that i was using, but would like to use it on
other forms. i notice that the original function calls out a specific form.
how do i get the function / process to other forms?
--
Crane

[Allen Browne]

The function doesn't call a particular form.
You can pass in any form as the first argument.

In other words:
Call SortForm(Me, "[City]")
sorts on the City field of the form that contains this code.

This assumes you saved the SortForm() function in a standard module (one you
see on the Modules tab of the Database window), not in the module of a
particular form.

--
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.

"Crane" <donotspam.com> wrote in message
news:15F11D6B-84CD-40AB...@microsoft.com...

> Allen,
>
> This worked great for a form that i was using, but would like to use it on
> other forms. i notice that the original function calls out a specific
> form.
> how do i get the function / process to other forms?
> --
> Crane
>
>
> "Allen Browne" wrote:
>
>> No, the [Form] is literally that.
>> However, the field name needs to be in quotes, so:
>> =SortForm([FormName], "[UserNo]")
>>
>> If that doesn't work open the code window, and choose Compile from the
>> Debug
>> menu. If Access shows you any error, fix it, and repeat until it compiles
>> without error.
>>
>> An alternative is to set the button's On Click property to:
>> [Event Procedure]
>> Then click the Build button (...) beside that.
>> Access opens the code window.
>> Between the Private Sub and End Sub lines, enter:
>> Call SortForm(Me, "[UserNo]")
>>
>> You can also add breakpoints or put Stop in the code and then single step
>> through by pressing F8 to see what's going on.
>>