Web Development

Ask something
Discussion Locked

Access auto close a report with no data

0 helpful
3 replies

by d_bones

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Easy one for you clever people but it's driving me mad.

If yo create an "OnNoData" event procedure for a report and the enter the event as DoCmd.Close "repName". You get a debug error.

How can you not display a report wih no data when using

DoCmd.OpenReport "repName", acViewPreview

All help gratefully received. As usual

3 replies

by techrep

The usual way is to test for an empty recordset in the Report_Open section and exit if the count is zero. You can do this with the Dcount() function or by opening a recordset using the SQL statement that is used as the RecordSource for your report. For example:

In the Report_Open section, include these lines:

Dim cnt as Long

cnt = DCount("*", "Orders", "[ShipRegion] = 'CA'")
If cnt = 0 Then
'Setting cancel = true will cause the report to close
Cancel = True
Exit Sub
End If

'If the cnt is > zero the report will continue here.

Here is an example using a recordset:

Dim Rs as Recordset, SQL as String

SQL = "Select * from Orders where [ShipRegion] = 'CA'"
Set Rs = Db.Openrecordset(SQL)

If Rs.Eof Then
Cancel = True
Exit Sub
End If

This discussion is closed to new replies.