Access MS: How to make a macro generating custom reports?

advertisements

I currently have a macro in MS Access 2010 .mdb (Access 2002-'03 format).

The macro opens the Report and utilizes pairs of SetFilter and ExportWithFormatting. The macro hangs up after the first 2 or 3.

I was looking into placing delays but it appears that I would have to use VBA. Is that my only choice? Any suggestions or fixes to go about it?

Function Copy_Of_Wellness()
On Error GoTo Copy_Of_Wellness_Err

    DoCmd.Hourglass True
    DoCmd.OpenReport "Wellness Plan Report", acViewReport, "", "", acIcon
    DoCmd.SetFilter "", "[Ind]=""Mary Jane""", ""
    DoCmd.OutputTo acOutputReport, "Wellness Plan Report",
           "PDFFormat(*.pdf)", """M:\Mary.pdf""", True, "", , acExportQualityPrint
    DoCmd.SetFilter "", "[Ind]=""Howard Johnson""", ""
    DoCmd.OutputTo acOutputReport, "Wellness Plan Report", "PDFFormat(*.pdf)", """M:\Howard.pdf""", True, "", , acExportQualityPrint
    DoCmd.Hourglass False

Copy_Of_Wellness_Exit:
    Exit Function

Copy_Of_Wellness_Err:
    MsgBox Error$
    Resume Copy_Of_Wellness_Exit

End Function

Despite what the initial error indicates, the report IS bounded and does produce the reports fine at first.

The stops are arbitrary, possibly run-time error?


Consider using the WhereCondition in DoCmd.OpenReport and then passing an empty ObjectName argument in DoCmdOutputTo with no need for DoCmd.SetFilter as per the docs.

ObjectName > Optional > Variant: ...If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank...

VBA

Adjusted your use of quotes, also with remove of acIcon which may be hanging the processing as it launches an external taskbar action

...
DoCmd.OpenReport "Wellness Plan Report", acViewReport, , "[Ind]='Mary Jane'"
DoCmd.OutputTo acOutputReport, , acFormatPDF, "M:\Mary.pdf", True, , , acExportQualityPrint

DoCmd.OpenReport "Wellness Plan Report", acViewReport, , "[Ind]='Howard Johnson'"
DoCmd.OutputTo acOutputReport, , acFormatPDF, "M:\Howard.pdf", True, , , acExportQualityPrint
...

Even consider a loop for a DRY-er approach:

...
Dim var as Variant

For Each var in Array("Mary Jane", "Howard Johnson")
   DoCmd.OpenReport "Wellness Plan Report", acViewReport, , "[Ind]='" & var & "'",
   DoCmd.OutputTo acOutputReport, , acFormatPDF, "M:\'" & var & "'.pdf", True, , , acExportQualityPrint
Next var