Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

728

Avoiding empty reports

If Access fails to find valid records to insert into the Detail section of a report, all you’ll see is

#Error in the Detail section when the report is printed. To avoid this problem, attach code to the

report’s Open event that checks for valid records and sets a flag to cancel the print event if no

records are found.

The NoData event is triggered when Access tries to build a report and finds no data in the report’s

underlying recordset. Using NoData is easy:

Private Sub Report_NoData(Cancel As Integer)

MsgBox “ There are no records for this report.”

Cancel = True

End Sub

The Cancel = True statement instructs Access to stop trying to open the report. The user will see

the dialog box shown in Figure 20.23 and will avoid getting a report that can’t be printed. (Open

rptEmpty in Chapter20.accdb for this example.)

FIGURE 20.23

Better than #Error in all the text boxes in the report!

Because the NoData event is tied to the report itself, don’t look for it in any of the report’s sec-

tions. Simply add this code as the report’s NoData event procedure and your users will never

encounter a report full of #Error messages.

Avoiding null values in a tabular report

Null values in reports can cause errors, particularly when the field containing the null value is part

of an expression in another control on the report. Instead of simply ignoring the null value and the

resulting errors, you may decide that forcing a zero into the field is preferable.

The following expression in a numeric field’s ControlSource property will solve this problem.

In this expression, the field is contained in a text box named txtField:

=IIf(IsNull([Field]),0,[Field])

This immediate If statement sets the value of txtField to 0 if the value of Field (the data) is

null; otherwise, txtField is set to the value of Field.