Sort Table in subform by clicking on column heading

[Burt Rosner]

To Anyone that can help,

I would like to Sort a Table in a subform by clicking on
one of the column headings in the table.

If it is possble, please advise.

Right now I am using the menu bar sort commands. I'd like
the program to act a little more professional then that.

Thanks for any advice,

Burt

 

[Allen Browne]

If you use a continuous form, you can respond to the Click event of the
label over the column.

Set the On Click property to:
=SortForm([Form], "Surname")
to sort by the Surname field.

Paste the function below into a module.

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

Exit_SortForm:
Exit Function

Err_SortForm:
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_SortForm
End Function

 

[Burt Rosner]

-----Original Message-----
If you use a continuous form, you can respond to the Click event of the
label over the column.

Set the On Click property to:
=SortForm([Form], "Surname")
to sort by the Surname field.

Paste the function below into a module.

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

Exit_SortForm:
Exit Function

Err_SortForm:
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_SortForm
End Function

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

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

To Anyone that can help,

I would like to Sort a Table in a subform by clicking on
one of the column headings in the table.

If it is possble, please advise.

Right now I am using the menu bar sort commands. I'd like
the program to act a little more professional then that.

Thanks for any advice,

Burt

.
Allen,

Thanks. I'll try that. Also, I was messing around and
found the if I insert code into the subforms "on click"
event that identifies when to use acCmdSortAscending or
acCmdSortDescending it accomplishes (I think) what I want
to do.

Thanks again,

Burt