Preview Same Report With Different Where Clause 1

[JohnLowell]

I would like to be able to preview the same report multiple times with different Where clauses used in the OpenReport command.

Does anyone know how to create an instance of a Report so that you can have the same report previewed multiple times?

I know you need to create a variable (Dim rpt As Report), but I'm lost as to what you are supposed to do with it.

Thanks in advance....

 

[ByteMyzer]

In a new Module, paste the following code:

[COLOR=blue]Public[/color] rptCol [COLOR=blue]As New[/color] Collection
[COLOR=blue]Public[/color] rptTemp [COLOR=blue]As[/color] Report
[COLOR=blue]Public[/color] rptSQL [COLOR=blue]As String[/color]

[COLOR=blue]Function[/color] ReportMulti(txtSQL [COLOR=blue]As String[/color])

    rptSQL = txtSQL
    [COLOR=blue]Set[/color] rptTemp = [COLOR=blue]New[/color] Report_Report1
[COLOR=green]        ' Substitute Report_Report with the actual object reference to
        ' your report.  For example, if your report is named [b]MyReport[/b],
        ' the above statement would be:
        ' [b]Set rptTemp = New Report_MyReport[/b][/color]

    rptTemp.Visible = [COLOR=blue]True[/color]
    rptCol.Add rptTemp, [COLOR=blue]CStr[/color](rptTemp.hwnd)
    [COLOR=blue]Set[/color] rptTemp = [COLOR=blue]Nothing[/color]

[COLOR=blue]End Function[/color]

Save the Module as ReportMulti Module.

Open the report in Design View. In the report's Code Module, paste the following code:

[COLOR=blue]Private Sub[/color] Report_Close()
[COLOR=blue]On Error Resume Next[/color]
    rptCol.Remove [COLOR=blue]CStr[/color](Me.hwnd)
[COLOR=blue]End Sub[/color]

[COLOR=blue]Private Sub[/color] Report_Open(Cancel [COLOR=blue]As Integer[/color])
    Me.RecordSource = rptSQL
[COLOR=blue]End Sub[/color]

Save the report's new settings.


In the Debug Window, type:
[tt] ReportMulti "SELECT * FROM

;"[/tt]
(or whatever SQL statement you choose for your report's RecordSource)

...and press Enter.

The report should appear on your screen. In the Debug Window (without closing the report instance currently on your screen), repeat the command, with a different WHERE clause in the SQL statement, and press Enter. You should see another instance of the same report, this time with a different set of records.

 

[JohnLowell]

Thanks for the code. I will try this tomorrow.

Looking at the code, the only problem I have is with statement:

Set rptTemp = New Report_Report1

I need to make it generic enough to work with any report that is selected from a list.
Is there a way to set rptTemp without a specific reference (ie. Report("Report1")). Can I build the value (with a variable)when setting rptTemp?

Thanks...

 

[ByteMyzer]

Unfortunately, no, this statement cannot be executed with a variable reference (i.e. Set rptTemp = New Reports("Report1").

You can, however, modify the above ReportMulti Code to something like this:

[COLOR=blue]Function[/color] ReportMulti(rptName [COLOR=blue]As String[/color], txtSQL [COLOR=blue]As String[/color])

    rptSQL = txtSQL

    [COLOR=blue]Select Case[/color] rptName
        [COLOR=blue]Case[/color] "Report1"
            [COLOR=blue]Set[/color] rptTemp = [COLOR=blue]New[/color] Report_Report1
        [COLOR=blue]Case[/color] "Report2"
            [COLOR=blue]Set[/color] rptTemp = [COLOR=blue]New[/color] Report_Report2
        [COLOR=blue]Case[/color] "Report3"
            [COLOR=blue]Set[/color] rptTemp = [COLOR=blue]New[/color] Report_Report3
[COLOR=green]        ' ...and so on, for all reports you wish to do this with[/color]
    [COLOR=blue]End Select[/color]

    rptTemp.Visible = [COLOR=blue]True[/color]
    rptCol.Add rptTemp, [COLOR=blue]CStr[/color](rptTemp.hwnd)
    [COLOR=blue]Set[/color] rptTemp = [COLOR=blue]Nothing[/color]

[COLOR=blue]End Function[/color]

Also, make certain that each of the reports that this is done with includes the Report Open and Close events code to set the record source on Open and to remove the report instance from the Collection on Close.

 

[JohnLowell]

I didn't think you could set it...

Thanks again