asked on

Sort subreport results based on a form option group

I have a form that contains an option group. Then I have a report with a subreport and I want the results of the subreport to sort based on the selection made in the form's option group. The following code which I have in the subreport's onopen event works fine if I just print preview the subreport.

But if I print preview the report (which contains the subreport) nothing happens.

    If Forms!frmSelect.Frame133.Value = 1 Then
    
        Me.OrderBy = "[CategoryName]"

        ElseIf Forms!frmSelect.Frame133.Value = 2 Then
        Me.OrderBy = "[Location]"

        ElseIf Forms!frmSelect.Frame133.Value = 3 Then
        Me.OrderBy = "[Room]"

        End If

    Me.Report.OrderByOn = True

Open in new window

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

I have this in the afterupdate event of the option group:

    If Forms!frmSelect.Frame133.Value = 1 Then
        pubstrSort = "Category"

    ElseIf Forms!frmSelect.Frame133.Value = 2 Then
        pubstrSort = "Location"

    ElseIf Forms!frmSelect.Frame133.Value = 3 Then
        pubstrSort = "Room"

    End If

    DoCmd.OpenReport "Asset List View Report", acViewPreview, , , acWindowNormal

Open in new window

And I have this in the onopen event of the subreport:

Dim strRecScr As String
    strRecScr = "SELECT * FROM qryAssetListView ORDER BY " & pubstrSort
    Me.RecordSource = strRecScr
End Sub

Open in new window

But it is not working. I get these two windows popping up:

Then the report opens but is not sorted by my choice,

Jeffrey Coachman

Post a sample db...

This all worked fine for me in the sample I posted in the db I posted in your previous question. All I did here was use an option group instead of a combobox...