Forms without data

[Maker1]

I have a database in Access 2000. The people useing it will occasionaly run a report which has no data. Is there a way for the database to recognize that and put up a MsgBox instead of displaying the report with the #Error message?

 

[stix4t2]

You can use a DCOUNT command to count the records before showing the report.

 

[Maker1]

So...if Dcount=0 then pop the message box? Where would you put that in the events?

 

[stix4t2]

How are the reports being opened?

 

[Maker1]

VBA tied to OnClick on a button on a form.

 

[fneily]

You could create a Macro, such as NoData. In the first Action row, select MsgBox. Set the bottom parameters with your message.
In the second Action Row, select the CancelEvent.
Save the Macro.
Open your report in design view, select the Report (top left corner is a little grey square. Click in it and a black square should appear). Bring up the property sheet for the report. Click the Event tab, click the line next to On No Data. Click the dropdown arrow and select your Macro (NoData)

 

[stix4t2]

Test in the OnClick event

if the report is based on a query then run this code

if( DCOUNT("*", "ReportQry") > 0 )then
docmd.openreport ...
else
msgbox "Report has no records"
end if

I can provide an example if you are using a sql statement or built the report query in the query statement.

 

[Maker1]

Great! Thanks for the help.