Close if No Data for Report

[Steve S]

Is there a way to have a report immediately close (or never open) if there is
no data for the report? I have tried to put DoCmd.Close in the 'No
data'event but get an error message?

Also have tried some very convoluted IF...THEN...ELSE which is just a mirror
image of the sql logic in the record source of the report. seems like a
duplication of effort.

any ideas?

 

[Jeanette Cunningham]

Steve,

You can use the Report's OnNoData event for this.
For example, the following code

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data found! Closing report."
Cancel = True
End Sub

will automatically close the report if there are no
records in the underlying source.


However, if you're opening the report from code behind
a form, you need to handle the error that's generated as
a result.


Private Sub TestNoData_Click()
On Error Resume Next
DoCmd.OpenReport "SomeReport", acViewPreview
If Err = 2501 Then
Err.Clear
End If
End Sub


Jeanette Cunningham

 

[Steve S]

Thank you. That worked great although I did not seem to need the error
handling.

 

[Steve S]

Well almost great. Here is th code in form A that opens the report on the
open event for form A.

If Forms!menu2![Twirl Order] = 3 Then
MsgBox Forms!menu2![Twirl Order]
On Error Resume Next
DoCmd.OpenReport "VerifyStartLetter", acViewPreview
If Err = 2501 Then
Err.Clear
End If
End If

the problem now is that when the report does open it is BEHIND Form A. How
do i get it to bo on top. I have looked at the list of events for a report
and do not see anything that would seem to work.

thanks for the help.

 

[Jeanette Cunningham]

Steve,
as you want the form to appear after the report is closed, there are a
couple of options.
You can hide the form after the report opens, and then show it again after
the report closes.
To hide the form
Forms!menu2.Visible = False

To show the form
Forms!menu2.Visible = True


Jeanette Cunningham

Well almost great. Here is th code in form A that opens the report on the
open event for form A.

If Forms!menu2![Twirl Order] = 3 Then
MsgBox Forms!menu2![Twirl Order]
On Error Resume Next
DoCmd.OpenReport "VerifyStartLetter", acViewPreview
If Err = 2501 Then
Err.Clear
End If
End If

the problem now is that when the report does open it is BEHIND Form A.
How
do i get it to bo on top. I have looked at the list of events for a
report
and do not see anything that would seem to work.

thanks for the help.

Steve,

You can use the Report's OnNoData event for this.
For example, the following code

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data found! Closing report."
Cancel = True
End Sub

will automatically close the report if there are no
records in the underlying source.


However, if you're opening the report from code behind
a form, you need to handle the error that's generated as
a result.


Private Sub TestNoData_Click()
On Error Resume Next
DoCmd.OpenReport "SomeReport", acViewPreview
If Err = 2501 Then
Err.Clear
End If
End Sub


Jeanette Cunningham