Access Reports - Pass MainReport Filter to SubReport 1

[sds568219]

I have a main form with several (8) combo boxes, which after making selections creates the strWhere clause for the main report. I also have two subReports that are in the MainReport used for providing totals (take to long to explain why). Anyhow the MainReport works great, the first subReport works correctly (only because I can make a link for Master/Child). However, the second subReport really needs the Main Reports Filter to total correctly. Can't use the Master/Child because there are exceptions that must be taken into consideration. I've tried passing the filter to the subReport from both the main form and the main report but get a run-time error '2101': The setting you entered isn't valid for this property.

Any suggestions?

Steve

 

[Remou]

There are problems with filters for subreports, perhaps a work-around would suit? This is intended for the open event of the subreport:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "Select * From tblT Where " & Me.Parent.Filter
End Sub

 

[sds568219]

Remou,
Thanks for the suggestion but when I try to change the record source I get the run-time error 2191 - You can't set the Record Source property in print preview or after printing has started.

The subreport runs find on its own by changing the code to what you suggested, but it has something to do with it being a subform.

Steve

 

[Remou]

Which event are you using to change the record source?

 

[sds568219]

Remou,

Here is the code for on open event for the subreport. As you can see I've tried several different ways to pass the filter to the subreport


Private Sub Report_Open(Cancel As Integer)
Dim strFilter As String
'strFilter = Reports!CalledUMDMainReport.Filter
'Me.Filter = strFilter
'Me.Filter = Forms!frmUMD.txtSavedUMD

Me.RecordSource = "SELECT * FROM CalledUMD_PAS WHERE " & Reports!CalledUMDMainReport.Filter

End Sub

 

[Remou]

I'm lost, I afraid. I tested the above with the subreport in a variety of report sections (detail, report footer, etc) and it worked for me. I am using Access 2000.

 

[Remou]

It looks as if you will have to open the subreport in design view, set the filter, save, and then run the report.

 

[sds568219]

Remou,

Thanks for taking the time to help and offer suggestions.

I'm using Access 2003. We're you putting the filter code on the subreport, or having the mainreport pass the filter to the subreport?

I've heard of opening the report in design,update the filter, save, and close it, but not familiar with the process. Do you have an example of the code.

 

[Remou]

The sample code I posted was for the Open event of the subreport.

To open in design view, you would say something like:

DoCmd.OpenReport "Subreport Name", acViewDesign
Reports("Subreport Name").Filter="FieldA='B'"
Reports("Subreport Name").FilterOn=True
DoCmd.Close acReport,"Subreport Name",acSaveYes

The above is typed, not tested.

 

[sds568219]

Remou,

That did the trick. Not exactly my preferred method but it will work until I can come up with something else.

Thanks again for the assistance.

Steve