Re: How do I assign a report to an object variable?

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 03/17/05 

Date: Thu, 17 Mar 2005 10:11:43 -0500

One, a report object cannot be set to a string...it can be set only to a
report object. This is why you're getting a data type mismatch.

Two, if the report is not already open, then you would use the
CurrentProject.AllReports object to get the report object.
    Set objReport = CurrentProject.AllReports("rptAudit" _
        & strTableNames(intCount))

Three, you cannot set the RecordSource of a report when it is closed. You
either must open the report in design view and change the RecordSource (can
do via code, but I wouldn't do it as part of a regularly run procedure); or
you must change the RecordSource in the Open event of the report. If you're
using ACCESS 2002 or 2003, you can pass the desired RecordSource string to
the report via the OpenArgs argument of the DoCmd.OpenReport action:

    DoCmd.OpenReport objReport.Name, acViewPreview, _
        , , , strQryName

Then the report's Open event procedure would need to be this:
    Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = Me.OpenArgs
    End Sub 

-- 
        Ken Snell
<MS ACCESS MVP>
"Simon Cleal" <SimonCleal@discussions.microsoft.com> wrote in message 
news:C94B6C13-F285-4727-804F-AE30BEE9262B@microsoft.com...
>I have 20 reports based on different tables in my db each of these reports
> can be run from 3 different queries depending on what selection criteria 
> the
> user needs.
>
> I want to use an object(report) variable to change the RecordSource 
> property
> and then print the report this is the code I have so far
>
> *************
> Select Case fraAudit.Value
>   Case 1 ' All
>      strAuditRepType = "All"
>   Case 2 ' By User Id
>      strAuditRepType = "UserID"
>   Case 3 ' By Staff Number
>      strAuditRepType = "StaffNo"
> End Select
>
> Dim strQryName As String
> Dim intCount As Integer
> Dim objReport As Report
>
> For intCount = 1 To 20
>   strQryName = "qryrptAudit" & strTableNames(intCount) & strAuditRepType
>   Set objReport = "rptAudit" & strTableNames(intCount) '<<<<<<<<<<<<<<
>   objReport.RecordSource = strQryName
>   DoCmd.OpenReport objReport.Name, acViewPreview
> Next intCount
>
> *********************
> My problem is the line I've indicated with <<<<<<<<<<<<<<< gives me a type
> mismatch error
>
> Can someone tell me what the correct syntax / method is for assigning an
> object varaible to a string?
>
> Thanks
>
> Simon
>