Subreports within subreports

[SallySue]

Hi,

I have a report, and in the detail section of one of the groups have inserted
a subreport (RptEstimBkdownStyleshSub) which references a query - this
operates correctly, and displays the appropriate stylesheet name in a control
in this subreport's detail section.

However, I then tried to add a subreport (RptEstimBkdownScreenSub), also in
the stylesheet subreport's detail section, to list the screens used by the
current stylesheet appearing. In isolation, the query for the screen
stylesheet worked fine; and the preview for the subreport also looked okay.

But when I try to add the screen subreport to the stylesheet subreport, I get
the "helpful" error "Microsoft Office Access has encountered a problem and
needs to close.......", and I'm thrown out. This happens both if I create
the screen subreport in situ using the toolbox wizard, and if I try creating
it first and dragging it to the stylesheet subreport. If I drag it to, or
create it in, the main form instead, there's no error.

At the end of my tether on this one. Help?

 

[Allen Browne]

What you are trying to do should work, so we are trying to hunt down what is
causing Access to crash.

Firstly, let's handle the basics:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database:
Tools | Database Utilities | Compact/Repair

3. Do any of these 3 reports have any code in their events, or do they call
functions in the event properties? If so, try a decompile:
3a. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
3b. Open Access (holding down the Shift key if you have any startup code),
and compact again.
3c. Open a code window. Choose Compile from the Debug menu. Fix any errors,
and repeat until it compiles okay.

4. Do other wizards work? If not, there could be several things that need
addressing there. Here's a starting point:
http://support.microsoft.com/?id=303769

5. If those issues don't help, we may be down to examining what's in the
queries that feed the reports. Particularly, things like subqueries and
boolean fields on the outer side of a join can be problematic.

Hope that gives you a useful lead on something that isn't easy to track
down.

 

[SallySue via AccessMonster.com]

Hi Allen,

Thanks very much for having a look at this.
I followed your suggestions (including recompiling and recompacting), but am
still getting that error. The subreport wizard works, as long as I'm
creating/dragging in a subreport in the main report; but goes haywire when I
try to do either inside the stylesheet subreport.

I've tried this also with a very simple subreport (SELECT Application.
ApplicationName FROM Application, both creating it in situ with the wizard,
and dragging it in; and this too worked outside the stylesheet subreport, and
caused a crash inside it. So - even my very rudimentary Access knowledge
says the problem seems to be in the stylesheet subreport rather than in what
I'm trying to put in it.

The query for the stylesheet subreport is:-
SELECT RptEstimBkdownStyleshSubQuery.LegacyDocID,
RptEstimBkdownStyleshSubQuery.LegacyDocName, RptEstimBkdownStyleshSubQuery.
ApplicationName, RptEstimBkdownStyleshSubQuery.StylesheetName
FROM RptEstimBkdownStyleshSubQuery;

which references a query I created first. And here is the query it
references:-

SELECT RptEstimBkdownQuery.LegacyDocID, RptEstimBkdownQuery.LegacyDocName,
RptEstimBkdownQuery.ApplicationName, Stylesheet.StylesheetName
FROM ((RptEstimBkdownQuery INNER JOIN Stylesheet ON RptEstimBkdownQuery.
ApplicationID = Stylesheet.ApplicationID) INNER JOIN (LegacyDialogueScreen
INNER JOIN LegacyChange ON LegacyDialogueScreen.LegacyDialogueScreen =
LegacyChange.LegacyDialogueScreen) ON RptEstimBkdownQuery.LegacyDocID =
LegacyChange.LegacyDocID) INNER JOIN StylesheetForm ON (Stylesheet.
StylesheetName = StylesheetForm.StylesheetName) AND (LegacyDialogueScreen.
LegacyDialogueScreen = StylesheetForm.LegacyDialogueScreen)
GROUP BY RptEstimBkdownQuery.LegacyDocID, RptEstimBkdownQuery.LegacyDocName,
RptEstimBkdownQuery.ApplicationName, Stylesheet.StylesheetName
ORDER BY Stylesheet.StylesheetName;

These seem to pick up the correct elements for the stylesheet subreport; but
my unpracticed eye can't see what could be causing a problem here for other
subreports inserted into it - hopefully yours will! Please let me know if
you need any other info, in any event.

What you are trying to do should work, so we are trying to hunt down what is
causing Access to crash.

Firstly, let's handle the basics:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database:
Tools | Database Utilities | Compact/Repair

3. Do any of these 3 reports have any code in their events, or do they call
functions in the event properties? If so, try a decompile:
3a. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
3b. Open Access (holding down the Shift key if you have any startup code),
and compact again.
3c. Open a code window. Choose Compile from the Debug menu. Fix any errors,
and repeat until it compiles okay.

4. Do other wizards work? If not, there could be several things that need
addressing there. Here's a starting point:
http://support.microsoft.com/?id=303769

5. If those issues don't help, we may be down to examining what's in the
queries that feed the reports. Particularly, things like subqueries and
boolean fields on the outer side of a join can be problematic.

Hope that gives you a useful lead on something that isn't easy to track
down.

I have a report, and in the detail section of one of the groups have
inserted

[quoted text clipped - 20 lines]

At the end of my tether on this one. Help?

 

[Allen Browne]

The queries look fine, though they are based on other queries, so the
problem could be lower down.

There seems to be a table named Application? That's the name of a root
object in VBA, so it could cause problems if it were actually referred to in
the report (as in your simple example), but I don't see how that would
affect the report you are having problems with.

You suspect this particular subreport is the issue. Would it be simple
enough to make a copy of the database, delete the (possibly bad) subreport,
compact the database, create it again, and see if it works now?

An alternative might be to save it out to a text file with:
SaveAsText acReport, "RptEstimBkdownScreenSub",
"C:\RptEstimBkdownScreenSub.txt"
Then delete it, compact, and import it again with:
LoadFromText acReport, "RptEstimBkdownScreenSub",
"C:\RptEstimBkdownScreenSub.txt"

Another approach might be to open the main report in design view, and delete
its RecordSource property so it is unbound. Then try adding the subreport.
After that, set the main report's RecordSource again, and manually set the
LinkMasterFields and LinkChildFields properties of the subreport. That might
work around the problem if it was caused by bad linkage.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks very much for having a look at this.
I followed your suggestions (including recompiling and recompacting), but
am
still getting that error. The subreport wizard works, as long as I'm
creating/dragging in a subreport in the main report; but goes haywire when
I
try to do either inside the stylesheet subreport.

I've tried this also with a very simple subreport (SELECT Application.
ApplicationName FROM Application, both creating it in situ with the
wizard,
and dragging it in; and this too worked outside the stylesheet subreport,
and
caused a crash inside it. So - even my very rudimentary Access knowledge
says the problem seems to be in the stylesheet subreport rather than in
what
I'm trying to put in it.

The query for the stylesheet subreport is:-
SELECT RptEstimBkdownStyleshSubQuery.LegacyDocID,
RptEstimBkdownStyleshSubQuery.LegacyDocName,
RptEstimBkdownStyleshSubQuery.
ApplicationName, RptEstimBkdownStyleshSubQuery.StylesheetName
FROM RptEstimBkdownStyleshSubQuery;

which references a query I created first. And here is the query it
references:-

SELECT RptEstimBkdownQuery.LegacyDocID, RptEstimBkdownQuery.LegacyDocName,
RptEstimBkdownQuery.ApplicationName, Stylesheet.StylesheetName
FROM ((RptEstimBkdownQuery INNER JOIN Stylesheet ON RptEstimBkdownQuery.
ApplicationID = Stylesheet.ApplicationID) INNER JOIN (LegacyDialogueScreen
INNER JOIN LegacyChange ON LegacyDialogueScreen.LegacyDialogueScreen =
LegacyChange.LegacyDialogueScreen) ON RptEstimBkdownQuery.LegacyDocID =
LegacyChange.LegacyDocID) INNER JOIN StylesheetForm ON (Stylesheet.
StylesheetName = StylesheetForm.StylesheetName) AND (LegacyDialogueScreen.
LegacyDialogueScreen = StylesheetForm.LegacyDialogueScreen)
GROUP BY RptEstimBkdownQuery.LegacyDocID,
RptEstimBkdownQuery.LegacyDocName,
RptEstimBkdownQuery.ApplicationName, Stylesheet.StylesheetName
ORDER BY Stylesheet.StylesheetName;

These seem to pick up the correct elements for the stylesheet subreport;
but
my unpracticed eye can't see what could be causing a problem here for
other
subreports inserted into it - hopefully yours will! Please let me know if
you need any other info, in any event.

What you are trying to do should work, so we are trying to hunt down what
is
causing Access to crash.

Firstly, let's handle the basics:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database:
Tools | Database Utilities | Compact/Repair

3. Do any of these 3 reports have any code in their events, or do they
call
functions in the event properties? If so, try a decompile:
3a. Close Access. Make a backup copy of the file. Decompile the database
by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
3b. Open Access (holding down the Shift key if you have any startup code),
and compact again.
3c. Open a code window. Choose Compile from the Debug menu. Fix any
errors,
and repeat until it compiles okay.

4. Do other wizards work? If not, there could be several things that need
addressing there. Here's a starting point:
http://support.microsoft.com/?id=303769

5. If those issues don't help, we may be down to examining what's in the
queries that feed the reports. Particularly, things like subqueries and
boolean fields on the outer side of a join can be problematic.

Hope that gives you a useful lead on something that isn't easy to track
down.

I have a report, and in the detail section of one of the groups have
inserted

[quoted text clipped - 20 lines]

At the end of my tether on this one. Help?

 

[SallySue via AccessMonster.com]

Allen,

Thanks for that - it sounds like the most logical next step!
I'm away over the weekend, but I'll try it on Monday, and report back.

The queries look fine, though they are based on other queries, so the
problem could be lower down.

There seems to be a table named Application? That's the name of a root
object in VBA, so it could cause problems if it were actually referred to in
the report (as in your simple example), but I don't see how that would
affect the report you are having problems with.

You suspect this particular subreport is the issue. Would it be simple
enough to make a copy of the database, delete the (possibly bad) subreport,
compact the database, create it again, and see if it works now?

An alternative might be to save it out to a text file with:
SaveAsText acReport, "RptEstimBkdownScreenSub",
"C:\RptEstimBkdownScreenSub.txt"
Then delete it, compact, and import it again with:
LoadFromText acReport, "RptEstimBkdownScreenSub",
"C:\RptEstimBkdownScreenSub.txt"

Another approach might be to open the main report in design view, and delete
its RecordSource property so it is unbound. Then try adding the subreport.
After that, set the main report's RecordSource again, and manually set the
LinkMasterFields and LinkChildFields properties of the subreport. That might
work around the problem if it was caused by bad linkage.

HTH

Thanks very much for having a look at this.
I followed your suggestions (including recompiling and recompacting), but

[quoted text clipped - 89 lines]