Avatar of ajd3rd
 asked on

Need To Add A Parameter to A Report Using acOutputReport

I am trying to create reports in Access 2007 and output them to a pdf documents.  I have a report that I want to run and pass a fund number as a parameter and then output it to a pdf doc.  I've attached the code that I'm using to run the report and create the pdf.

I've been unable to pass in the fund number so the report uses it as a filter.


Function PrintToPDF(SrcFile, DestFileName As String)
On Error GoTo PrintToPDF_Err

ShowPdf = False
DoCmd.OutputTo acOutputReport, SrcFile, "PDFFormat(*.pdf)", DestFileName, ShowPdf, "", 0, acExportQualityPrint

    Exit Function

    MsgBox Error$
    Resume PrintToPDF_Exit

End Function

Open in new window

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

Somehere in the Access's Report open event you probably already have code that looks something  like this:
Me.Filter =  "[FundNo] = [Enter Fund No]"  
Me.FilterOn =True
>>>>>>don't close report until you have output it to pdf<<<<<<<<
Private Sub btnPDF_Click()
Call PrintToPDF
DoCmd.Close acReport , SrcFile                                <<<<assumed SrcFile is a variable name      
End Sub
Dale Fye

What is the code you are using to call the PrintToPDF?

It sounds like the way you normally open this report is with a parameter that filters in when you open the report.  So if you are in design view, and click PrintPreview, it gives you the report for all of the Fund Numbers.  But if you are in the form view, and you want to see the report, you use the OpenReport method and pass it a Filter or WHERE parameter, something like:

docmd.OpenForm "FormName", , , "[FundNum] = 1234"

Unfortunately, the OutputTo method accepts no such parameter, so when you use it, it outputs the entire report.

I've found several ways around this, but prefer one of these two:

1.  Open the report (you can hide it if you don't want your users to see it) using the OpenReport method and passing it the criteria for filter prior to using the OutputTo method.  Then, once the report is open you can use the output method the way you normally would.  Interestingly enough, you could also add a line of code that changes the reports caption so that the title of the file that gets "output" actually has a more specific name, instead of the name of the report.

2.  Another way is to modify the report so that instead of passing it a value when you open it, it uses the value of a control on an open form to filter it.  So the query for the report might look like:

SELECT * FROM yourTable
WHERE Forms!yourFormName.controlName IS NULL
OR [FundNo] = Forms!yourFormName.ControlName

This filter will return all of the records if there is no value in Forms!yourFormName.controlName, but will only return those records where [FundNo] matches the value in that field if the field has an entry.



You are right that when I open the report in form view I filter for one fund but if I just run the report it returns all funds.  

What I need to accomplish is to run the report multiple times to create one pdf doc for each fund number.  I used DoCmd.Open report in the past with a pdf utility but that utility doesn't always work and is very pc dependent.

Do you know of anyway to create the multple reports using the OutputTo command?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

<<<I used DoCmd.Open report in the past with a pdf utility but that utility doesn't always work and is very pc dependent.>>>>
I've used this method extensively and never had a problem.  If it did not work, you probabably did not use the right syntax for the variable's data type in the where clause. Also, how is this method pc dependant?????

If you want another approach, as a slight variation of this method you can pass the filter to the report via a parameter form which is referenced in the criteria row of the query that you are using as the record source of your report.  You could also set up a global variable (for example gFilter) and pass the global variable to your report filter, and then pass it via the open report to the pdf function.  



The DoCmd open report worked fine.  It's the utility that I'm using to create the pdf.  This was prior to the Microsoft add in for pdf.  It was very dependent on how you had Adobe Accrobat set up on the individual pc.  Sometime it creates the pdf docs fine and sometimes it doesn't create anything.  It was also very dependent on which version of Accrobat was on the pc.

I can't find a way to create the pdf docs using the new microsoft add in with the DoCmd.Open report command.  Do you know of any way to specify the report output to automatically create the pdf?  Sort of like the acpreview but instead acpdf with a given name.

Thanks for your help!

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.