Sort order

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Can this be done:

We have a form which has a subform. The subform can only be seen as a
datasheet. The recordsource for that subform has 3 fields visible PartNo,
PartName, PartType.

What we want to be able to do is, for example, click on the PartNo column
and the sort order of the recordsource is reversed i.e from ascending to
descending or vice versa. The same action also for the PartName And PartType
columns.

Using Access 2003.

Any advice on the issue would be appreciated

Josie
 
Paste the function below into a standard module, and save.

Set the On Click property of the label over the PartNo field to:
=SortForm([Form], "PartNo")

Repeat for other buttons. Leave the [Form] exactly as is, and replace the
PartNo with the actual fields names (enclosed in square brackets if they
contain spaces).

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

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 "Error " & Err.Number & " - " & Err.Description
Resume Exit_SortForm
End Function
 
Allen

Thanks for your help.

Have followed your instructions but when I click on the PartNo column get
message "Microsoft Office Access can't find the macro SortForm([Form],
"PartNo")

Any suggestions

Josie

Allen Browne said:
Paste the function below into a standard module, and save.

Set the On Click property of the label over the PartNo field to:
=SortForm([Form], "PartNo")

Repeat for other buttons. Leave the [Form] exactly as is, and replace the
PartNo with the actual fields names (enclosed in square brackets if they
contain spaces).

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

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

Tom said:
Can this be done:

We have a form which has a subform. The subform can only be seen as a
datasheet. The recordsource for that subform has 3 fields visible PartNo,
PartName, PartType.

What we want to be able to do is, for example, click on the PartNo column
and the sort order of the recordsource is reversed i.e from ascending to
descending or vice versa. The same action also for the PartName And
PartType columns.

Using Access 2003.
 
Allen

Thanks for your help.

Have followed your instructions but when I click on the PartNo column get
message "Microsoft Office Access can't find the macro SortForm([Form],
"PartNo")

Any suggestions

Josie

Allen Browne said:
Paste the function below into a standard module, and save.

Set the On Click property of the label over the PartNo field to:
=SortForm([Form], "PartNo")

Repeat for other buttons. Leave the [Form] exactly as is, and replace the
PartNo with the actual fields names (enclosed in square brackets if they
contain spaces).

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

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

Tom said:
Can this be done:

We have a form which has a subform. The subform can only be seen as a
datasheet. The recordsource for that subform has 3 fields visible PartNo,
PartName, PartType.

What we want to be able to do is, for example, click on the PartNo column
and the sort order of the recordsource is reversed i.e from ascending to
descending or vice versa. The same action also for the PartName And
PartType columns.

Using Access 2003.
 
Did you include the "="?

Did you save the function in a standard module (i.e. one that you reach
through the Modules tab of the Database window)?

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

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

Josie said:
Allen

Thanks for your help.

Have followed your instructions but when I click on the PartNo column get
message "Microsoft Office Access can't find the macro SortForm([Form],
"PartNo")

Any suggestions

Josie

Allen Browne said:
Paste the function below into a standard module, and save.

Set the On Click property of the label over the PartNo field to:
=SortForm([Form], "PartNo")

Repeat for other buttons. Leave the [Form] exactly as is, and replace the
PartNo with the actual fields names (enclosed in square brackets if they
contain spaces).

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

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 "Error " & Err.Number & " - " & Err.Description
Resume Exit_SortForm
End Function


Tom said:
Can this be done:

We have a form which has a subform. The subform can only be seen as a
datasheet. The recordsource for that subform has 3 fields visible
PartNo,
PartName, PartType.

What we want to be able to do is, for example, click on the PartNo
column
and the sort order of the recordsource is reversed i.e from ascending to
descending or vice versa. The same action also for the PartName And
PartType columns.

Using Access 2003.
 
Sorry Allen

I'm being a bit of a dipstick - the "=" had been included but there was a
space before the "=" - Your code works brilliant

Thanks again

Josie
Allen Browne said:
Did you include the "="?

Did you save the function in a standard module (i.e. one that you reach
through the Modules tab of the Database window)?

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

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

Josie said:
Allen

Thanks for your help.

Have followed your instructions but when I click on the PartNo column get
message "Microsoft Office Access can't find the macro SortForm([Form],
"PartNo")

Any suggestions

Josie

Allen Browne said:
Paste the function below into a standard module, and save.

Set the On Click property of the label over the PartNo field to:
=SortForm([Form], "PartNo")

Repeat for other buttons. Leave the [Form] exactly as is, and replace
the
PartNo with the actual fields names (enclosed in square brackets if they
contain spaces).

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

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 "Error " & Err.Number & " - " & Err.Description
Resume Exit_SortForm
End Function


Can this be done:

We have a form which has a subform. The subform can only be seen as a
datasheet. The recordsource for that subform has 3 fields visible
PartNo,
PartName, PartType.

What we want to be able to do is, for example, click on the PartNo
column
and the sort order of the recordsource is reversed i.e from ascending
to
descending or vice versa. The same action also for the PartName And
PartType columns.

Using Access 2003.
 
Back
Top