save report as pdf 1

[humpydumpy2000]

hi all,

I would like to save an unbound report as pdf, the problem is access is bit confused what I did was to make a command button in a form and embeded the following code in an onClick event:



Dim wsSQL As String
wsSQL = "SELECT * from tblsubject;"
Set db = CurrentDb
Set rs = db.OpenRecordset(wsSQL, dbOpenDynaset, dbSeeChanges)


Reports!rpt!subject = rs.Fields("subject").Value
rs.Close
db.Close

DoCmd.OutputTo acOutputReport, "rpt", "PDFFormat(*.pdf)", "report.pdf", True, "", 0, acExportQualityPrint


access tells me its error 2451: report name rpt doesnt exist. Any ideas please?

 

[MajP]

Is your report actually called "Rpt"?

 

[humpydumpy2000]

yes

 

[MajP]

your problem is here
Reports!rpt!subject = rs.Fields("subject").Value

The reports collection like the forms collection only contains the open forms/report

Since the report is not open that line of code is looking for a report in the reports collection, but there is none. Have to open it first.

 

[MajP]

BTW you can open it hidden if you do not want to show it.

 

[humpydumpy2000]

ok my point is I dont want to open the report I simply want to export it to pdf? Isnt it possible?

 

[MajP]

Yes you can, but as I explained that is not your problem. Your problem is that you are trying to set a value on a closed form first. That you can not do.
Reports!rpt!subject = rs.Fields("subject").Value

If you want to do that open it hidden

Dim wsSQL As String

wsSQL = "SELECT * from tblsubject;"
Set rs = currentdb.OpenRecordset(wsSQL, dbOpenDynaset, dbSeeChanges)
docmd.openreport "rpt",acpreview,,,achidden
Reports!rpt!subject = rs!subject
DoCmd.OutputTo acOutputReport, "rpt", "PDFFormat(*.pdf)", "report.pdf", True, "", 0, acExportQualityPrint
docmd.close acreport,"rpt"
rs.Close

If you truly wanted it closed, you would have to open it in design view, set the property of the subject field as
Reports!rpt!subject = "= '" & rs!subject & "'"
then save the report and close the report.
Too much work, for what purpose?

 

[humpydumpy2000]

thanks a lot!