Sort by clicking on head button

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

In a Subform there are the following four field-columns:
system number, name, village and status. The Subform has a
query as source called Qry_Customer

I would like the user to have the opportunity to sort the
data according to any one of the field-columns with just
one click. What I did is to add four buttons in the form
header as labels for the columns. What would be the code I
need to enter behind each button (On click event) for
automatically sorting the data in the SubForm once the
user clicks on it.

Thank you for your help,

Marc
 
Paste the function below into a standard module (created from the Modules
tab of the Database window). If the form is already sorted by this field, it
reverses the sort (like Details view of the Windows Explorer).

Then set on On Click property of your buttons to:
=SortForm([Form], "MyField")
and change "MyField" to the name of the field to be sorted by.


Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
'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
SortForm = True
End If
End Function
 
Bad news! You have to write a little code! But it's easy!
Lets suppose that you want to sort the subform's underlying query ascending
or descending.
1. You have to replace the command buttons with option buttons. You have to
do that in order
to have, according of the state of the buttons, acsending or descending
order.
2. Go to the query's SQL statement and copy it.
3. Open the form's code module.
4. Now lets suppose that the four buttons are named: opbSystem, opbName,
opbVillage, opbStatus
and there Defualt Value is False
5. Write the following piece of code:
===== Code Starts =====
Private Sub sSortSubForm ()
Dim strSQL as String

On Error Goto Error_sSortSubForm

strSQL = <Past here the previously copied SQL statement from
Qry_Customer>
'You have to delete any ORDER BY statement already existing in the
statement

strSQL = strSQL & "ORDER BY "

strSQL = strSQL & "Qry_Customer.[system number] " &
Choose(Me.opbSystem+2, "ASC, ", "DESC, ")

strSQL = strSQL & "Qry_Customer.[name] " & Choose(Me.opbName+2, "ASC, ",
"DESC, ")

strSQL = strSQL & "Qry_Customer.[village] " & Choose(Me.opbVillage+2,
"ASC, ", "DESC, ")

strSQL = strSQL & "Qry_Customer.[status] " & Choose(Me.opbStatus+2,
"ASC", "DESC")

me.[SubFormName].Form.RecordSource = strSQL
me.[SubFormName].Requery

Exit_sSortSubForm:
Exit Sub

Error_sSortSubForm:
Select Case Err.Number
Case Else
MsgBox "sSortSubForm>Unexpected Error No: " & Err.Number &
vbCrLf & Err.Description
Resume Exit_sSortSubForm
End Select
End Sub
==== Code Ends =====
6. Now on each of the four Option button OnClick event write:
==== Code Starts =====
Call sSortSubForm
==== Code Ends =====

You could also on the Form_Load event put:
==== Code Starts =====
Call sSortSubForm
==== Code Ends =====
among other, if any, statements


That could be a solution!
Margaritis Paktitis
 
Allen--

I liked this idea so much I took the liberty
of "borrowing" it for my own use -- it works great!

By the way: you don't have to replace the Labels with
Command Buttons -- it works equally well with Labels (as
they both have the "on click" event)

--Gary
 
Allen, I've tried your recommendation for sorting but get
an error at =SortForm([Form], "MyField"). It needs
something in front of =, like a variable, but I cannot see
where that variable would be called in the module. I'm a
beginner, so perhaps I'm missing something?
Christine

-----Original Message-----
Paste the function below into a standard module (created from the Modules
tab of the Database window). If the form is already sorted by this field, it
reverses the sort (like Details view of the Windows Explorer).

Then set on On Click property of your buttons to:
=SortForm([Form], "MyField")
and change "MyField" to the name of the field to be sorted by.


Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
'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
SortForm = True
End If
End Function

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

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

In a Subform there are the following four field-columns:
system number, name, village and status. The Subform has a
query as source called Qry_Customer

I would like the user to have the opportunity to sort the
data according to any one of the field-columns with just
one click. What I did is to add four buttons in the form
header as labels for the columns. What would be the code I
need to enter behind each button (On click event) for
automatically sorting the data in the SubForm once the
user clicks on it.

Thank you for your help,

Marc
Click to expand...


.
Click to expand...
 
That was intended as the property setting for On Click of the button.

If you want to use it in code:
Call SortForm(Me, "MyField")

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

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

Christine said:
Allen, I've tried your recommendation for sorting but get
an error at =SortForm([Form], "MyField"). It needs
something in front of =, like a variable, but I cannot see
where that variable would be called in the module. I'm a
beginner, so perhaps I'm missing something?
Christine

-----Original Message-----
Paste the function below into a standard module (created from the Modules
tab of the Database window). If the form is already sorted by this field, it
reverses the sort (like Details view of the Windows Explorer).

Then set on On Click property of your buttons to:
=SortForm([Form], "MyField")
and change "MyField" to the name of the field to be sorted by.


Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
'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
SortForm = True
End If
End Function

In a Subform there are the following four field-columns:
system number, name, village and status. The Subform has a
query as source called Qry_Customer

I would like the user to have the opportunity to sort the
data according to any one of the field-columns with just
one click. What I did is to add four buttons in the form
header as labels for the columns. What would be the code I
need to enter behind each button (On click event) for
automatically sorting the data in the SubForm once the
user clicks on it.

Thank you for your help,

Marc
Click to expand...
Click to expand...
Click to expand...
 
Back
Top