sub-report problem

[SAL]

Hello,
I'm going to try to explain this the best I can...
I have a report that has a sub-report embedded in it which has a sub-report
embedded in it.
With the main report, I filter the report with user input. The report looks
fine and the sub-reports look good too except for one thing:
the first sub-report is not including all of the related records--but the
query does.
So, in my main report it has a field called InspectionId and so does the
sub-report. This is the Master/Child link fields.
The query returns the following when I use:
Where InspectionId=340

qryInspectionObjects2 InspectionID FacilityObjectID SiteObjectID
Compliance FacilityObject
340 5 1301 Yes Catch Basin
340 5 1302 Yes Catch Basin
340 5 1303 Yes Catch Basin
340 5 1305 Yes Catch Basin
340 5 1308 No Catch Basin
340 15 1304 Yes CONTECH StormFilter (leaf compost or ZPG filters)
340 20 1300 Yes Access Road or Easement
340 21 1306 Yes Drywell


But the report is dropping off four of the Catch Basin items. As far as I
know, I'm not filtering this sub-report except by using the Master/Child
Link field stated above.
Can anyone give me any clues as to why the report is dropping off those
records while the query is not?

Any help is way appreciated. I've beat my head against the wall on this...

SAL

 

[strive4peace]

Hi Sal,

is CanGrow for the subreport control set to Yes?

"As far as I know, I'm not filtering this sub-report except by using the
Master/Child Link field ..."

open the design view of the subreport and check the report Filter property

is there any code behind the subreport?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[SAL]

Hi Crystal.
No code behind for the subreport and it's filter property is set to no.

S

 

[strive4peace]

Hi Sal,

what is the SQL for the RecordSource of the subreport?

did you check the CanGrow property of the subreport control?

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[SAL]

SELECT InspectionObjects.InspectionID, InspectionObjects.FacilityObjectID,
InspectionObjects.SiteObjectID, InspectionObjects.Compliance,
FacilityObjects.FacilityObject
FROM InspectionObjects INNER JOIN FacilityObjects ON
InspectionObjects.FacilityObjectID = FacilityObjects.FacitityObjectID
WHERE ((Not (InspectionObjects.InspectionID) Is Null))
ORDER BY InspectionObjects.InspectionID, InspectionObjects.FacilityObjectID,
InspectionObjects.SiteObjectID;

When I change the where clause to an InspectionId, as noted below, to 340,
it returns all of the records as expected.

I'm sorry, I did check CanGrow property and it's set to Yes.

S

 

[strive4peace]

Hi Sal,

email me your database. Specify the report name to look at. Put
'sub-report problem' in the subject line

strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Hi Steve,

got your database. Here is the problem:

you have 8 detail records, but only 4 unique FacilityObject values ... 5
are for FacilityObject = "Catch Basin"

because you are putting the information in the FacilityObjectID group
header section, only one of the five values is showing up -- move the
controls to the detail section of your report and you will see them all
<smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[SAL]

Nice,
Thanks Crystal. I only do Access stuff on a very limited basis any more and
I'm just not up to snuff...

S

 

[strive4peace]

no problem, that is why we are here

you're welcome, Steve happy to help

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*