One Report....different queries??

[carmo]

Hi There,
I have a report that is used in different situations. Each situation uses a different query as its recordsource. Right now, I have 3 copies of the same reports since each version is based on a different recordsource query. How can I create one report and dynamically change the recordsource using VBA?? Thanks in advance.

 

[carmo]

Actually...I figured it out. In order to change the record source, the following steps must be taken in VBA:

- open report in design view
- change recordsource for report
- save report
- close report
- open report

Here is my code:

Dim rpt As Report
Dim rptName As String
rptName = "rpt_profile"
DoCmd.OpenReport "rpt_profile", acViewDesign
Set rpt = Reports(rptName)
rpt.RecordSource = "qry_profile_single"
DoCmd.Save acReport, "rpt_profile"
DoCmd.Close acReport, "rpt_profile"
DoCmd.OpenReport "rpt_profile", acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow

 

[sxschech]

Another method I have used is to create a generic query name and have all the reports refer to this generic query in that way any report (assuming fields match on the report) will run with any query after the query has been copied to the name of the generic query.

Here is a code snippet which used a combo box to select the query to be copied to the generic name query (In this case the generic name is Employees):

DoCmd.SetWarnings False
DoCmd.CopyObject , "Employees", acQuery, Me!cboQueryName
DoCmd.SetWarnings True