MS Access 2010 – Simple Function to loop through a list and Print to file an individual PDF

Had to move to MS Access 2010 to do this as no facility for direct to PDF print in MS Access 2003

Where the fields in the QUERY-ListofIDtoPrint includes ID / Field02 / Field03 / Field04

Public Function LoopandPrint()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String

mypath = "C:\Data\EXPORTdirectory\"

Set db = CurrentDb()
Set rs = db.OpenRecordset("QUERY-listofIDtoPrint")

Do While Not rs.EOF

MyFileName = rs!ID & "-" & rs!Field02 & "-" & rs!Field03 & "-" & rs!Filed04 & ".pdf"

'MsgBox MyFileName

DoCmd.OpenReport "R001TargetReport", acViewPreview, , "[PlanAppID]=" & rs!ID
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "R001TargetReport"

rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

End Function

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, MS Access, VBA Code MS Access. Bookmark the permalink.