Print to PDF error (1 Viewer)

[jsic1210]

This is the second database where this has happened to me. I have code to print a report (with several sub-reports) to PDF. When it prints, only the first couple reports appear. The rest of the pages on the PDF are blank. If I try manually saving to PDF (Save As > PDF or XPS), I get the error "Cannot open any more databases."
In the other case, my "solution" was to copy all the queries into temporary tables and use the temp tables as the datasources for the reports. I realize this is bad practice, but I couldn't find another solution.
Does anyone have any suggestions?

 

[Minty]

How complex are the queries you are running?
Do they have lots of outer joins?
How many records are being returned out of how many source records?

If making the data available in temp tables cured the problem it sound like you may have too complex a query set.

 

[jsic1210]

Yes, some have 2 or 3 outer joins (max maybe 5). Most derive from 2 tables. One table has about 3,500 records, the other has about 24,000 records. The queries are returning 1,000-3,500 and a few to 24,000 records, respectively. My queries need to be complex to display the report.

 

[Minty]

To be fair up to 24000 records to export to a pdf is fairly significant number - I would have thought that amount of data would be better exported to excel for analysis.

Do the sub reports run on their own?

Could you show us some example queries, with just some sample data?

Is the data stored in Access or a SQL backend?

 

[jsic1210]

It's actually not exporting thousands of records to the report. I just meant the queries that are the datasources have that many records. The reports at most output 25 or 30 records.
Some of the subreports have that many records (could that be the problem?), but the detail is hidden. I haven't tried loading the reports individually - I don't have forms to filter and open them individually. And the report as a whole runs just fine. It's when I try to export it to PDF that I have problems.

 

[Minty]

I have seen something similar with filtered sub-reports a long time ago.
From memory If possible I would try and restrict the sub reports recordsets - maybe if you are using a form to select the report data you could add additional criteria there?

I think when I looked into it in more depth, the export to pdf literally loops through all the sub data before formatting, and then exports, which could be your issue.

Can you apply any extra grouping that might give you the higher level info without the sub-detail?