Using one report as 3 diffferent subreports

[sync123]

Hi,

I created a report which is based on a cross tab query and it is based on one particular col in my table.

Now I have 2 other reports which are exactly the same but just picking up a dif col from the table.So I just used the same report and change my record source depending on the report I want to generate.

report 1 :based on month_Actual col
report 2: based on month_prev col
report 3: based on month_budget col

Now the thing is that I need to show these 3 reports in one report.

This is what I did:

Lets call the General report whos record source I would change previously "report_all"

I created a blank report and added report_all three times as a subreport.
Now what I would like to do is make the first subreport point to query that picks up month actual.The second to point to query that picks up last year col and the third to point to budget col .

I tried right clicking the subreports but the event property option is dimmed.How can I change the record source of these subreports??

Really appreciate it if someone can anyone help me out on this.

Thanks.
sync123

 

[dhookom]

Can you place a value in the main report and then use Link Master/Child properties to limit the records displayed in each copy of the subreport?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sync123]

Hi Duane,

Just recapping my problem : I have imported the same report 3 times as a subreport.Now I was thinking of changing the record source of each of these subreports.

-What I found is that in most cases you would just go into the subreport design view and change its record source there.But I won't be able to do that, since that would change the record source of all 3 copies of the subreport.

The thing is that the 3 queries that I want my subreports to be based on are exactly the same except the row and col intersection value (cross tab query) is different.

Is it possible to change the recordsource of each subreport in the on open event of the main report.
something like

Public Sub on_open(..) MAIN REPORT
Me.subrpt1.Report.recordSource="qry1"
Me.subrpt2.Report.recordSource="qry2"
Me.subrpt3.Report.recordSource="qry3"

OR do you see a problem with this.

-I am not sure how I could use the link master/child property.My subreports should pivot on month_actual col ,month_prev col ,month_budget col respectively.

Your help is much appreciated.
Thanks!

-sync123

 

[dhookom]

It is difficult to change the record source of a subreport. If I recall correctly, it must be done in the On Open event of the subreport. If more than one instance of the subreport opens, you will get an error in the later instances unless you disable the code that sets the record source.

It might help to understand your subreport recordsource SQL and the main report's record source SQL.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.