Access 2003 - Create PDF batch filenames from Access report

[JimG]

I have an Access report that creates a two page report for each record
selected. I need to output each two page report as a PDF print file with a
filename selected from feilds from the records selected for the report. If I
print one customer record at a time I can manually name the pdf file with the
customer name and customer number.

I have thousands of these reports to create so I need a batch method which
will start a new print output for each customer record selected with the
customer name and customer number as the PDF filename.

The default print filename is the report name or caption property. I tried
setting the caption property equeal to a field name ( ie. "=
[CustomerName]"). This changed the report name to "= [CustomerName]".

Help .... any ideas?

Jim

 

[Paulius]

I have an Access report that creates a two page report for each record
selected. I need to output each two page report as a PDF print file with a
filename selected from feilds from the records selected for the report. If I
print one customer record at a time I can manually name the pdf file with the
customer name and customer number.

I have thousands of these reports to create so I need a batch method which
will start a new print output for each customer record selected with the
customer name and customer number as the PDF filename.

The default print filename is the report name or caption property. I tried
setting the caption property equeal to a field name ( ie. "=
[CustomerName]"). This changed the report name to "= [CustomerName]".

Help .... any ideas?

Jim

you can try
Private Sub Report_Open(Cancel As Integer)
Report_ReportName.Caption = [CustomerName]
End Sub

but this doesn't work if you make a print button on a form which prints this
report. I tried all events (report/header onprint, format, etc events) but
when i try to print the caption won't change. I wonder how could i fix this..

 

[strive4peace]

Hi Jim,

If what Paulius gave you doesn't work, here is a generic routine to go
into the design view of a report and change something before it is
output... you can modify it to change caption -- it would be best to
pass whatever you want the caption to be


'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub SetRecordSource(_
ByVal pReportName As String, _
ByVal pSQL As String)

' written by Crystal
' strive4peace2006 at yahoo.com

' PARAMETERS:
' pReportName is the name of your report
' pSQL is an SQL string or tablename or queryname

' USEAGE:
' SetRecordSource "MyReportname","QueryName"
' SetRecordSource "MyAppointments", _
"SELECT * FROM Addresses WHERE City='Denver';"

On Error GoTo err_proc
Dim rpt As Report

'you can remove these lines once everything works ok
debug.print pReportName & " --> "
debug.print pSQL


'you may want to comment this out... not sure it is needed
DoCmd.Echo False

'open as hidden if you don't want to see what is happening
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
rpt.RecordSource = pSQL
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
DoCmd.Echo True

'if you want to open report now, remove comment
'DoCmd.OpenReport pReportName, acViewPreview

'if you want to use the WHERE parameter
'to further filter the report
'DoCmd.OpenReport pReportName, _
acPreview, , "condition"

Exit Sub

err_proc:

MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " SetRecordSource"

DoCmd.Echo True
'press F8 to step thru lines and fix problem
Stop
Resume

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

if you want to change the filter...

rpt.Filter = pFilter

where pFilter is a passed string

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

I have an Access report that creates a two page report for each record
selected. I need to output each two page report as a PDF print file with a
filename selected from feilds from the records selected for the report. If I
print one customer record at a time I can manually name the pdf file with the
customer name and customer number.

I have thousands of these reports to create so I need a batch method which
will start a new print output for each customer record selected with the
customer name and customer number as the PDF filename.

The default print filename is the report name or caption property. I tried
setting the caption property equeal to a field name ( ie. "=
[CustomerName]"). This changed the report name to "= [CustomerName]".

Help .... any ideas?

Jim

you can try
Private Sub Report_Open(Cancel As Integer)
Report_ReportName.Caption = [CustomerName]
End Sub

but this doesn't work if you make a print button on a form which prints this
report. I tried all events (report/header onprint, format, etc events) but
when i try to print the caption won't change. I wonder how could i fix this..