How do I make the sub-report disappear if it has no data?

[Keith Howard]

I created a report "CustomerAndOrders" aiming to see a list of all Customers
(Master) and their corresponding (completed and paid) orders in the
sub-report.

Some customers do not have nay such entries yet. The report therefore
displays a blank page for them while showing all completed & paid orders for
others.

I wish the report could be dynamic where the sub-report could be made
visible = false where there is no corresponding transactions.

 

[Duane Hookom]

This is a bit unusual since generally people want a subreport to appear even
if it doesn't have related records. Are you sure your subreport is
displaying? I expect only the Master record is displaying. If this is the
case, you may need to modify the record source of the main report so you can
filter out customer with no orders.

 

[Keith Howard]

Hi Duane,

Let me be more clear about what I need.

At present my report says:

Customer 1:
---Transactions
------1
------n

Customer 2:
---Transactions

Customer 3
---Transactions
------1
------n

The following is what I want:
Customer 1
---Transactions
------1
------n

Customer 2

Customer 3
---Transactions
------1
------n


I hope this clearifies.

Regards

 

[Duane Hookom]

What is:
---Transactions

Is this a subreport control or textbox or label or what?

 

[Amicron]

Just use the OnNoData event and set the section(s) visible property to
FALSE.

Richard Rost
http://www.AccessLearningZone

 

[Keith Howard]

List of Transactions is a sub-report.

Regards

Hi Duane,

Let me be more clear about what I need.

At present my report says:

Customer 1:
---Transactions
------1
------n

Customer 2:
---Transactions

Customer 3
---Transactions
------1
------n

The following is what I want:
Customer 1
---Transactions
------1
------n

Customer 2

Customer 3
---Transactions
------1
------n


I hope this clearifies.

Regards

 

[Keith Howard]

I tries the following event but it doesn't work. I can see an empty box after
many customers.

Private Sub Report_NoData(Cancel As Integer)
Report_Transactions.Visible = False
End Sub

Regards

 

[Duane Hookom]

I still find it hard to believe that a bound subreport that contains no data
displays in the main report. I have never heard of this. It would be
interesting if you place a text box in the Report Header section of the
subreport with a control source of:
=Count(*)
to see the value.

Expanding on Amicron's suggest you would have code in the On Format of the
main report's section containing the subreport like:

Me.subrptTransactions.Visible = Me.subrptTransactions.Report.HasData

I still think this isn't necessary.