Reports in Macros - Do not run if no records

[EddiRae]

Hello,
I am using Access 2007 and I have a macro that currently has seven reports. Some times there isn't any data for the reports. For those, I don't want to have them run at all.

I am not sure how to use the "Condition" expressions in this instance.

Any suggestions?

Thanks!!
Eddi Rae

 

[EddiRae]

I found a way to do this.
I used a command button and then scripted it to determine what to run. Here is the code

Dim db As Database
Dim rs As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb

'Determine if there are letters for New Cat 1
Set qdf = db.QueryDefs("qryLISLetterNewCat1")
Set rs = qdf.OpenRecordset()
If rs.EOF Then
Else
DoCmd.RunMacro "mcrLISLetterNewCat1"
End If
rs.Close
qdf.Close
db.Close

 

[boblarson]

You could do it even easier. Just put this in the report's On No Data event:

EDIT: Paul's post below refreshed me - don't know why I forgot that simple one.

Cancel = True

And then add an error handler to your event that opens the report to handle the error 2501 which will occur because of the cancelation:

On Error GoTo Err_handler
 
'....everything else here
 
EvExit:
   Exit Sub
 
Err_handler:
  If Err.Number <> 2501 Then
     Msgbox Err.Description, vbInformation, "Error #: " & Err.Number
  End If
  Resume EvExit
  Resume

 

[pbaldy]

FYI, the "usual" method for this type of thing is including

Cancel = True

in the No Data event of the report. This does require error trapping in the code that opened the report, which would mean you'd want to use VBA code rather than a macro. It would be more efficient than opening 7 recordsets.

 

[boblarson]

And the benefits of mine over yours is that you aren't having to create objects to check, nor do you have to do a check first. No extra memory is used, etc.