Filter subreport with filter of main report

[ValS]

I have a main report where each detail line contains a country. I have a subreport in the main report footer which shows a total count (plus some other stuff) by country, plus a grand total. I run the main report from a form and apply a Where clause filter at that time. I would also like to apply the same filter to the subreport, but haven't been able to figure out how.

Any suggestions?

THanks,
Val

 

[Lozere]

In the Open event procedure of you Report, put the follwing code

>>Me.SubReport_Name.Report.Filter=Me.Filter
>>Me.SubReport_Name.Report.FilterOn=True

An alternative is to put
>>Me.Filter=Parent.Filter
>>Me.FilterOn=True
in the Open event procedure of your SubReport

 

[ValS]

Thanks Lozere - I tried both with the following results...

When I put
>>Me.Filter=Parent.Filter
>>Me.FilterOn=True
in the OnOpen event of the subreport I get an error 2101 "The setting you entered isn't valid for this property". I get the same error even if I comment out the first line and just have the Me.FilterOn = True line.

When I put Me.TotalsSubreport.Report.Filter = Me.Filter in the onOpen event of the report I get the error 2455 "You entered an expression that has an invalid reference to the property form/report". One question on this one... (I'm never totally sure how to reference subreports) the subreport control is called TotalsSubreport and the name of the subreport itself (i.e. if I were to open it on its own) is 'rptTotalsAbandoned'. Have I referenced it properly?

thanks,
Val

 

[Lozere]

I have not checked yet:
If you are hot on the issue, try to put the code in the Activate event procedure, may be the Open event comes too soon

 

[ValS]

When I try it in onactivate, it doesn't give an error, just doesn't work... (that was the me.filter option)

 

[Lozere]

Sorry to leave you. It was night this side of the Ocean.

I suspect that the problem is in the sequence of Open/Activate events between report and subreport.

What I suggest you is:

To create a Public string variable (in some independant module), say <GV_Country_Clause>

When, in your form, your create the string for the Where clause of your report, put it in this variable BEFORE opening the report.

In the OPEN event procedure of you Sub-Report, put the following code

>>Me.Filter = GV_Country_Clause
>>Me.FilterOn = True

 

[ValS]

Hi Lozere - thanks again. It is actually as if it is not calling the onOpen for the subreport properly, rather then it not begin able to have the correct value. If I use
Me.Filter = &quot;[country] = 'us'&quot;
Me.FilterOn = True
in the subreport, if I open the subreport directly it filters ok, but if I open the main report, it gives me the
error 2101 &quot;The setting you entered isn't valid for this property&quot;. error when it gets to the subreport.

 

[Chewypoo]

Vallys,

U can somewhat be confusing of de report when u used de 'Me' when it doznt kno what report u is talking about (it must be thinking de 'me' is could be maybe de main report or maybe it must be thinking de 'me' is de subrebport. Youz need to speify exactically what you want. So, whenz you 'me' instead youz put de [report]![&quot;subreport&quot;].filteron = True. Dis must work. If no den rite back an I will help youz more.

godluck!

 

[Chewypoo]

Vallys,

Waz my helpfulness very helpfuly for youz! or do youz needing some more helpsies? i no of another soluton i think, but youz shood let me no if dis was helpitious.

pleez let me no!

 

[SockMonkey]

Val

I have experienced this problem before.
You can not use the Filter and FilterOn events on a subreport.

I have created a complex report manager where I can select a report from a group and then depending on the report
filter on fields from both the main & sub reports.

I found the easiest way to make this work was to use code to temporarily change the Sub-Reports RecordSource SQL string and add WHERE clause to the end of it with the filter criteria.
Then the DoCmd.OpenReport
Then restore the Sub-reports original RecordSource

Dim strReportName As String, strSubReportName As String
Dim strReportWhere As String, strReportSubWhere As String Dim strSubReportQuery As String
Dim strSQL As String
Dim qdf As DAO.QueryDef

strReportName = Me![Selected Report]
strReportWhere = ReportWhere()
strSubReportName = Me![Selected Report].Column(4)

If strSubReportQuery = &quot;&quot; Then
Else
strReportSubWhere = ReportSubWhere()
End If

If strReportSubWhere = &quot;&quot; Then
DoCmd.OpenReport strReportName,intPrintMode,,strReportWhere
Else
strSubReportQuery = Me![Selected report].Column(5)
Set qdf = currentDB().QueryDefs(strSubReportQuery)
strSQL = qdf.SQL
qdf.SQL = Left(qdf.SQL,Len(qdf.SQL)-3)& strReportSubWhere
DoCmd.OpenReport strReportName,,,strReportWhere
qdf.SQL = strSQL
End If

I hope this helps.

Robert

 

[Chewypoo]

Vallys,

Did we be helping you with our helpfully helpfull ansers? Kan dere not be something we can never help you with some more?? Or did little brains of ourz not see de happier side of thing? Plees let us no!

Take scare!

 

[demoman]

Two thumbs up! I have not been posting on this thread, but I have been working the same problem. Robert - your solution of changing the query definition was the best way to work the problem.

I can see I need to spend more time studying Access objects. Thanks again!

Steve

 

[Oremoh]

I tried several of this possibilities (not the longer one) and some others that I found around and none of them worked. Finally I got to this pretty simple solution from the MS Knowledge Base:
Instead of trying to change the filter on the Open event of the subform you can get the same result changing the recordsource and that is feasible with some code like this:

Private Sub Report_Open(Cancel As Integer)
Static intCallCount As Long

If intCallCount = 0 Then Me.RecordSource = &quot;SELECT * FROM tTable WHERE (&quot; & Parent.Filter & &quot;&quot;
intCallCount = intCallCount + 1
End Sub

The SQL inserted as recordsource should come from the query that feeds the main report. Just make sure that you insert the WHERE clause in the proper place and that you set intCallCount as static because the Open event is called more than once.

Enjoy

 

[corobori]

Robert,

You made my day ! Great idea, it work's just fine.
For those who wants to use it be aware that with print preview you have to let the query modified for printing otherwise it won't work. I just change Robert's code to use only the select part of the query in there was already a WHERE clause in it.

Jean-Luc jeanluc@corobori.com