I have various reports that I want to be able to open in Preview mode, and/or export to PDF, depending upon which form I'm using to create the report. Here is the code I'm using, in a module:
Code:
Public Function PreviewOrExportReport(action As ReportActionEnum) As Boolean
Dim isOK As Boolean
PreviewOrExportReport = False
If action = raPreview Then
On Error Resume Next
DoCmd.OpenReport TempVars!ReportName, acViewPreview
PreviewOrExportReport = Err.Number = 0
If Err.Number <> 0 Then
MsgBox Err.Description, vbExclamation, "Error"
End If
ElseIf action = raExport Then
' if report exists, ask if ok to replace
isOK = Dir$(TempVars!ReportFile) = ""
If Not isOK Then isOK = MsgBox("This report exists. OK to replace?", vbQuestion + vbYesNo + vbDefaultButton2, "Duplicate") = vbYes
If isOK Then
On Error Resume Next
DoCmd.OutputTo acOutputReport, TempVars!ReportName, acFormatPDF, TempVars!ReportFile, False
PreviewOrExportReport = Err.Number = 0
If Err.Number <> 0 Then
MsgBox Err.Description, vbExclamation, "Error"
End If
End If
End If
End Function
I have code running in the Report_Load() event and the Detail_Format() event. If I open the report in preview mode using the first part of the code above, everything works fine.
However, when I choose to export the report using the second part of the code, no report code runs at all. Therefore, items in the page header or report header appear blank, because they depend on that code running.
Researching this issue supplied a solution, which is to open the report in preview mode (so all the code runs), then export, then close the report in code.
The modified portion of the module code looks like this:
Code:
If isOK Then On Error Resume Next
DoCmd.OpenReport TempVars!ReportName, acViewPreview
If Err.Number = 0 Then
DoCmd.OutputTo acOutputReport, TempVars!ReportName, acFormatPDF, TempVars!ReportFile, False
DoCmd.Close acReport, TempVars!ReportName
End If
PreviewOrExportReport = Err.Number = 0
If Err.Number <> 0 Then
MsgBox Err.Description, vbExclamation, "Error"
End If
End If
This works, but causes an unpleasant flickering when the report opens in preview mode.
Is there any other solution which doesn't rely on opening the report in preview mode?
The report is based on a query rather than a table, so it's not so easy to put in values that are set at run-time into the data source query.
Thanks...