Close a Report When Sub-Reports are Empty

[Keith Wilby]

Following on from my recent "RecordCount" thread ...

I have an unbound report which contains two subreports. I've used the
method suggested by Al to count the records in the subs but I want the main
report to close if both subs are empty. I've successfully trapped error 440
in various events but each time I use

DoCmd.Close acReport, Me.Name

I get an error saying that the action isn't possible whilst the report is
formatting. Does anyone have a solution?

Many thanks.

Keith.

 

[Arno R]

Following on from my recent "RecordCount" thread ...

I have an unbound report which contains two subreports. I've used the
method suggested by Al to count the records in the subs but I want the main
report to close if both subs are empty. I've successfully trapped error 440
in various events but each time I use

DoCmd.Close acReport, Me.Name

I get an error saying that the action isn't possible whilst the report is
formatting. Does anyone have a solution?

Many thanks.

Keith.

Maybe the other way around ??
Only open the report IF there are records in one of the subs??
(Using DCount or whatever other function)

Or use a timerevent when the formatting is done ??

Arno R

 

[krissco]

Following on from my recent "RecordCount" thread ...

I have an unbound report which contains two subreports. I've used the
method suggested by Al to count the records in the subs but I want the main
report to close if both subs are empty. I've successfully trapped error 440
in various events but each time I use

DoCmd.Close acReport, Me.Name

I get an error saying that the action isn't possible whilst the report is
formatting. Does anyone have a solution?

Many thanks.

Keith.

I believe you can use the ReportActivate event for this purpose:

Private Sub Report_Activate()
If Me.subrptName.Report.someValue < someOtherValue Then
DoCmd.Close
End If
End Sub


-Kris

 

[Stefan Hoffmann]

hi Keith,

I have an unbound report which contains two subreports. I've used the
method suggested by Al to count the records in the subs but I want the
main report to close if both subs are empty. I've successfully trapped
error 440 in various events but each time I use
DoCmd.Close acReport, Me.Name
I get an error saying that the action isn't possible whilst the report
is formatting. Does anyone have a solution?

Use a bound report with a SQL statment like that

SELECT * FROM [yourTable] WHERE 0 = 1

As this will never return a record, the event on no data is called. In
this event, you can count the recordsets of the subreport. If they are 0
then you set Cancel = True and the report will not be printed:

Private Sub Report_NoData(Cancel As Integer)

Dim RecCount As Long

RecCount = DCount(<subreport1>) + DCount(<subreport2>)

Cancel = (RecCount = 0)

End Sub



mfG
--> stefan <--

 

[Keith Wilby]

Maybe the other way around ??
Only open the report IF there are records in one of the subs??
(Using DCount or whatever other function)

Or use a timerevent when the formatting is done ??

Arno R

Thanks Arno, good lateral thinking. And your fee was very reasonable ;-)

Regards,
Keith.

 

[Keith Wilby]

I believe you can use the ReportActivate event for this purpose:

Using this event worked a treat, thanks very much.

Keith.

 

[Keith Wilby]

hi Keith,

Use a bound report with a SQL statment like that

SELECT * FROM [yourTable] WHERE 0 = 1

As this will never return a record, the event on no data is called. In
this event, you can count the recordsets of the subreport. If they are 0
then you set Cancel = True and the report will not be printed:

Private Sub Report_NoData(Cancel As Integer)

Dim RecCount As Long

RecCount = DCount(<subreport1>) + DCount(<subreport2>)

Cancel = (RecCount = 0)

End Sub

Hi Stefan,

Beautiful in its simplicity and I follow the logic but I'm getting an
"argument not optional" run-time error. Here's my code:

Private Sub Report_NoData(Cancel As Integer)

Dim RecCount As Long
RecCount = DCount(Me.srptFailures1) + DCount(Me.srptFailures2) < The
first DCount is highlighted
Cancel = (RecCount = 0)

End Sub

Any clues? I'm guessing I'm using the wrong method to reference my subs.

Many thanks.

Keith.

 

[Keith Wilby]

Hi Stefan,

Beautiful in its simplicity and I follow the logic but I'm getting an
"argument not optional" run-time error. Here's my code:

Private Sub Report_NoData(Cancel As Integer)

Dim RecCount As Long
RecCount = DCount(Me.srptFailures1) + DCount(Me.srptFailures2) < The
first DCount is highlighted
Cancel = (RecCount = 0)

End Sub

I have it working now but using the "conventional" DCount notation below
(I've never seen DCount used your way before and I'm guessing that I'm
misunderstanding you).

lngRecCount = DCount("EmployeeNumber", "qrptFailures", strSQL1) +
DCount("EmployeeNumber", "qrptFailures", strSQL2)

 

[Stefan Hoffmann]

hi Keith,

I have it working now but using the "conventional" DCount notation below
(I've never seen DCount used your way before and I'm guessing that I'm
misunderstanding you).

The <> should indicate, that you have to insert here the correct
parameters as you have done using the normal DCount() parameter list.

Sorry for that, seems it wasn't obvious.

mfG
--> stefan <--

 

[Keith Wilby]

hi Keith,

The <> should indicate, that you have to insert here the correct
parameters as you have done using the normal DCount() parameter list.

Sorry for that, seems it wasn't obvious.

No probs Stefan, it was me having a "duh" moment Thanks again for the
tip.

Keith.