469,270 Members | 1,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Loop through multiple reports and combine as 1 PDF file

I work for a company with strict lockdown on how our computers are configured. So, add-ins or any new installations are impossible. The following is my dilemma.

I created almost 60 different types of metrics report for my department. The MIS is being used by different levels of users. With so many audience, I want to provide them a tool to create a profile and each profile contains the pre-set reports to run on a weekly basis. For example, John Doe will only need 15 out of the 60 reports. Most of the reports are driven off a form through parameters in the queries.

First problem: I was able to create a loop to cycle through the 15 reports. However, the problem appears when the report has no data. I want to be able to exclude the reports with no data. I cannot seem to find any VB on how to do this.

Second problem: I don't want to kill trees in the process. If I solve the first problem, ideally instead of printing the reports (which could run in hundreds pages) I would like to export the batch report as 1 PDF file. Some of the solutions I found seem to focus on merging file in PDF. Any idea on how to do this in VB and then export to PDF. Headers and report numbers are irrelevant.
Jul 22 '10 #1
5 6069
3,532 Expert 2GB
Never used PDFs but someone here is sure to have and can help you.

As to the reports with no data, use the...you got it, report's NoData event to cancel the report!
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.  Cancel = True
  3. End Sub
If you are using a form button to open the report, then add error handling to the click event set to ignore Error Number 2501.

Welcome to Bytes!

Linq ;0)>
Jul 22 '10 #2
I already have no data event in all my reports. The event cancels the report. This will not work in a loop. Correct me if I am wrong cancelling a report in the middle of the loop will break the loop right? Say of the 15 reports selected, report 8 has no data. The loop will not proceed to report 9. I think a better VB will resolve this. Not an advance programmer myself...
Jul 23 '10 #3
207 100+
Instead of using an error trapper surrounding the bulk of your code couldn't you just use:

On Error goto Next

To have it jump to the next line of code. I remember I had the same issue once with printing reports and I believe this is how I got around it.
Jul 23 '10 #4
283 100+
I have a question about your PDF question...

You want to export to a PDF file, but do you have Adobe writer installed or another type of PDF conversion program? Since you said your company has some strict lock down rules on add-ins. You would need to get an add-in so you can write to a PDF.

A work around for this is you can write to whats call a snap shot, it looks and opens just like a PDF but its a built in feature that Access offers for those who dont have PDF conversion programs.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "reportname", "Snapshot Format", "DrivePathFileDOTsnp"
Jul 24 '10 #5
Than you for the Snap suggestion. Our company uses PDF documents on everything we do. Most admin personel has PDF writer, including me.

The PDF add-in is standard on our laptop and the add in allows user to select multiple reports to export to PDF. This will not work because all our reports are menu driven.

I don't think there are any solutions to combine multiple reports in Access 2007. I've seen suggestions on using sub-reports. Not a solution to what I need to do.
Jul 24 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Knocked Wood | last post: by
2 posts views Thread by Sigurd Bruteig | last post: by
2 posts views Thread by Eych | last post: by
4 posts views Thread by Kim Mitchell | last post: by
1 post views Thread by fsbernard | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.