Learn how to a build a cloud-first strategyRegister Now


Sort subreport results based on a form option group

Posted on 2014-08-02
Medium Priority
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
  • 2
LVL 31

Accepted Solution

Helen Feddema earned 1000 total points
ID: 40236537
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
Jeffrey Coachman earned 1000 total points
ID: 40238026
Again (as in your other question)
...is 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

ID: 40238840
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
ID: 40239146
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

810 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