Have a report not open if no records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that lists all attendance records for a particular student
upon his or her departure from our program.

I would like to program the report *not* to open from the custom form if the
underlying query has no records.

I have tried using If statements to check for a null recordset on the query
and for a "#Error" value for a report textbox control that otherwise prints
the selected student's name at the top of the form when there are attendance
records found.

Either the report continues to open with the error showing, or I get an
"Object required" error but don't know what is causing it. The code does not
break for me to see what line in the sub routine is causing the error.

Thank you in advance for any suggestions.
 
Use the report's NoData event to close the report.

Note that you'll then get an Error 2501 returned to the object that called
the report, telling you that the open report action has been cancelled.
You'll need to trap for that error so that the error message doesn't appear.
 
Thanks Ken,
You guys have been invaluable to me along the long road to developing this
project.
Use the report's NoData event to close the report.
Click to expand...
I understand the concept but could you spot me the syntax to make it happen.
I see the NoData event in the Properties page, but...

Then said:
Note that you'll then get an Error 2501 returned to the object that called the report, telling you that the open report action has been cancelled. You'll need to trap for that error so that the error message doesn't appear.
Click to expand...

Again, this makes sense in principle but could you spot me the syntax for
the form? Something like:

On Error 2501 GoTo Err_cmdRunReport_Click ...?

Thanks again!
 
Answers inline....

--

Ken Snell
<MS ACCESS MVP>

B. Meincke said:
Thanks Ken,
You guys have been invaluable to me along the long road to developing this
project.

I understand the concept but could you spot me the syntax to make it
happen.
I see the NoData event in the Properties page, but...
Click to expand...

You will want to create an Event Procedure for the NoData event. It will
look something like this:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no data for the selected parameters.", vbInformation, "No
Data"
Cancel = True
End Sub


Again, this makes sense in principle but could you spot me the syntax for
the form? Something like:

On Error 2501 GoTo Err_cmdRunReport_Click ...?
Click to expand...

Actually, in this case, you'll want to ignore the error:

On Errur Resume Next
DoCmd.OpenReport "Reportname"
If Err.Number <> 2501 Then
MsgBox "An error has occurred: Error #" & _
Err.Number & " -- " & Err.Description
Exit Sub
End If
 
Back
Top