Close report on No data.....Complication! (1 Viewer)

[Franky G]

HI,

I have a few reports which I want to close when there is no data. I'm using this code;

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data in Report", vbInformation, "Report is cancelled"
Cancel = True
End Sub

Problem is that I have fields in the report such as the present date and the dates that the report covers. These are in the report header and page footer and will allways return data, and I'm sure they prevent the report closing.

Is there any way I can avoid this happening?

thx,

Franky

 

[Atomic Shrimp]

The way I would handle this is only to call the report if there's going to be something in it, so some code like this perhaps:

Private Sub cmdPrintReport_Click()
Dim Db As Database
Dim rstTmp As Recordset
Dim strSQl As String

Set Db = CurrentDb()

strSQl = "[NB the same as the recordsource of your report]"
Set rstTmp = Db.OpenRecordset(SqlStr, dbOpenSnapshot)

'get an accurate recordcount:
On Error Resume Next
rstTmp.MoveLast
rstTmp.MoveFirst
On Error GoTo 0

If rstTmp.RecordCount > 0 Then
DoCmd.OpenReport "YourReportName", acViewNormal
MsgBox "Report printed"
Else
MsgBox "No data to report"
End If

Set Db = Nothing

End Sub

If the recordsource for your report is a saved query (as opposed to SQL pasted directly into the Recordsource property), you may be able to simplify it right down to:

Private Sub cmdPrintReport_Click()

If DCount("*", "[YourQueryName]") > 0 Then
DoCmd.OpenReport "YourReportName", acViewNormal
MsgBox "Report printed"
Else
MsgBox "No data to report"
End If

End Sub

(I'm assuming for both of these examples that you are running the report from a command button)

HTH

Mike

[This message has been edited by Mike Gurman (edited 02-08-2001).]