Subreports Don't Display

[Martin]

I have a report with 8 subreports. They are all linked correctly and some of
the subreports do contain data. I have checked that the subreport the the
Visible property to "Yes", and I have also filtered the query in my main
report to the value that is linked to the subreports to have data. At least
the SubReport label prints.

If I run each of my subreports alone, they all print correctly.

I even tried eliminating 7 subreports to see if only with one subreport
containing data will print. And it still does not show the subreport's
content.

Any ideas? Thanks.

 

[Duane Hookom]

This almost always is caused by the data and the link master/child
properties. We can't see any of these from your application.

 

[Martin]

I meant to say print.

I checked that and they are correct. To further evaluate your comment, I
eliminated all 9 fields and added only the link field in the subreport. This
time I get a message that says "The query is too complex" and the borders of
the subreport do show up.

If I add a field in the subreport in addition to the link field, I don't get
any message and the subreport does not print. However, if I run the subreport
alone, the contents do print.

After my post yesterday, I went back to every query for each subreport to
make sure that I will get a 0 even if the field is null.

 

[Duane Hookom]

We still can't see your record sources, sample records, link master/child
properties,... Query is too complex might suggest your link master/child
properties are the same data type. What do you see if you clear out the link
properties entirely?

 

[Martin]

Duane,

Thanks for responding quickly.

I clear out the link properties and the subreport prints.

The link master/child field is a "Text" data type.

What I am trying to do is create an overall chemical use in a project by
categories and compare it against the limits allowed by its category. The
report format has different categories, and in my case, not all categories
are used in a single project. The tricky thing about chemicals is that many
times a single chemical belongs to more than one category.
What I have done in my queries, is create a series of query steps to create
a single row per project.
My first query reads from a Query for another report where the unit
quantities have been converted to the correct units of measurement (lbs or
gal) and contains the chemical category and room location.
My second query is a Union Query where I start assigning a field for each
category. I tried to create one union query but I was getting a message that
not enough resources were available. So I reduced the numbers of categories
per query. Below is a code for these queries:
SELECT ProjNumber, USQTY AS PS,0 AS PL,0 AS PG
FROM [QRptCBC-3DAll-2]
WHERE [QRptCBC-3DAll-2].Hazard="Pyrophoric" and [QRptCBC-3DAll-2].USM="Lbs"
UNION ALL
SELECT ProjNumber, 0 AS PS,USQTY AS PL,0 AS PG
FROM [QRptCBC-3DAll-2]
WHERE [QRptCBC-3DAll-2].Hazard="Pyrophoric" AND [QRptCBC-3DAll-2].USM="Gal"
UNION ALL
SELECT ProjNumber, 0 AS PS,0 AS PL,USQTY AS PG
FROM [QRptCBC-3DAll-2]
WHERE [QRptCBC-3DAll-2].Hazard="Pyrophoric" AND [QRptCBC-3DAll-2].USM="ft3";

What this query will do is create a row with a total sum at each category if
there is data. So if I have chemicals in two categories, I will get two rows.
So to reduce to one row I created the following query where the subreport
uses as a source.
SELECT Projects.ProjNumber, Sum(nz([QRptCBC-3DAll-15].[PS])) AS PS,
Sum(nz_([QRptCBC-3DAll-15].[PL])) AS PL, Sum(nz([QRptCBC-3DAll-15].[PG])) AS
PG
FROM [QRptCBC-3DAll-15] RIGHT JOIN Projects ON _
[QRptCBC-3DAll-15].ProjNumber = Projects.ProjNumber
GROUP BY Projects.ProjNumber;

The Main Report uses a Query based on the table Projects.

The intended link field between the Master and the Child is ProjNumber.

Thanks for your help.

 

[Duane Hookom]

If you are using ProjNumber as the Link Master/Child property and you know
that values in a subreport should match the ProjNumber but you aren't seeing
any records then I expect your subreport might be invisible or filtered or
there might be corruption. Your queries etc look fairly complex. I would
start with the simplest example of a main and subreport using these basic
records. Then continue to add stuff until you are either finished or the
report breaks.
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks for responding quickly.

I clear out the link properties and the subreport prints.

The link master/child field is a "Text" data type.

What I am trying to do is create an overall chemical use in a project by
categories and compare it against the limits allowed by its category. The
report format has different categories, and in my case, not all categories
are used in a single project. The tricky thing about chemicals is that many
times a single chemical belongs to more than one category.
What I have done in my queries, is create a series of query steps to create
a single row per project.
My first query reads from a Query for another report where the unit
quantities have been converted to the correct units of measurement (lbs or
gal) and contains the chemical category and room location.
My second query is a Union Query where I start assigning a field for each
category. I tried to create one union query but I was getting a message that
not enough resources were available. So I reduced the numbers of categories
per query. Below is a code for these queries:
SELECT ProjNumber, USQTY AS PS,0 AS PL,0 AS PG
FROM [QRptCBC-3DAll-2]
WHERE [QRptCBC-3DAll-2].Hazard="Pyrophoric" and [QRptCBC-3DAll-2].USM="Lbs"
UNION ALL
SELECT ProjNumber, 0 AS PS,USQTY AS PL,0 AS PG
FROM [QRptCBC-3DAll-2]
WHERE [QRptCBC-3DAll-2].Hazard="Pyrophoric" AND [QRptCBC-3DAll-2].USM="Gal"
UNION ALL
SELECT ProjNumber, 0 AS PS,0 AS PL,USQTY AS PG
FROM [QRptCBC-3DAll-2]
WHERE [QRptCBC-3DAll-2].Hazard="Pyrophoric" AND [QRptCBC-3DAll-2].USM="ft3";

What this query will do is create a row with a total sum at each category if
there is data. So if I have chemicals in two categories, I will get two rows.
So to reduce to one row I created the following query where the subreport
uses as a source.
SELECT Projects.ProjNumber, Sum(nz([QRptCBC-3DAll-15].[PS])) AS PS,
Sum(nz_([QRptCBC-3DAll-15].[PL])) AS PL, Sum(nz([QRptCBC-3DAll-15].[PG])) AS
PG
FROM [QRptCBC-3DAll-15] RIGHT JOIN Projects ON _
[QRptCBC-3DAll-15].ProjNumber = Projects.ProjNumber
GROUP BY Projects.ProjNumber;

The Main Report uses a Query based on the table Projects.

The intended link field between the Master and the Child is ProjNumber.

Thanks for your help.

We still can't see your record sources, sample records, link master/child
properties,... Query is too complex might suggest your link master/child
properties are the same data type. What do you see if you clear out the link
properties entirely?