Print reports to PDF using a value from a table as a file name

[UongSaki]

I was able to adapt some codes from Roger Carlson (

http://www.rogersaccesslibrary.com/)

to make Access prints a report based on value in a table. Now my boss wants to print it to a PDF instead. How do I modify the procedure below so that Access prints to PDF using using value from a table as a file name?

I found Save A Report As PDF FAQ703-2533 with the related post #705-816439 and tried to make it works for my needs but so far without luck; I do have Adobe Acrobat version 5.0 installed on my PC. It sounds relative easy but I just could not make it work, please help.

'Adapted from PrintingSpecificReports2k database
'from Roger Carlson ([URL unfurl="true"]http://www.rogersaccesslibrary.com/)[/URL]

'===============Print Cost Centers======================

Sub PrintCostCenters()

'*** error trapping - execution goes to bottom on error
On Error GoTo Err_PrintCostCenters

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("TblCC", dbOpenSnapshot)

'*** the first record in the Criteria table ***
rsCriteria.MoveFirst

'*** loop to move through the records in Criteria table
Do Until rsCriteria.EOF
    '*** create the Select query based on
    '    the first record in the Criteria table
    
    
    strSQL = "SELECT * FROM TblFinalData WHERE "
    strSQL = strSQL & "[CC] = '" & rsCriteria![PkCC] & "'"
    
    
    'MsgBox strSQL
    '*** delete the previous query
    db.QueryDefs.Delete "vbPrintReportQuery"
    Set qdf = db.CreateQueryDef("vbPrintReportQuery", strSQL)
    
    DoCmd.OpenReport "Variance - Cost Centers Printing", acNormal

    rsCriteria!Emailed = True
    '*** goto the next record in Criteria table
    rsCriteria.MoveNext

Loop

rsCriteria.Close
 
Exit_PrintCostCenters:
    Exit Sub

Err_PrintCostCenters:  '*** if there  is an error, execution goes here
    '*** if the error is the table or query missing (3265)
    '    then skip the delete line and resume on the next line
    '    Error 2501 notifies you that the SendObject action
    '    has been cancelled.  See the OnNoData Event of the report.
    If Err.Number = 3265 Or Err.Number = 2501 Then
        Resume Next
    Else
    '*** write out the error and exit the sub
    MsgBox Err.Description
    Resume Exit_PrintCostCenters
    End If

End Sub

Thank you,

UongSaki