I have a report that does exactly what I want it to do but I want run that report on different recordsets. I could make a copy of the report and make the SQL of each copy specific to a recordset, but it doesn't make much sense to have multiple copies of the same report as I'm sure to modify it later on.

So, is it possible to call the report from a form and pass it a different SQL command depending on which recordset is required? Is there a better way to do it?

---

Reply from Richard Rost:

Robert, unfortunately you can't change the RecordSource of a report once the report is open for printing or preview. However, there is a hack you can use to do something like this.

The trick is to open the report in DESIGN mode, make the change, and then immediately open it in PREVIEW mode. The user won't know the difference. Just make a form with two buttons on it. Each one can open the report and specify a different table for the data:

    DoCmd.OpenReport "MyReport", acViewDesign
    Reports!MyReport.RecordSource = "SELECT * FROM Table1"
    DoCmd.OpenReport "MyReport", acViewPreview

The only down side is that the user is going to see a "save changes to design of report?" when they close the report. If that's not a big deal, then this will work for you.

There are BETTER solutions, but they involve more work. For example, you could have a temporary table that is the recordsource for the report, then just run a delete query followed by an append query to fill the data into that table, then open the report.

There are a million ways to do everything in Access. Hope this helps.