VBA for exporting reports to PDF (1 Viewer)

bbsyfek · Oct 1, 2019

Hello guys,

I have an issue with my reporting macro.

The main idea is that I have 2 reports that I have to run couple times a day and export them to PDF files. But those reports need parameters input each time they are ran. Is there any way I can set those parameters with value taken from InputBox? Please see attached SS from macro builder.

Thanks in advance

Bart

Ranman256 · Oct 1, 2019

Tempvars!Account = InputBox(…..)
docmd.runMacro "myMacro"

bbsyfek · Oct 1, 2019

Ranman256 said:
Tempvars!Account = InputBox(…..)
docmd.runMacro "myMacro"

Uhm, okay but in my case I used TempVar only for naming the files with it. If it's possible I need to set this tempvar as a parameter for those reports.

Edit:
To clarify, when I run this macro I have 3 input boxes. First from my TempVar and second and third from two reports I am running. I need those last two boxes to be filled automatically with TempVar one.

Minty · Oct 2, 2019

I would create a form with a combo box to pick your account number then you can easily adjust your reports to use that value. Attach something like this to a print button on the form

Code:

    Dim sWhere as String
    Dim sFilepath as string
    Dim lAccountNo as Long
    
    lAccountNo = Me.cmbAcctNo [COLOR="SeaGreen"] ' rename this to your combo control you created[/COLOR]
    
    sFilepath = "C:\Users\filelocation\MyPDF" & lAccountNo & ".pdf"
    
    sWhere = "[AccountNo] = " & lAccountNo
    
    DoCmd.OpenReport "R01 Consignment reconciliation by Acct", acViewPreview, , ,sWhere
    DoCmd.OutputTo acOutputReport, "R01 Consignment reconciliation by Acct", "PDFFormat(*.pdf)", sFilepath, False, , , acExportQualityPrint
    DoCmd.Close acReport, "R01 Consignment reconciliation by Acct"
    
    sFilepath = "C:\Users\filelocation\MySecondPDF" & lAccountNo & ".pdf"
    
    DoCmd.OpenReport "R02 Par Level Summary By Acct", acViewPreview, , ,sWhere
    DoCmd.OutputTo acOutputReport, "R02 Par Level Summary By Acct", "PDFFormat(*.pdf)", sFilepath, False, , , acExportQualityPrint
    DoCmd.Close acReport, "R02 Par Level Summary By Acct"

Remove the criteria from your reports underlying queries, as this will open them filtered to the correct account.

bbsyfek · Oct 2, 2019

Thank you very much!

VBA for exporting reports to PDF (1 Viewer)

bbsyfek

New member

Attachments

Ranman256

Well-known member

bbsyfek

New member

Minty

AWF VIP

bbsyfek

New member

Similar threads

Users who are viewing this thread