Have a look at the attached code:
Code:
Public lCurrentID_Record As Long 'place this at the top of a standard module
Public Function vcCurrentID_Record() As Long 'place this at the top of a standard module
vcCurrentID_Record= lCurrentID_Record
'use this function in a new query based on your existing report record source
'place the function in the criteria row of the PK ID to return one record at the time
End Function
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<this is the code for the export button on your form>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub cmdExportPDF_Click()
On Error GoTo Err_cmdExportPDF_Click
Dim rst As DAO.Recordset
DIm sFileName as string
Set rst = CurrentDb.OpenRecordset("qryYourExistingReportRecordSource", dbOpenSnapshot) 'this is the existing query returning all records to be exported
On Error Resume Next
DoCmd.Close acReport, "rptYourReportFiltered" 'this is a copy of your existing report with the new recordsource returning one record at the time using the public function above
lCurrentID_Record = 0
Do Until rst.EOF
lCurrentID_Record = rst("[PK_ID]") 'replace PK_ID with your own unique ID
sFileName="BuildYourFileNameUsingACommonPathAndUniqueInfoFromTheRecordset"
DoCmd.OutputTo acOutputReport, "rptYourReportFiltered" , "PDF Format (*.pdf)", sFileName, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Exit_cmdExportPDF_Click:
Exit Sub
Err_cmdExportPDF_Click
MsgBox Err.Description
Resume Exit_cmdExportPDF_Click
End Sub
Basically you create a new report based on a parameter query that returns one record at the time. The parameter can be supplied to the query in many ways (tempvar, hidden control on a form, etc.), I like to use a public function and a public variable as it is easier to follow.
Cheers,
Vlad