I am still struggling with this report failing on me, so I will try and describe it better. First of all the error is not the result of a no data situation; if anything it is too much data.
The structure of the report is a set of nested subreports. There is the main report, whose query data source is very straightforward. Then there is a "main subreport" within the main report, and its data source is a query that is similar to but different from the main report's query, meaning that in each case a subset of data is pulled depending upon a combo box on a form.
The subreport itself contains three other subreports, which have their own queries. All of these queries contain only one or two joins at most, although there is one union query involved.
To use my nomenclature of Service Groups and Departments and Job Categories, I would say that the report generating form is where a Service Group is selected for reporting. Each Service Group contains several (1 to 40) Departments, and each Department contains from about 5 to 20 or so Job Categories (plenty of overlap, e.g. every department might have Clerical as a Job Category). Department and Job Category are both part of the same table that is periodically updated by HR. So almost the entire report comes off one table, or actually two as one is a Budget table, with similar Department and Job Category fields as you might expect.
OK. So I can run what I call a "consolidated" report for 20 of 21 Service Groups, and in each of those cases, I get a report of one to four or so pages, listing data (other fields) for each of the Job Categories in any of the Departments that are part of the Service Group selected. The report is slow to output (either on its own or to a pdf), but it works fine. So there is no query problem, i.e. too many joins, etc. as far as I know. Only on this one Service Group, which happens to contain more Departments than any other Servie Group--the report generates, very slowly (I'm talking minutes here), page one, two, etc., up past five, and then quits. In Access proper, I get a system resource exceeded message, and attempting to create a pdf I get a vba output cancelled message.
If there is a way to error trap this output problem, then I need a pointer to help me insert such into my code. When it fails, I just get my docmd.output to line highlighted, with no other clue.
For testing purposes, I can back out one or more of the 40 departments that belong in the report, and when I get down to 30 or so, the report works fine, but of course it is no longer a consolidated/roll-up report! We are not talking about thousands of data points here, only a couple hundred at most. Coud there be a page formatting issue that, after 5 pages, exceeds some limit? If so, how can I see it happen, in real time, so to speak.
Sorry for all this text. I would share some code, but as I mention above there is no single report query as such!
Many thanks,
T.Y.
