Export embedded report to PDF

[Aaron5714]

I have pasted a link below to a database I am working on that tracks contract documents. I am having some trouble trying to export a report that is emedded in a form using the BrowseTo method (I believe this feature is new in Access 2010 so it may not work in earlier versions).

Click the "Report Center" tab, then click the "Active Agreements" link on the left side of the page. Select the first option on the pop-up window ("Select a Category"), click OK, then on the next window select "Category 1" in the drop-down menu and click OK.

The "rptActiveContractsByLocation" report will load using the BrowseTo method. On the bottom of the report I added a link to export to PDF. This link uses the DoCmd.OutputTo method and saves a PDF of the report on the user's desktop.

Here is the problem: the PDF report that is generated shows all records; it is not the filtered report that is displayed on the screen. I would like to have a link or button the user can click if they want to export the exact report displayed on screen with the filters applied.

Any help would be greatly appreciated.



Here is the sample database: http://dl.dropbox.com/u/65832352/Exa...Database.accdb

[June7]

I believe the problem is that the report is not open as an independent object that the OutputTo method can recognize so it finds it in the Navigation pane list and outputs the report which is not filtered. Referencing the embedded report would mean referencing through the levels of the navigation form it is buried in. I am doubtful the OutputTo method can handle this.

[Aaron5714]

Thanks for your input; I assumed that was the issue. Any ideas for a work-around? My users like the embedded reports feature rather than having reports open in separate tabs or windows, however they also need to be able to export them.

Would it be possible to have a link on the embedded report that opens a copy as an independent object (in a new window/tab, possibly hidden), and then runs the OutputTo method, then closes the window? Let me know what you think.

Thanks again!

[June7]

That is the approach I would take. Code opens the report in ReportView, runs OutputTo, and closes. The procedure can happen so fast, the users might barely see a flash on the screen.

I don't use Tabbed Documents, I use Overlapping Windows, in my db setup.

[Aaron5714]

I tried editing the link at the bottom of the report so that it would open the embedded report in a new window, using the DoCmd.OpenReport method, however I am still having trouble referencing the filtered report that is displayed on the screen rather than the report from the navigaition pane.

Any advice on how to write the VBA statement so that is will open the embedded report displayed on screen (with the filters applied) in a new window?

[June7]

So the report would be open as embedded in the form and also as an independent object? Opening multiple instances of same object is not easy. I have seen code that does it for forms.

Review http://allenbrowne.com/ser-35.html

[Aaron5714]

Here's a thought: Once the report has been generated by the user and is embedded in the Report Center form, is there a way to read the filter property of that embedded report? If so, I think I can just run the standard DoCmd.OpenReport and pass those filter values in the Where Condition, then when the report is open in a new window I can run the OutputTo method to generate a PDF.

I know how to read the filter property of a report that is an independent object (not embedded), but I'm not sure how to do it when the report is embedded within the levels of the navigation subform. I'm guessing I would have to reference a path like this: frmMain > NavigationSubform > frmReportCenter > rptActiveContractsByLocation however I'm not sure of the exact syntax.

[June7]

Can read the RecordSource or Filter properties of subform/subreport - I do that with normal form/subform that is built with Container Control. You are using a Navigation form. Referencing the embedded report would mean referencing through the levels of the navigation form it is buried in and I don't know the syntax for that. I've tried to help another poster figure that out and gave up. I've never used navigation form.

[Aaron5714]

Ok, thanks for your input. I will keep experimenting and see if I can figure it out.

[Aaron5714]

Looks like I figured this out. I was able to read the filter property of the embedded report with a simple "Me.Filter". When the user clicks the "Export to PDF" link on the embedded report it runs the code below. This opens the report as an independent object using the OpenReport method and using Me.Filter (i.e. the filters currently applied on the embedded report) as the where condition, then it runs OutputTo to generate a PDF of the filtered report, then finally is closes the new window that was opened by OpenReport.

Private Sub txtExport_Click()
    DoCmd.OpenReport "rptActiveContractsByLocation", acViewReport, , Me.Filter, acWindowNormal
    DoCmd.OutputTo acOutputReport, "rptActiveContractsByLocation", acFormatPDF, "C:\Users\" & (Environ$("Username")) & "\Desktop\ActiveContractReport.pdf"
    DoCmd.Close acReport, "rptActiveContractsByLocation", acSaveNo
    Beep
    MsgBox "Report export complete. File saved to:" & Chr(13) & "C:\Users\" & (Environ$("Username")) & "\Desktop\ActiveContractsByCategory.pdf"
End Sub

[June7]

Having the link on the report instead of some level above the report does make it easier. Interesting that two instances of report is allowed with regular code. Maybe it's just forms that require special handling.

Glad you figured it out, congratulations!