How do I assign a report to an object variable?

[Simon Cleal]

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

 

[Ken Snell [MVP]]

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

 

[JaRa]

there are a few options :

1) you give an argument when you open the report and at Report_Open you set
the proper recordsource
2) you open the report in design, change the recordsource and then print it
dim rep as Report
dim strName as string
strName="rptAudit" & strTableNames(intCount)
DoCmd.Report strName ,acViewDesign
set rep=Reports(strName)
rep.RecordSource=strQryName
DoCmd.Close acReport ,strName,acSaveYes
DoCmd.OpenReport strName, acViewPreview

I would go for solution 1

- Raoul

 

[Simon Cleal]

Thanks a lot for your help Raoul & Ken