Hide Subreport and Detail Section if Empty

[David]

Is there a simple way to hide a group header and its detail section if the
subreport in the detail section has no records?
Thanks in advance!
David

 

[Allen Browne]

The simplest way to do this would be to change the query the report is based
on, so as to exclude the records that have no related records in the
subreport's table.

You could do that with an INNER JOIN, or with a subquery. This query example
excludes the Customers in Nortwind who have no order:
SELECT Customers.CustomerID FROM Customers
WHERE EXISTS (SELECT Orders.OrderID FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID);

If subqueries are new, see:
http://support.microsoft.com/?id=209066

 

[David]

Thanks, Allen. I was really hoping for something simple like "If NOT
me.srpEmployees.HasData Then me.GroupHeader0.Visible = False", but can't get
reports to do the things forms do!

 

[Allen Browne]

You can set the Visible property of the section in its Format event (or the
runtime properties PrintSection and MoveLayout).

However, the subreport in the detail section is not available at this time,
so to find out if there will be related records you would need to DLookup()
the subform's table.

Once you start messing with showing/hiding sections conditionally, Access
also gets confused about the page count. The events are not fired all the
way through a long report, so you can get weird results like a report
telling you that you are viewing "Page 7 of 5", or even printing the wrong
stuff if you just ask for page 4 without printing pages 2 and 3. Since the
events were not run for those pages, you don't actually finish up with what
should be on page 4 at all.

That's why I said that the simplest way to solve the problem is to suppress
the records from the source of the report.

 

[David]

Thanks for the info. Looks like the record source is the simple way!