Report output fails after several pages--why? 1

[TomYC]

I have an Access 2007 report that works off a query whose parameters include texboxes filled in on a form, and also a few subreports. It loads very slowly, but it works, except for one of the choices among "departments" (actually "services") whose output would be a page or two more than the six pages or less that all the others turn out to be. I get a 2501 error message and the output, whether report display or pdf, fails with no further information.
How can I remedy this--or even trace it through to the error? Could it be that the page size grows by tiny amounts and after six pages spills over into forbidden territory? Or is it something like the 'system resources exceeded' message that I also get, and cannot fix either?
In sum, how to trouble shoot a balky report?

 

[genomon]

The 2501 error indicates no data for a report or subreport. Add some error handling to your code, or do a ctrl+brk and step through it a line at a time when the erorror is thrown to see what is expecting data that is not being provided.
Also have a look at the FAQs here regarding error handling.

Gluais faicilleach le cupan làn

 

[TomYC]

I will try and look up error trapping as you said; all the more so, since in my case it cannot be a no data cause, except in the trivial sense that something has already quit loading data. Again, this report works for dozens of departments (a query parameter from a form in which one or another departments is chosen to be included in the recordset), but for one particular one, with lots of what are equivalent to subdepartments, after the report has finished formatting up to approximately 8-10 pages (of what would probably be a 12-14 page report), it quits and gives me the 2501 error. So I need to identify the error that leads to 2501, etc.

 

[TomYC]

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.

 

[genomon]

The 2501 error indicates no data for a report or subreport.
That is what this error means. It's that simple. It doesn't mean anything else. Honest!

Gluais faicilleach le cupan làn

 

[TomYC]

Then I must amend my earlier description of results when the output fails. These days I am not getting any error number, only the message that the output was canceled, or that the system resources were exceeded. Actually, I can "generate" the 8 pages of the report, but I can only view them one at a time, and after 5 this view option fails. Is that clear? The report runs, but Access runs out of gas in attempting to either display page six, or to print any page past 5!! Sorry for the confusion.

 

[dhookom]

I don't think the 2501 has much to do with No Data. It just happens that people will use the No Data of a report to cancel/close it. IMO, the actual cancel/close generates the 2501, not the No Data.

TomYC,
Do you have Page of Pages in your report?
Are all of the joins and link master/child fields indexed?
Are you using any DLookup()s or combo boxes/lookup fields in the report(s)?


Duane
Hook'D on Access
MS Access MVP

 

[genomon]

Check to see that the page size/margins haven't been reset or increased. More on that here:

http://www.pcreview.co.uk/forums/access-2007-error-2501-using-vba-outputto-printto-t3753141.html

Gluais faicilleach le cupan làn

 

[TomYC]

Thanks to you both; I have reviewed that printer issue before, and I think I'll try re-copying elements of my report to a new file. Only trouble there is that I have subreports, so I'll have to recreate several items. And to dhookom, I have indexed the relevant fields, I believe, which are text fields in every case, and I do have a page of pages control, which I could dispense with, and no DLookups that I know of!
T.Y.

 

[dhookom]

Another possible issue might be the union query. Are you using UNION ALL?

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

Dhookom!
Could it be that [Page] of [Pages] is the culprit?
I removed that and my report works.
Halelujah!
But seriously if that is a known issue, I want to know!
I am using a UNION ALL query, FYI, thanks to earlier help on this site.
Thanks to all--
T.Y.

 

[dhookom]

The page of pages numbers have to be calculated by rendering the entire report with all of the Can Grow properties typically found in subreports.

I'm not sure it is an issue, just a function that takes time to deliver in some reports.

Duane
Hook'D on Access
MS Access MVP