How to pass filter form criteria to a sub report??

Jabba007 · July 12th, 2007, 09:11 PM

I have a filter form that passes criteria to a report that contains two sub reports. Right now, I am able to pass filter criteria into the main report successfully. However, one of the filter criterion on the filter form is a column within one of the sub reports. The problem I am having is not being able to figure out how to pass criteria to the sub report dynamically (i.e. from the filter form). Otherwise, if I manually enter the filter criteria into the sub report's Filter property, then I am able to meet the requirement just fine. Unfortunately for me, the Filter property of the sub report needs to set dynamically from the filter form. Any ideas on how to pass form filter criteria into the sub report are greatly appreciated!

Below is the VBA code that resides behind the command button (cmdViewReport) on the filter form that opens the main report with embedded sub reports. Criteria that is selected within the filter form control ListNoteType should ultimately be used to filter the column note_type_id within the sub report named rpt_business_reqs_subreport_configuration_notes (not referenced in any of the code below).

Code:

Private Sub cmdViewReport_Click()
    Dim FilterString As String
    'Dim FilterString2 As String

    ' Add the controls to our report filter criteria
    AddFilterCriteria FilterString, ListRequirementID, "id"
    AddFilterCriteria FilterString, ListFunction, "function_id"
    AddFilterCriteria FilterString, ListProcess, "process_id"
    AddFilterCriteria FilterString, ListStatus, "status_id"
    'AddFilterCriteria FilterString2, ListNoteType, "note_type_id"

    'check to see if any results return
    Dim results As New ADODB.Recordset
    Dim reccnt As Integer
    reccnt = 0
    If FilterString <> "" Then
        With results
            .CursorLocation = adUseClient
            .Open "select * FROM tbl_business_reqs WHERE " & FilterString, _
                CurrentProject.Connection, adOpenKeyset, adLockOptimistic
            reccnt = .RecordCount
            .Close
        End With
    Else
        With results
            .CursorLocation = adUseClient
            .Open "select * FROM tbl_business_reqs", _
                CurrentProject.Connection, adOpenKeyset, adLockOptimistic
            reccnt = .RecordCount
            .Close
        End With
    End If

    ' Open the form
    Debug.Print FilterString
    If reccnt = 0 Then
        MsgBox "The selected filter criteria did not return any matching records.  Please clear your criteria and try again."
    Else
        DoCmd.OpenReport "rpt_business_reqs_with_configuration_details", acViewPreview, , FilterString
        If FilterString <> "" Then
            With Reports![rpt_business_reqs_with_configuration_details]
                .Filter = FilterString
                .FilterOn = True
                .lblFilterCriteria.Visible = True
                .txtFilterCriteria.Visible = True
                .txtFilterCriteria.Value = FilterString
            End With
        Else
            With Reports![rpt_business_reqs_with_configuration_details]
                .lblFilterCriteria.Visible = False
            End With
        End If
    End If
End Sub

I believe that user selections within the filter form control ListNoteType should be captured within a different string (e.g. FilterString2) than the other controls (all of which tie to the main report), but that's basically as far as I've gotten so far.

Please help!