FilterOn in subreports

[jsic1210]

Hello all,

When filtering subreports in VBA, I sometimes get errors with FilterOn. For example, my code looks like this:

    strWhere = "[Billing Effective Date] <= #" & Me.txtAsOfDate & "#"

    'Filter Buyside CPU Appendix
    With .srptBuysideCPU.Report
        .Filter = strWhere
        .FilterOn = True
    End With

I then get a run-time error "Application-defined or object-defined error. But it only happens sometimes. If I comment out .FilterOn, it will work the first time, then not work after that. (I did dim strWhere earlier in the code)

Does anyone know why this happens?

 

[vbaInet]

It would be useful to mention in which even the code is placed?

But it only happens sometimes.

When??

I'm going to guess that you have it in the Current event or Load event of the form. You're getting the error because the subreport cannot be found. The subreport loads before the parent form.

Also make sure that the subform control name is not the same as the subform name.

Edit: I was mentioning form all through, I actually meant report.

 

[jsic1210]

The code is actually from a different form, and it's on click. First, the report is loaded, then the subreports are filtered. As far as when it works and when it doesn't, I don't know. I think the FilterOn usually works the first time then after that, it doesn't. The subreport does not have the same name as the control.

 

[vbaInet]

You are filtering a subreport from a form's click event?

 

[jsic1210]

yes, that's correct

 

[vbaInet]

Is the report in Report View? What version of Access are you using?

 

[jsic1210]

Yes, the report is in report view. I am using Access 2007.

 

[vbaInet]

What you need to do is tell me exactly when it fails. I need to know the steps you took.

 

[jsic1210]

Okay, here is my entire code, hope this helps. I put a comment in pointing out where it fails (close to the bottom, under "BUYSIDE CPU APPENDIX".

Private Sub cmdReport_Click()
Dim strWarning As String

'Warn if not last day of month
    strWarning = "Would you like to view report to end of month?"
    strMonthEndDate = DateSerial(Year(Me.txtAsOfDate), Month(Me.txtAsOfDate) + 1, 0)
    'Warning
        If Me.txtAsOfDate.Value <> strMonthEndDate Then
            If MsgBox(strWarning, vbYesNo, "Not End of Month") = vbYes Then
                Me.txtAsOfDate.Value = strMonthEndDate
            End If
        End If
'Change qryNewBusinessYTD
Dim strSQL As String
Dim qdf As QueryDef
Dim db As Database
    'Set SQL
    strSQL = "TRANSFORM Sum(qryPendingDetailByMonth.AbsoluteValue) AS SumOfAbsoluteValue "
    strSQL = strSQL & "SELECT qryPendingDetailByMonth.AddReduce "
    strSQL = strSQL & "FROM qryPendingDetailByMonth "
    strSQL = strSQL & "WHERE (((qryPendingDetailByMonth.[Billing Effective Date])<= #" & Me.txtAsOfDate & "#)) "
    strSQL = strSQL & "GROUP BY qryPendingDetailByMonth.AddReduce "
    strSQL = strSQL & "PIVOT qryPendingDetailByMonth.[Change Type]"
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryNewBusinessYTD")
    
    'Change WHERE Clause
    qdf.sql = strSQL
    Set qdf = Nothing
    Set db = Nothing
    
'Open Report
    DoCmd.OpenReport "rptRevenueTargets", acViewReport
    
'Reports
With Reports!rptRevenueTargets

'Label Month
    .labelMonth.Caption = MonthName(Month(Me.txtAsOfDate), False) & " " & Year(Me.txtAsOfDate)
    
'--------------------------------
'MONTHLY ADDS AND REDUCTIONS
'--------------------------------
Dim strWhere As String

    strWhere = "[Billing Effective Date] <= #" & Me.txtAsOfDate & "#"
    strWhere = strWhere & "AND [Billing Effective Date] >= #" & DateSerial(Year(Me.txtAsOfDate), Month(Me.txtAsOfDate), 1) & "#"
    
    'Set Focus to subreport
        .srptMonthlyAddsReductions.SetFocus

    With .srptMonthlyAddsReductions.Report
    'Filter Report
        .Filter = strWhere
        .FilterOn = True
    'Populate Label
        Reports!rptRevenueTargets.srptMonthlyAddsReductions.Report.txtDateRange.Caption = MonthName(Month(Me.txtAsOfDate), False) & " " & Year(Me.txtAsOfDate) & " Summary"
    End With
    
'--------------------------------
'NEW BUSINESS YTD
'--------------------------------
    strWhere = "[Billing Effective Date] <= #" & Me.txtAsOfDate & "#"
    With .srptCurrentYearNewBusiness.Report
    'Title of Chart
        .labelTitle.Caption = "New Business Run-Rate as of " & Me.txtAsOfDate
    'Filter Report
        .Filter = strWhere
        .FilterOn = True
    End With
        
'--------------------------------
'EMPLOYEE HEADCOUNT
'--------------------------------
'Need to update the query and report to be able to pick for a certain time period.  Background work must be done _
' in Employee DB
'--------------------------------
'BUDGET - REVENUE & EXPENSES
'--------------------------------
'--------------------------------
'FUTURES CALENDAR
'--------------------------------
Dim strBeginDate As String
Dim strEndDate As String
Dim strYearEnd As String
Dim strSummaryBegin As String
Dim strSummaryRange As String

    strBeginDate = "[Billing Effective Date] >= #" & Me.txtAsOfDate + 1 & "#"
    strEndDate = "[Billing Effective Date] < #" & DateAdd("m", 3, Me.txtAsOfDate + 1) & "#"
    strYearEnd = "[Billing Effective Date] <= #" & DateSerial(Year(Me.txtAsOfDate), 12, 31) & "#"
    strWhere = strBeginDate & " AND " & strEndDate & " AND " & strYearEnd
    strSummaryBegin = "[Billing Effective Date] >= #" & DateAdd("m", 3, Me.txtAsOfDate + 1) & "#"
    strSummaryRange = strSummaryBegin & " AND " & strYearEnd
        
    'Filter Futures Detail
    With .srptFuturesCalendarDetail.Report
        .Filter = strWhere
'        .FilterOn = True
    End With
    'Filter Futures Summary
    With .srptFuturesCalendarSummary.Report
        .Filter = strSummaryRange
        .FilterOn = True
    End With

'--------------------------------
'BUYSIDE APPENDIX
'--------------------------------
    strWhere = "[Category] = 'Buyside'"
    strWhere = strWhere & " AND [Billing Effective Date] <= #" & Me.txtAsOfDate & "#"

    'Filter Buyiside Appendix
    With .srptBuysideAppendix.Report
        .Filter = strWhere
        .FilterOn = True
    End With
    
'--------------------------------
'BUYSIDE CPU APPENDIX
'--------------------------------
    strWhere = "[Billing Effective Date] <= #" & Me.txtAsOfDate & "#"

    'Filter Buyside CPU Appendix
    With .srptBuysideCPU.Report
        .Filter = strWhere
'FAILS ON THE NEXT LINE (FilterOn=True)
        .FilterOn = True
    End With
        
'--------------------------------
'BROKER DEALER APPENDIX
'--------------------------------
    'Filter BD Appendix
    With .srptBDAppendix.Report
        .Filter = strWhere & " AND [Category] = 'BD'"
        .FilterOn = True
    End With
    
'--------------------------------
'INDEX APPENDIX
'--------------------------------
Dim strIndex As String

    strIndex = "left([Category],5) = 'Index' AND right([Category],10) <> 'Consulting'"
    
    'Filter Index Appendix
    With .srptIndexAppendix.Report
        .Filter = strWhere & " AND " & strIndex
        .FilterOn = True
    End With
    
'--------------------------------
'BUSINESS PARTNERS APPENDIX
'--------------------------------
Dim strPartners As String

    'Filter Business Partners Appendix
    With .srptBusinessPartnersAppendix.Report
        .Filter = strWhere & " AND [Category] = 'Business Partner'"
        .FilterOn = True
    End With

'--------------------------------
'BUSINESS CONSULTING
'--------------------------------
Dim strConsulting As String

    strConsulting = "right([Category],10) = 'Consulting' OR left([Category],10) = 'Consulting'"
    
    'Filter ConsultingIndex
    With .srptConsultingAppendix.Report
        .Filter = strWhere & " AND " & strConsulting
        .FilterOn = True
    End With
    
End With


'Print Preview
    DoCmd.OpenReport "rptRevenueTargets", acViewPreview
'Zoom to Fit
    DoCmd.RunCommand acCmdFitToWindow
'Close Filter
    DoCmd.Close acForm, "frmRevenueTargetsFilter"

End Sub

Thanks!

 

[vbaInet]

Ok, my last post was more about "when" as opposed to "where". That's what would be useful for now.

 

[jsic1210]

Okay, I'm not sure what you mean by 'when.' I click the button and the report begins to load, including subreports. It stops when it gets to that point. I can't figure out what I do differently that it works the first time I click the button, then each time after that, I get an error message.

 

[vbaInet]

Alright, upload a stripped down version of your db and I'll take a look.

 

[jsic1210]

For now, it's okay. I was more asking to see if anyone else had had this problem. It's working okay with the FilterOn function commented out. Thanks for your help, though.

 

[vbaInet]

It's odd that you're filtering a subreport from a Form so hardly would we come across this kind of scenario. If you have time upload the db (i.e. non-confidential data and relevant objects only) and I'll see what's going on.