Soring on a subform

[richmond88]

I have a subform that I want to sort both in ascending and descending order. It can be sorted on any one of 4 fields.

The subform has a query bound to it.

I presumably have 4 toggle buttons.

This may be a bit vague but can someone help me with the code to do it.

Thaks

 

[formerTexan]

Richmond,

Here is one approach that works well for form's in continuous view.

Set the subform's OrderByOn property to True.

Add command button's in the form header to serve as column headers/labels.

And then paste the following into the button's OnClick event. YourFieldName will be whatever field you wish to sort by,

Dim strOrderBy As String
Static x As Boolean
x = Not x
If x Then
strOrderBy = "YourFieldName" & " ASC"
Else
strOrderBy = "YourFieldName" & " DESC"
End If
Me.OrderBy = strOrderBy


Cheers,
Bill

 

[obli]

Here is a great place to view and download a similar sample:

http://www.microsoft-accesssolutions.co.uk/sort_listbox_columns.htm

 

[obli]

After much searching I found a site with a sample that deals specifically with a subform.

http://www.rogersaccesslibrary.com/download3.asp?SampleName=ListBoxSorting.mdb

 

[richmond88]

Thanks for the replies folks.

I have decided to go with obli's solution.

I do have a problem with it though. I am sorting on 2 fields - StudentName and Effor. The student name works fine but I get a runtime 2001 on Effort Button - You cancelled previous operation.

Below is the code that I am using.

Private Sub cmdOrderEffortATS_Click()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("EffortATSPercentageTotal", "asc")
Me!cmdOrderEffortATSDesc.Visible = True
Me!cmdOrderEffortATSDesc.Caption = "v Application v"
Me!cmdOrderEffortATSDesc.SetFocus
Me!cmdOrderEffortATS.Visible = False
'Me!lstSearch.SetFocus

End Sub


Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String

'Clear captions from command buttons
ClearCaptions

'Set row source for list box
strSQL = "SELECT * "
strSQL = strSQL & "FROM qselXCurricularHOYTutorYrList "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me.RecordSource = strSQL ****BOMS OUT HERE****
Me.Requery

End Function

Any Help will be gratefully appreciated