Too many Subreports?

[Guest]

I have created several reports (11 for now, soon to be 14). They are all
based on their own specific query. Each report contains a total calculation
textbox which totals up in dollars the data for the report. All of these
reports are used as subreports on a main report. The main report totals up
the calculated textbox on each subreport and displays it in the footer.

Here's the problem I have. The report previews fine, everything totals and
looks great. BUT, when I try to print the report, an error says something
like "too many tables". Then sometimes another lengthy error pops up just
after, stating the calculation is too complex, etc. I do not have the
database with me right now, so I can't give you the exact error message.

I have read in help that Access can only handle ten groupings? Here's a key
point...if I delete three of the subreports leaving 8 on the main report,
everything prints beautifully. If I leave 9 subreports, it doesn't work.

I tried splitting the subreports into groups of two, such as the main report
contains two subreports: one holding 7 "sub" subreports, and the other
holding 4 "sub" subreports. This still does not work.

#1: Why would the report preview to the screen fine but not print?
#2: Have I in fact exceeded what Access is capable of?
#3: Short of redesigning my whole database, is there any way around this?

This is the last thing that needs to be done to complete this database.

 

[PC Datasheet]

After you preview the report, you need to close the report and then open it
to print without previewing. If you print directly from preview, the Format
event runs again opening all the queries that are needed for the report
again while not closing the queries when they were opened to preview the
report. Thus a double load is placed on your system resources.

 

[Guest]

I have to say that I can't believe that works! But it does, and thank you
very much.
The only thing that concerns me is that 99% of the users will preview the
report first to review the data for accuracy then print the report directly
from the preview. I am hesitant to ask the even less knowledgable than I to
click more buttons. (we know how the average user can be, lol) Most users
are using the runtime version and have older pc's so I'm afraid it might not
even print at all.

#1: Is this strictly a system resource issue?
#2: Could I create a custom print button that will close the report then
print it? (I will try this)

 

[PC Datasheet]

#1: Is this strictly a system resource issue?>
No, it has to do with the capacity of Access to basically handle open
queries. Access97 SR2 has twice the capacity as Access97, Access2000, 2002
and 2003 have the same capacity as Access97 SR2.
Sometimes you get the error message "Can't open any more databases".
Databases here is a misnomer, more accurate would be recordsets.

#2: Could I create a custom print button that will close the report then
print it? (I will try this)

Yes that will work!

 

[Guest]

Thanks for the help. That's what I kind of figured, that it was a limitation
of Access. Unfortunately, I do not have much flexibility with changing the
database design at this point.

The custom button works. It's a workaround that the user won't know is
happening. (I hope,lol)