Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    21

    Unanswered: Filtering both the form and sub form in a report

    Howdy,

    I have a report which contains form information and subform information. In both the report and subreport, I have a person's name for each entry. On a seperate form, I have my filter settings. The report criteria information are passing through the DoCmd.OpenReport on the same form. The subreport filters are using the Union Join on the open event of the report. I have used

    Microsoft Access tips: Filter a Form on a Field in a Subform

    as my example. However, all of the report filter criteria is being applied to the subreport. I end up getting "enter parameter value" errors. Any way to fix this? And on a more general note, is there a way to do an either or filter? (I want to show all entries for Apples and Oranges, but not anything else)

    The form filter code:

    Code:
    Private Sub Command14_Click()
    On Error GoTo Err_Command14_Click
    
        Dim stDocName As String
        Dim blAnd As Boolean
    
    Dim strQuery As String
    Dim dtstartdate As Date
    Dim dtenddate As Date
    Dim dtEndDateTeam As Date
    Dim dtStartDateTeam As Date
    Dim dtRCAstart As Date
    Dim dtRCAend As Date
    
    If Combo30 <> "<ALL>" Then
        If blAnd = True Then strQuery = strQuery & " And "
        strQuery = strQuery & "(([qryActionItems].[RCA Type]) = '" & Combo30.Value & "')"
        blAnd = True
    End If
    
    If Combo15 <> "<ALL>" Then
        If blAnd = True Then strQuery = strQuery & " And "
        strQuery = strQuery & "(([qryActionItems].[unit]) = '" & Combo15.Value & "')"
        blAnd = True
    End If
    
    If Option8 = True Then
        If blAnd = True Then strQuery = strQuery & " And "
        strQuery = strQuery & "(([qryActionItems].[completed]) = false)"
        blAnd = True
    End If
    
    If Areareportfilter <> "<ALL>" Then
        If blAnd = True Then strQuery = strQuery & " And "
        strQuery = strQuery & "(([qryActionItems].[Area]) = '" & Areareportfilter.Value & "')"
        blAnd = True
    End If
    
        stDocName = "RCA Log"
        DoCmd.OpenReport stDocName, acPreview, , strQuery
    
    Exit_Command14_Click:
        Exit Sub
    
    Err_Command14_Click:
        MsgBox Err.Description
        Resume Exit_Command14_Click
        
    End Sub
    And the Open report code for the Union Join
    Code:
    Private Sub Report_Open(Cancel As Integer)
    
        Dim strSQL As String
        Dim bWasFilterOn As Boolean
    
        ' Save the FilterOn state. (It's lost during RecordSource change.)
        bWasFilterOn = Me.FilterOn
    
    If IsNull(Forms![Frm_report].[Combo10]) Or Forms![Frm_report].[Combo10] = "<ALL>" Then
        ' If the combo is Null, use the whole table as the RecordSource.
        Me.RecordSource = "qryAllReportLog"
    Else
        strSQL = "SELECT DISTINCTROW qryAllReportLog.* FROM qryAllReportLog " & _
            "INNER JOIN qryAllActionItems ON " & _
            "qryAllReportLog.ProjectType = qryAllActionItems.ProjectType " & _
            "AND qryAllReportLog.TrackingNo = qryAllActionItems.ProjectLogNo " & _
            "WHERE qryAllActionItems.ActionPPR = """ & Forms![Frm_report].[Combo10] & """;"
        Me.RecordSource = strSQL
    End If
    
        ' Apply the filter again, if it was on.
        If bWasFilterOn And Not Reports![RCA Log].FilterOn Then
            Reports![RCA Log].FilterOn = True
        End If
    
    
    End Sub

  2. #2
    Join Date
    May 2009
    Posts
    21
    Ok, I think I was alittle bit confusing in my question. This is what I mean. I want to filter for all entries related to "Sam". So if I have a database like so:

    1.Bill
    a.Tom
    b.Jamie
    c.Peter
    d.Sam

    2.Sam
    a.Sam
    b.Jane
    c.Peter

    3.Jamie
    a.Peter
    b.Peter
    c.Jane

    It would fiter to:

    1.Bill
    a.Tom
    b.Jamie
    c.Peter
    d.Sam

    2.Sam
    a.Sam
    b.Jane
    c.Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •