how many subreports?

[JohnLute]

How many subreports can be embedded?

I have a report that has 1 subreport with about 20 subreports. Each of those
subreports uses 1 common subreport that has about 20 subreports as well. In
other words:

1 Report > 1 Subreport w/20 subreports > 1 Subreport w/20 subreports

I now have to create 1 more subreport with 20 more subreports to embed in
the last subreport listed above. I know it sounds insane but my database is a
rather unique design. I've begun designing this last subreport but the main
report returns odd data/format if I embed any more than 3 subreports.

It seems I've reached the number of subreports possible...?

 

[Allen Browne]

Subreports cannot be nested to more than 7 levels, but AFAIK, there is no
specified limit on the number of subreports that can be placed on a report,
but there are other limits you are likely to strike.

If each of those reports/subreports are bound, and some of them are bound to
queries that contain more than one table, and especially if some of those
tables are attached or the reports include combos or list boxes that have
further RowSources, you are likely to hit limits on the number of open
tables or databases that Access can handle.

Each query has a limit of around 2000 characters width, so you could hit
that one with so many firing also.

 

[Larry Linson]

There is no stated limit on the total number of Subreports. Access 2003 Help
states:

"A main report can include subforms (subform: A form contained within
another form or a report.) as well as subreports, and it can include as many
subforms and subreports as you want. Additionally, a main report can contain
up to two levels of subforms and subreports. For example, a report can
contain a subreport, and that subreport can contain a subform or a
subreport. The following table shows the possible combinations of subforms
and subreports in a main report."

However, this is in error, as the maximum level of nested
subforms/subreports is actually 7, just as is the maximum level of nested
subforms in forms.

BUT, if you have many subforms or subreports, you can expect performance to
suffer because there is considerable overhead involved. Just calculate how
many total reports you will be formatting for each detail line! Still, if
that is the only acceptable way to show the data, 'taking a performance hit'
may be justified; but don't be surprised if it seems like stirring a vat of
cold molasses with a canoe paddle. (And perhaps I should have used "when"
rather than "if".)

Larry Linson
Microsoft Access MVP

 

[JohnLute]

Thanks, Allen. It seems I've hit the limit on the number of open tables
however, the database doesn't prompt me with this. Typically, it either
returns odd formatting of the main report or Access shuts down.

I've received the prompt "too many databases open" in the past for other
reasons however, I can only assume this is what's happening now minus the
prompt as things are fine until I add a 4th subreport.

This whole problem has arisen as a result of my trying to correct a nesting
problem I recently posted here.

UGH. Now I don't know what to do.

 

[Mike Binger]

Is it correct that only 2 levels are allowed in Access 97?

Mike Binger