Update
I found some code here that works beautifully! The only issue I now have is how do I alter the code to save the report into (let's say) the directory where the database is located?
I'm not a VBA expert but I believe the relevant part of the latest code is this part :
Public Function RunReportAsPDF(prmRptName As String, prmPdfName As String, UniqueID As Integer) As Boolean
' Returns TRUE if a PDF file has been created
Dim AdobeDevice As String
Dim strDefaultPrinter As String
'Find the Acrobat PDF device
AdobeDevice = GetRegistryValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Devices", "Adobe PDF")
If AdobeDevice = "" Then ' The device was not found
MsgBox "You must install Acrobat Writer before using this feature"
RunReportAsPDF = False
Exit Function
End If
' get current default printer.
strDefaultPrinter = Application.Printer.DeviceName
Set Application.Printer = Application.Printers("Adobe PDF")
'Create the Registry Key where Acrobat looks for a file name
CreateNewRegistryKey HKEY_CURRENT_USER, "Software\Adobe\Acrobat Distiller\PrinterJobControl"
'Put the output filename where Acrobat could find it
SetRegistryValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat Distiller\PrinterJobControl", Find_Exe_Name(CurrentDb.Name, CurrentDb.Name), prmPdfName
On Error GoTo Err_handler
'Run the report (Note : I amended this code to filter the report for the unique ID)
DoCmd.OpenReport prmRptName, acViewNormal, , "[Unique_id] = " & UniqueID
While Len(Dir(prmPdfName)) = 0 ' Wait for the PDF to actually exist
DoEvents
Wend
RunReportAsPDF = True ' Mission accomplished!
Normal_Exit:
Set Application.Printer = Application.Printers(strDefaultPrinter) ' Restore default printer
On Error GoTo 0
Exit Function
Err_handler:
If Err.Number = 2501 Then ' The report did not run properly (ex NO DATA)
RunReportAsPDF = False
Resume Normal_Exit
Else
RunReportAsPDF = False ' The report did not run properly (anything else!)
MsgBox "Unexpected error #" & Err.Number & " - " & Err.Description
Resume Normal_Exit
End If
End Function
TIA, Andrew