Applying a Filter to a Main report and Subreport 2

[rbrychckn]

I have VB code that works to filter a main report running off a query. My primary goal is to do some counts on the main report and throw them into the report header, via a subreport. I've created a subreport running off the same query the main report is running off of, but only made the counts visible, and added them to the main report. Now I just need correct code to pass the filter string to both the main report and subreport.

This is my filter-building code:
strFilter = "([Cases.Date_of_Death] " & strStartDate & " AND [Cases.Date_of_Death] " & strEndDate & ") OR ([Cases.Discharge_Date] " & strStartDate & " AND [Cases.Discharge_Date] " & strEndDate & ")"

This is the code passing the filter to the main report (which works):
Reports(strReport).Filter = strFilter
Reports(strReport).FilterOn = True

This is my faulty code that is attempting to pass the same filter string to the subreport:
Reports(strReport).sbrptProcCompDeath.Report!Filter = strFilter
Reports(strReport).sbrptProcCompDeath.Report!FilterOn = True

sbrptProcCompDeath is the name of the report AND the name of the control. strReport is being chosen by a radio button and stored.

Any help is appreciated

 

[MajP]

Why did you use a bang operator before "Filter"? Filter is a property not a named object in a collection.
collectionName!objectInTheCollection
object.property

Report.filter = strFilter
report.filteron = true

 

[rbrychckn]

So, the sbrpt is a control or object in the main report. Filter then is a property of the report, though. So after calling the subreport control, I'd need to reference the report itself, right? Therefore:

Reports(strReport).sbrptProcCompDeath.Filter = strFilter
Reports(strReport).sbrptProcCompDeath.FilterOn = True

This works now - that is the count is accurate, but I still get an error "The setting you entered isn't valid for this property.

 

[MajP]

It looked to me like you almost had it originally. Should be like this using the long hand notation

Reports("strRptName").controls("subRptControlName").report.filter
in your case
Reports(strReport).controls("sbrptProcCompDeath").report.filter

I could shorten the notation above, but this shows the chain
Reports collection, report name, the controls collection on the report, the subreport control, the report within the sub control, and the reports filter.

hope this helps

 

[rbrychckn]

This is helpful in understanding the syntax.

I am still getting an error though. The odd part is that the filter is clearly being applied. I'm getting:

Run-time error '2101'
The setting you entered isn't valid for this property.

and it's highlighting the reference to the filter,
Reports!rptProcCompDeath.sbrptProcCompDeath.Report.Filter = strFilter

 

[MajP]

Does this work?
Reports!rptProcCompDeath.controls("sbrptProcCompDeath").Report.Filter = strFilter

 

[rbrychckn]

No, I still get the error with the full path.

 

[MajP]

I think I gave out some bad scoop. I actually tried to do this, but could not get it to work. I Read on the site for some suggestions, and it seems that once a report is loaded as a sub report you can no longer work with its filter property. This may make sense, since behind the scenes the sub form or sub report must filter on the link items every oncurrent event. I tried a bunch of combinations of events and never got it to work. I have seen the open args used as a solution. I removed the source object from the main forms subreport control. I got the main's filter, opened the report (subreport) in design view, set the filter = to the main's, closed it the report (sub report), then set the source object of the subreport control.

Private Sub Report_Open(Cancel As Integer)
  Dim strFilter As String
  Dim subRpt As Report
  Dim subCntrl As SubReport
  
  strFilter = "strShipper = 'Taxi'"
  Me.filter = strFilter
  Me.FilterOn = True
  DoCmd.OpenReport "rptTabShipper", acViewDesign
  Set subRpt = Reports("rptTabShipper")
  subRpt.filter = Me.filter
  subRpt.FilterOn = True
  DoCmd.SetWarnings (False)
  DoCmd.Close acReport, subRpt.Name
  Me.subRptCntrl.SourceObject = "rptTabShipper"
End Sub

 

[NorthNone]

MajP- Great post at the end. I got it all to work except the very last line. I don't see a "subRptCntrl" dimmed, only a subCntrl.
But a star for your work anyway!


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[rbrychckn]

I actually finally got around to fixing this report bug. I agree with NorthNone, the last line does not seem to work (Me.subRptCntrl is not found). Any editions on how to make this work? Thanks a bunch.

Tom