Need to set the DataSource property of a report on demand (1 Viewer)

[accesswatcher]

A DB in Access97 (Office97)has several reports. They all have the same data from the same table, and same format. But, each report derives the data from corresponding queries.

Origianlly I made several reprots by copying the first one and changed the "DataSource" and "Caption" properties manually for each report. Tehy work just fine.

All the reports are started by command button controls on a main user interface.

Now, I want to use code to set the "Caption" and "DataSource" properties of ONE SINGLE report to derive the several varaitions (based on diferent queries) to avoid having to keep all the different copies of the same report just with the difference of caption and datasource.. I want to be able to set those two properties while the user clicks the command button to open the report.

The Caption setting seem to work fine, but the DataSource property setting wont work.
Any Ideas? I would greatly appreciate any and all help.

 

[Travis]

What I do for this (one report many different data sources) is:

1. Remove the Record Source from the report property sheet leaving it blank.

2. On the on open event of the report (since reports don't have an OpenArgs like forms) I place this code:

Select Case Screen.ActiveControl.Name 'Gets the name of the last control or in this case button that executed the report
Case "Button1"
me.RecordSource = "query1"
me.lblCaption = "Query One"
Case "Button2"
me.RecordSource = "query2"
me.lblCaption = "Query Two"
Case "Button3"
me.RecordSource = "query3"
me.lblCaption = "Query Three"
Case "Button4"
me.RecordSource = "query4"
me.lblCaption = "Query Four"
End Select

Note: On reports in order for this to work the RecordSource Property must be blank.

 

[accesswatcher]

Thanks Travis,
Works good.
Now, I could get rid of 16 reports.
And making design changes could happen in just one.
Thanks again.
---------------------------------
Now there is a new one:

Since it relates to "FORMS" I am posting it in the Forms section.

 

[Laftrip]

I have a similar problem, but with a twist.

I have 11 subreports on a report that are basically identical except for their names and the recordsource. My report is perfect except for the fact that I have 11 almost identical subreports. I also have another report with another 11 subreports, so I would like to cut-out 20 reports if possible. It's very tidious work to do a simple change.

I would prefer to have 1 subreport and set the recordsource right before each instance of that subreport prints. In the design view, I have placed the subreport in the different places where it needs to be, but I can only access the properties and events of one instance of the subreport. The subreport controls (on the main report) that actually holds the subreport (I hope you know what I mean by that) has no events.

How can I do this?

Is the problem explained clearly?

I've tried putting this code in the Main Report in the "Details Format" and "Report Open" event with no success

Me!ReleaseReport_libnsrceo.RecordSource = "SELECT * FROM sqlReleaseReport_Generic WHERE [sqlReleaseReport_Generic].[ciuType]='libnsrceo';"

ReleaseReport_libnsrceo is an example of one of the 11 subreport controls that holds the actual subreport called ReleaseReport_Generic.