Sorting of subform by clicking the header 1

[aesarvan]

is there any way you can sort a subform by clicking a header?
ie can you write a sort code for the on click property of one of the headers in a subform?

 

[jabrony76]

One way to do this is to use a toggle button in the header of the form that changes the sql source of the subform.

To do this, you will have to define 2 sql statements, one with the sql being sorted ascending and the other descending. Something like this would go into the 'after update' of the toggle button.

Dim strsql1 As String
Dim strsql2 As String

strsql1 = "SELECT DISTINCTROW [Field1], [Field2] "
strsql1 = strsql1 & "FROM [tblYourTableHere] "
strsql1 = strsql1 & "ORDER BY [Field1] ASC"

strsql2 = "SELECT DISTINCTROW [Field1], [Field2] "
strsql2 = strsql1 & "FROM [tblYourTableHere] "
strsql2 = strsql1 & "ORDER BY [Field1] DESC"

If Me.Toggle1 = True Then

Forms!frmFormName!subfrmSubFormName.RowSource = strsql1
Forms!frmFormName!subfrmSubFormName.Requery
Me.Toggle1.Caption = "Field 1 (Asc)"

End If

If Me.Toggle1.Value = False Then

Forms!frmFormName!subfrmSubFormName.RowSource = strsql2
Forms!frmFormName!subfrmSubFormName.Requery
Me.Toggle1.Caption = "Field 1 (Desc)"

End If

 

[pbbriggs]

I wanted to do the same thing as aesarvan... I tried using jabrony76's solution but every time I click the toggle button, I receive the error "Object doesn't support this property or method." The line that is hanging it up is the "RowSource" line.

Could there possibly be an error in the SQL (I'm decent at VB/VBA but terrible at SQL)? I tried adding in (below the "If" statements) a "DoCmd.RunSQL strsql1" line. When I did this, the error changed to "A RunSQL action requires an argument consisting of an SQL statement," which makes me wonder if the problem could be with the SQL somehow...?

Thanks for the help.

 

[GoDawgs]

Try changing "Rowsource" to "RecordSource". Hope that helps.

Kevin

 

[LarryDeLaruelle]

aesarvan:

If your subform is in continuous format, you can use this. It's from a paper I use in class:

Access Code Sample

Setting sort order command buttons on Continuous Display forms.

For each field to be sorted, add a command button to the form header band.

Provide an appropriate caption
Provide an appropriate name
In the On_Click event for each button add this code:

strSortField = “FieldName”
Call SortOrder

In the General Declarations section of the form’s module add this:

Dim strSortfield As String

Add a sub procedure (private) to the form’s module named SortOrder; enter this code:

Private Sub SortOrder()
Dim bolOrder As Boolean
Dim strOrder As String

Screen.ActiveForm.AllowAdditions = False
DoCmd.GoToControl strSortField

strOrder = InputBox("Enter A for Ascending; D for Descending", "Sort Order", "A&quot

If strOrder = "A" Then
bolOrder = True
ElseIf strOrder = "d" Then
bolOrder = False
Else
MsgBox "Invalid Selection", vbOKOnly + vbCritical, "Sort Order"
Exit Sub
End If

If bolOrder Then
DoCmd.RunCommand acCmdSortAscending
Else
DoCmd.RunCommand acCmdSortDescending
End If

End Sub

This is based on using Command Buttons as the headers, but, of course, you can use this with the On Click event of a label as well.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[pbbriggs]

godawgs, changing to recordsource did not seem to help.

I actually found Larry De Laruelle's post (which he just posted here) in another thread. I modified it slightly to include the code in a toggle button (and removed the ascending/descending prompt), and it did the trick perfectly. It avoids the SQL issue altogether, which I believe was what was causing me problems.

Thanks, Larry! You get a star from me because your solution worked after a day of head-pounding.

Patricia

 

[BillPower]

Sorry, I started this an hour or so ago.

This method has a different approach. Left Click = Ascending, Right = Descending. In the On Click event of the Title Label:

Me!Field.SetFocus
If Button = 1 Then
DoCmd.RunCommand acCmdSortAscending
Else
DoCmd.RunCommand acCmdSortDescending
End If

There is a working version of this at

http://www.wpower.fsbusiness.co.uk/

The file to look at is Buttons.zip.

Bill