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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
I would recommend saving the sorted query from the AfterUpdate event of the option group, and then use that saved query as the subreport's record source.  The filtered query name would always be the same, but the query would be recreated every time you make a selection from the option group.  Here is some code:
Private Sub fraChoice_AfterUpdate()

   Dim intChoice As Integer
   Dim strSort As String
   Dim strRecordSource As String
   Dim strSQL As String
   Dim strQuery As String
   Dim lngcount As Long
   intChoice = Nz(Me![fraChoice], 1)
   strSort = Switch(intChoice = 1, "CategoryName", _
      intChoice = 2, "Location", _
      intChoice = 3, "Room")
   'Create SQL and save sorted query
   strRecordSource = "tblCompanyInfo"
   strQuery = "qrySortedInfo"
   strSQL = "SELECT * FROM " & strRecordSource _
      & " ORDER BY [" & strSort & "];"

   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngcount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngcount
End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 11-May-2013

On Error Resume Next
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   'Delete old query
   CurrentDb.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = CurrentDb.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = CurrentDb.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
End Function
In this example, qrySortedInfo would be the subreport's record source.

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Again (as in your other question) why I sometimes just grab the Reports Recordsouce and sort that ...
This technique works exactly the same as in my sample db for your previous Q:
Just change the form code to something like this:

If Forms!frmSelect.Frame133.Value = 1 Then
    pubstrSort = "EmpID"
ElseIf Forms!frmSelect.Frame133.Value = 2 Then
    pubstrSort = "EmpName"
SteveL13Author Commented:
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 CoachmanMIS LiasonCommented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.