Output Report to PDF file

[jcc285]

Tearing my hair out!! I use the following code to Open a report, specifically, using the Where clause to select an individual record as:

DoCmd.OpenReport strReportName, acPreview, , strWhere

There is a sub-report in the output linked by Main and Child fields and it all works fine and I get the Report Preview, including correct sub-report, on screen

Now what I want to do is Output a report for each record as a PDF file. So next line of code:

DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileOut, True

However, when this is run I get the following error 'Runtime error 2501 - The OutputTo Action was cancelled'

I don't need to Preview the report so could omit this step but, in that case, I don't know how to pass the WHERE clause and just create the file. This did not ought to be difficult!!!



Full Test Code as below, hope it makes sense.

Private Sub Test()

Call Initialise_Variables ' Collects str_MemberNumber as Global variable

Dim strReportName As String
Dim strSQL As String
Dim strWhere As String
Dim strFileOut As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

strReportName = "rpt_Distribution_Areas_AllDistributors"
strSQL = "SELECT Membership_Number FROM tbl_Distribution_Volunteers"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
str_MemberNumber = rst.Fields("Membership_Number")
strWhere = "[Membership_Number]=""" & str_MemberNumber & """"
strFileOut = "C:\Temp\" & str_MembershipNumber & ".lst"

DoCmd.OpenReport strReportName, acPreview, , strWhere
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strFileOut, True 'This line throws Runtime error 2501

rst.MoveNext

Loop 'Breakpoint inserted here for testing so only first report generated.

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

[ranman256]

Instead of using record set code, use a form.
a list box has your changing params. (Members)
the report qry reads the list box to pull the member via param.
...where [member] = forms!frmRpt!lstMem

the code scans the list box changing to each value,
the query sees the value
the report prints only that member.

i use this method for my PDF reports. It works.

[Dave D]

Try this; very similar to yours, but has slight modifications.

I use this to bring up, as hidden, the report

DoCmd.OpenReport "rptName", acViewPreview, , strWhere, acHidden

I put this code in a function within a module (modReports). The function takes the rptName as a string parameter. I can then use this same function to manage how different reports are displayed or stored by using a Select statement.

DoCmd.OutputTo acOutputReport, rptName, "*.pdf", strPath & "myFile.pdf", True

Be sure to set the variable, strPath, to a valid file directory. I use this:

strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") + "\"

[jcc285]

Yes, OK. I've moved to a List Box Selection on a Form. Then re-coded all to either display the report for viewing or Output to PDF File.

Thanks for your suggestion

Jcc285

[jcc285]

Thanks for your input but I have gone down the route suggested by ranman256.