Hide Subreport if no data

[m stroup]

[rptTestPts] report
[hdrTestPts] group header
[txtTestPtID] data grouped on this field and this is linked
to subform
[lblWeight] label
[dtlTestPts] detail section
[TxtWeight] text box
[sfrmTestPts] subform
[dtlPts] detail section of subform
[txtTestPtID] field linked to frm
[txtWeight] text box

If there is no record in the subform detail section [dtlPts], I would like
to hide
that instance of [hdrTestPts] and [dtlTestPts].

Any thoughts?

Teach me to fish! Thanks for the help.
Pax, M

 

[Allen Browne]

If I understand you correctly, you want to eliminate the dtlTestPts records
from the main report if they have no match in the subreport's query?

You may be able to do that by adding a subquery to the WHERE clause of the
main report's query. For example:
SELECT * FROM Table1
WHERE EXISTS
(SELECT txtTestPtlID FROM Table2
WHERE Table2.TestPtlID = Table1.TestPtlID);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

An alternative approach would be to create a query that gives just ONE
matching record from the related table:
SELECT DISTINCT TestPtlID
FROM Table2
WHERE TestPtlID Is Not Null
Then create another query, and inner join this one to it. This will also
eliminate the records in Table1 that don't have any match in Table2.

 

[NTC]

AB, could one use a HasData method ?? pros/cons?

If I understand you correctly, you want to eliminate the dtlTestPts records
from the main report if they have no match in the subreport's query?

You may be able to do that by adding a subquery to the WHERE clause of the
main report's query. For example:
SELECT * FROM Table1
WHERE EXISTS
(SELECT txtTestPtlID FROM Table2
WHERE Table2.TestPtlID = Table1.TestPtlID);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

An alternative approach would be to create a query that gives just ONE
matching record from the related table:
SELECT DISTINCT TestPtlID
FROM Table2
WHERE TestPtlID Is Not Null
Then create another query, and inner join this one to it. This will also
eliminate the records in Table1 that don't have any match in Table2.

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

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

[rptTestPts] report
[hdrTestPts] group header
[txtTestPtID] data grouped on this field and this is linked
to subform
[lblWeight] label
[dtlTestPts] detail section
[TxtWeight] text box
[sfrmTestPts] subform
[dtlPts] detail section of subform
[txtTestPtID] field linked to frm
[txtWeight] text box

If there is no record in the subform detail section [dtlPts], I would like
to hide
that instance of [hdrTestPts] and [dtlTestPts].

Any thoughts?

Teach me to fish! Thanks for the help.
Pax, M

 

[m stroup]

Thanks Allen,

I have used subqueries before, but I had a real brain block. Thanks for the
tip. It is working fine now.
--
Teach me to fish! Thanks for the help.
Pax, M

If I understand you correctly, you want to eliminate the dtlTestPts records
from the main report if they have no match in the subreport's query?

You may be able to do that by adding a subquery to the WHERE clause of the
main report's query. For example:
SELECT * FROM Table1
WHERE EXISTS
(SELECT txtTestPtlID FROM Table2
WHERE Table2.TestPtlID = Table1.TestPtlID);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

An alternative approach would be to create a query that gives just ONE
matching record from the related table:
SELECT DISTINCT TestPtlID
FROM Table2
WHERE TestPtlID Is Not Null
Then create another query, and inner join this one to it. This will also
eliminate the records in Table1 that don't have any match in Table2.

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

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

[rptTestPts] report
[hdrTestPts] group header
[txtTestPtID] data grouped on this field and this is linked
to subform
[lblWeight] label
[dtlTestPts] detail section
[TxtWeight] text box
[sfrmTestPts] subform
[dtlPts] detail section of subform
[txtTestPtID] field linked to frm
[txtWeight] text box

If there is no record in the subform detail section [dtlPts], I would like
to hide
that instance of [hdrTestPts] and [dtlTestPts].

Any thoughts?

Teach me to fish! Thanks for the help.
Pax, M

 

[Allen Browne]

It's much better to elminate the records you don't want, rather than
programmatically suppress their display.

If you merely suppress the display of the data (e.g. with an IIf()
expression), things like the count of records, averages, totals, etc. will
be wrong.

If you suppress the entire section (e.g. setting PrintSection and MoveLayout
to False), you get inconsistent page layouts (depending if you print some or
all pages), and nonsense results such as "Page 11 of 9." These problems
occur due to the way the events fire only for the pages that are actually
shown.

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

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

AB, could one use a HasData method ?? pros/cons?

If I understand you correctly, you want to eliminate the dtlTestPts
records
from the main report if they have no match in the subreport's query?

You may be able to do that by adding a subquery to the WHERE clause of
the
main report's query. For example:
SELECT * FROM Table1
WHERE EXISTS
(SELECT txtTestPtlID FROM Table2
WHERE Table2.TestPtlID = Table1.TestPtlID);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

An alternative approach would be to create a query that gives just ONE
matching record from the related table:
SELECT DISTINCT TestPtlID
FROM Table2
WHERE TestPtlID Is Not Null
Then create another query, and inner join this one to it. This will also
eliminate the records in Table1 that don't have any match in Table2.

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

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

[rptTestPts] report
[hdrTestPts] group header
[txtTestPtID] data grouped on this field and this is
linked
to subform
[lblWeight] label
[dtlTestPts] detail section
[TxtWeight] text box
[sfrmTestPts] subform
[dtlPts] detail section of subform
[txtTestPtID] field linked to frm
[txtWeight] text box

If there is no record in the subform detail section [dtlPts], I would
like
to hide
that instance of [hdrTestPts] and [dtlTestPts].

Any thoughts?

Teach me to fish! Thanks for the help.
Pax, M