Hi,

I am using a form to open reports (screen shot attached) that enables the user to select filters as required. The filters are used to build the "Where" clause of the sql. I have a working solution, but I need to know if my approach is efficient or not, or if there is a better way to achieve this. Please see code[Private Sub Preview_Click()] below.

For anyone checking out the Reporting Menu form, the report type is selected first. This changes the drop down menu contents (code shown below). The sort order is determined within the form, however, I build the "Order by" string during the Open event on teh report. I do not build the Order By string in the form because not all reports have the same fields, or need to be sorted in the same way. I have provided an example of the code attached to the reports.

Private Sub Preview_Click()
'************************************************************************************************
' FUNCTION NAME:        Preview_Click
' PURPOSE:              Previews selected report and closes ReportsMenu
' INPUT PARAMETERS:     [cmboReports].Value
' RETURN:               Preview of selected report
'************************************************************************************************
On Error GoTo Err_Preview_Click
Dim strFilterCriteria As String, errMsg As String
 
'Confirm a report has been selected
    If IsNull(Me.cmboReports) Then
        MsgBox "Select a report to run from the Select Report combo box", vbInformation, "No Report Error"
        Exit Sub
    End If
 
'Set filter criteria for Service
    If IsNull(Me.SelService) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = "[Service]=" & "'" & Me.[SelService] & "'"
    End If
 
'Set filter criteria for ServiceType
    If IsNull(Me.SelServiceType) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[ServiceType]=" & "'" & Me.[SelServiceType] & "'"
    End If
 
'Set filter criteria for Corps
    If IsNull(Me.SelCorps) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[Corps]=" & "'" & Me.[SelCorps] & "'"
    End If
 
'Set filter criteria for Unit
    If IsNull(Me.SelUnit) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[Unit]=" & "'" & Me.[SelUnit] & "'"
    End If
 
'Set filter criteria for Sub Unit
    If IsNull(Me.SelSubUnit) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[SubUnit]=" & "'" & Me.[SelSubUnit] & "'"
    End If
 
'Set filter criteria for Sub Unit
    If IsNull(Me.SelSubSubUnit) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[SubSubUnit]=" & "'" & Me.[SelSubSubUnit] & "'"
    End If
 
'Set filter criteria for FileNo
    If IsNull(Me.selFile) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[FileNo]=" & "'" & Me.[selFile] & "'"
    End If
 
    MsgBox strFilterCriteria
    DoCmd.OpenReport [cmboReports], acPreview, , strFilterCriteria           'Open selected report
    DoCmd.Close acForm, "frmReportsMenu1", acSaveNo      'Close ReportsMenu
 
Exit_Preview_Click:
    Exit Sub
 
Err_Preview_Click:
    If Err = 2501 Then
        Resume Exit_Preview_Click
    Else
        MsgBox Err.Description
        Resume Exit_Preview_Click
    End If
End Sub
'*****************************************************************************************
Private Sub SelectReportType_AfterUpdate()
 
'************************************************************************************************
' FUNCTION NAME:        SelReportType_AfterUpdate
' PURPOSE:              Reset control source for Report listbox and requery
' INPUT PARAMETERS:     Me.SelReportType
' RETURN:               Report list box source data refreshed
'************************************************************************************************
On Error GoTo Err_Handler
Dim intReportSelection As Integer, strReportType As String, strCaption As String, sqlRowSource As String
 
    intReportSelection = SelectReportType.Value
 
    Select Case intReportSelection
 
        Case "1"
            strCaption = "Unit Reporting Menu"
            strReportType = "ReportUnit"
        Case "2"
            strCaption = "Chief Clerk Reporting Menu"
            strReportType = "ReportCC"
        Case "3"
            strCaption = "Statistics Reporting Menu"
            strReportType = "ReportStatistics"
        Case "4"
            strCaption = "Manning & Establishments Reporting Menu"
            strReportType = "ReportManning"
        Case "5"
            strCaption = "PMKeyS Reporting Menu"
            strReportType = "ReportPMKeyS"
        Case "6"
            strCaption = "Individual Readiness Reporting Menu"
            strReportType = "ReportAIRN"
        Case "7"
            strCaption = "Unit Register Reporting Menu"
            strReportType = "ReportRegister"
        Case "8"
            DoCmd.OpenForm "frmReportsMenu2", acNormal
            DoCmd.Close acForm, "frmReportsMenu1", acSaveNo
            Exit Sub
        Case "9"
            strCaption = "Registry && Filiing Reporting Menu"
            strReportType = "ReportRegistry"
        Case "10"
            strCaption = "Operations Reporting Menu"
            strReportType = "ReportOperations"
        Case "11"
            strCaption = "Staff-in-Confidence Reporting Menu"
            strReportType = "ReportStaffInConfidence"
        Case "12"
            strCaption = "Welfare-in-Confidence Reporting Menu"
            strReportType = "ReportWelfare"
        Case "13"
            strCaption = "Discipline Reporting Menu"
            strReportType = "ReportDiscipline"
        Case "14"
            strCaption = "Deployment / UAC Reporting Menu"
            strReportType = "ReportDeployable"
        Case "15"
            strCaption = "Formation Reporting Menu"
            strReportType = "ReportFormation"
    End Select
    
    sqlRowSource = "SELECT systblMenus.ObjectName, systblMenus.ObjectTitle FROM systblMenus LEFT JOIN systblMenusData ON systblMenus.ObjectName = systblMenusData.LinkObjectName WHERE (((systblMenus.ObjectHidden)<>Yes) AND ((systblMenusData.ObjectMenuLocation)='" & strReportType & "')) ORDER BY systblMenus.ObjectTitle;"
    
    Me.cmboReports.RowSource = sqlRowSource
    Me.cmboReports.Requery
    Me.Caption = strCaption
    Me.lblTitle.Requery
 
Exit_Handler:
        Exit Sub
Err_Handler:
        MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "SelectReportType_AfterUpdate()"
        Resume Exit_Handler
End Sub
'************************************************************************************************
Private Sub Report_Open(Cancel As Integer)
'************I N C O M P L E T E********************************
'************************************************************************************************
' FUNCTION NAME:        Report_Open
' PURPOSE:              Set OrderBy property (sort order) based on selection from ReportsMenu
' INPUT PARAMETERS:     intSortOption
' RETURN:               Report is sorted based on option selected
'************************************************************************************************
On Error GoTo Err_Handler
Dim intSortOption As Integer, strOrderBy As String
 
    intSortOption = Form_frmReportsMenu1.FrameSort.Value
 
    Select Case intSortOption
        Case "1"
           'Report default
            strOrderBy = "RankOECode DESC,Surname,Initials"
        Case "2"
            'Rank Alphabetical
            strOrderBy = "RankOECode DESC,Surname,Initials"
        Case "3"
           'Alphabetical
            strOrderBy = "Surname, Initials"
        Case "4"
            'SubUnit Rank Alphabetical
            strOrderBy = "SubUnitSortPri, RankOECode DESC,Surname,Initials"
        Case "5"
            'SubUnit SubSubUnit
            strOrderBy = "SubUnitSortPri,SortPriSubSubUnit, RankOECode DESC, Surname, Initials"
        Case "6"
            'Employee ID
            strOrderBy = "EID"
        Case "7"
            'Ascending Date (date field used may vary)
            strOrderBy = "Date, Surname, Initials"
        Case "8"
            'Descending Date (date field used may vary)
            strOrderBy = "Date DESC, Surname, Initials"
        Case "9"
            'Ascending Value (value field used may vary - must update Value field name, or delete option)
            strOrderBy = "Value, Surname, Initials"
        Case "10"
            'Descending Value (value field used may vary)
            strOrderBy = "Value DESC, Surname, Initials"
        Case Else
            MsgBox "Else"
            'Report default
            strOrderBy = "RankOECode DESC,Surname,Initials"
    End Select
 
    Me.OrderBy = strOrderBy
 
Exit_Handler:
        Exit Sub
Err_Handler:
        MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "Report_Open()"
        Resume Exit_Handler
End Sub

Select allOpen in new window

ScreenShot007.png