Allow user to select a report via a dropdown in a form?

[templeowls]

I have a form to filter a report. I was wondering if its possible to allow the user to select which report they want to filter by having a dropdown field in the form whcih includes the three reports. All three reports are identical except the second and third have 1 & 2 more fields included, respectively. The report names are rptComplaints, rptComplaintsFollowUp, rptComplaintsDate



Below is a my code in the form to filter. Any recommendations?

Private Sub btnOpenReport_Click()On Error GoTo Err_Handler
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Dim strFilter As String
    Set frm = Forms!frmSearchTool
    strFilter = ""
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    
    strReport = "rptComplaints"
    strDateField = "[SubmissionDate]"
    lngView = acViewReport
    
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    
    'If Len("" & frm!ComboLocation) = 0 Then
    '    Me.Filter = ""
    '    Me.FilterOn = False
    '    Exit Sub
    'End If
    
    'Location
    'If Len("" & frm!ComboLocation) > 0 Then
    If Not IsNull(frm!ComboLocation) Then
        'strFilter = "[Location] = '" & frm!ComboLocation & "'"
        If strWhere <> "" Then
         strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Location] ='" & frm!ComboLocation & "'"
    End If
    
    'Add filter
    'Me.Filter = strFilter
    'Me.FilterOn = True
    
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    DoCmd.OpenReport strReport, lngView, , strWhere




Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

[Gicu]

Add a combo on the form called cboSelectedReport, set its row source type to Value list, its row source to "rptComplaints; rptComplaintsFollowUp; rptComplaintsDate"

Private Sub btnOpenReport_Click()On Error GoTo Err_Handler
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Dim strFilter As String
    Set frm = Forms!frmSearchTool
    strFilter = ""
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    'add a combo box on the form named cboSelectedSeport
    strReport = Me.cboSelectedReport
    'strReport = "rptComplaints"


    strDateField = "[SubmissionDate]"
    lngView = acViewReport
    
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    
    'If Len("" & frm!ComboLocation) = 0 Then
    '    Me.Filter = ""
    '    Me.FilterOn = False
    '    Exit Sub
    'End If
    
    'Location
    'If Len("" & frm!ComboLocation) > 0 Then
    If Not IsNull(frm!ComboLocation) Then
        'strFilter = "[Location] = '" & frm!ComboLocation & "'"
        If strWhere <> "" Then
         strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Location] ='" & frm!ComboLocation & "'"
    End If
    
    'Add filter
    'Me.Filter = strFilter
    'Me.FilterOn = True
    
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    DoCmd.OpenReport strReport, lngView, , strWhere








Exit_Handler:
    Exit Sub




Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

[templeowls]

That worked!!

This might be too in the weeds, but is there a way to customize the way that the report names appear in the dropdown? Like instead of appearing as rptComplaintsDate, it would help the user if it appeared in the dropdown as Complaint Report with closed date.

[Gicu]

Of course there is; you need to create a new table with two fields, the first one holding the actual report names (as you have them now in the row source) and the other holding the desired cation or combo listing. Now change the combo's row source type to Table\Query, select the new table, in the combo's property box change the number of columns to 2, set the column widths to 0";2" and voila, all done.

Cheers,

[Micron]

You should have a table of reports and show a useful name in a combo column but hide the bound column (report object name). Could include description column.
If you'll have many reports, a listbox may be a better choice. Whichever, user selection could show/hide criteria controls according to the fields that the table says are required for the chosen report (obviously you'd have to have fields for all the criteria controls on the form that relate to all the reports).

All three reports are identical except the second and third have 1 & 2 more fields included,

Probably could have accomplished this with one report object.

[templeowls]

Thank you both! This did the trick. Admittedly I need to get much better about creating tables that can be referred on elsewhere in the DB. This would make things much simpler

[alansidman]

Here is how I allow users to select different queries to run. You can change this up to be reports. Look at the design to understand how I achieved this. The file is a bit bigger than this site allows, so I have posted it on Box.net. Here is the link to download and look at it. Once you open, click on the search button at the top of the main form. You will need to go to the design for the form to see all the details. https://app.box.com/s/j97r5ob5hng9wfxzmfbt7gadjyimol9q

[Micron]

Standard practice here is to upload a zip file after compacting a copy of the db. I only point that out because I see that your file isn't zipped so I presume the one you attempted to post wasn't either.

[alansidman]

Micron
File was still too big after zipping.

Alan