Sort subreport results based on a form option group

Posted on 2014-08-02
Last Modified: 2014-08-07
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

Question by:SteveL13
    LVL 31

    Accepted Solution

    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

    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    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"

    Author Comment

    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,
    LVL 74

    Expert Comment

    by: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...

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now