Sort order

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
 
A

Allen Browne

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
 
J

Josie

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.
Click to expand...
Click to expand...
 
J

Josie

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.
Click to expand...
Click to expand...
 
A

Allen Browne

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.
Click to expand...
Click to expand...
Click to expand...
 
J

Josie

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting a column in a datasheet programatically 2
Create form for 1:N relationsip 3
sort order in subform 3
Change recorset 1
Looking for New Approach 1
Sort form records by field looked up in another table 1
Custom Shortcut Menus 1
Sort subform by inputted column name 1

Top