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

 

[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

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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

The module cannot have the same name as the procedure.

Rename either the module or the function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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.

 

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

 

[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

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

Reply to group, rather than allenbrowne at mvps dot org.

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]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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])