report container issue - I know there's going to be an easy answer

[Guest]

Please could someone tell me what I'm doing wrong here. It keeps telling me
that the "For" statement can't handle a report object...?

Public Function SetRptPaper()
Dim ctr As Container, rpt As Report, db As Database
Set db = CurrentDb
Set ctr = db.Containers!Reports
For Each rpt In ctr
DoCmd.OpenReport rpt, A_DESIGN
If Not Reports(rpt).Printer.PaperSize <> acPRPSA4 Then
SetPrinter rpt.Name
DoCmd.Close A_REPORT, rpt, acSaveYes
End If
Next
End Function

Please, any help would be appreciated

 

[Douglas J Steele]

Container objects don't contain Reports (nor Forms nor Queries). They
contain Documents.

Change rpt As Report to rpt As Document

Once you do that, you'll find that OpenReport can't open a document object
(but then it wouldn't have been able to open a report object either: it's
expecting a report Name, not an object). That means your final code needs to
look something like:


Public Function SetRptPaper()
Dim ctr As Container, rpt As Document, db As Database
Set db = CurrentDb
Set ctr = db.Containers!Reports
For Each rpt In ctr
DoCmd.OpenReport rpt.Name, A_DESIGN
If Not Reports(rpt.Name).Printer.PaperSize <> acPRPSA4 Then
SetPrinter rpt.Name
DoCmd.Close A_REPORT, rpt.Name, acSaveYes
End If
Next
End Function

Having said that, though, I have no idea what the line of code

SetPrinter rpt.Name

is supposed to be doing.

 

[Guest]

Hi Douglas,

Thanks for the reply. However, VBA still gives me the following error when
it hits the "For each rpt in ctr":
"Operation is not supported for this type of object."

The line: SetPrinter rpt.name is just a function call.
Thanks

 

[Guest]

Hi Douglas,
I got it with this, thanks for the effort though, much appreciated.

Public Function SetRptPaper()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
For Each obj In dbs.AllReports
DoCmd.OpenReport obj.Name, A_DESIGN, , , acHidden
If Not Reports(obj.Name).Printer.PaperSize = acPRPSA4 Then
Reports(obj.Name).Printer.PaperSize = acPRPSA4
DoCmd.Close A_REPORT, obj.Name, acSaveYes
Else
DoCmd.Close A_REPORT, obj.Name, acSaveNo
End If
Next obj
End Function