ACC2: "#Error" Message Referencing Subreport Values

• Microsoft Access version 2.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

Referencing a subreport control from a main report when the subreport does not return any records results in a "#Error" error message.

CAUSE

This error occurs because the subreport does not return any values.

RESOLUTION

Make sure that a subreport referenced in a main report returns values. Alternatively, you can use an Access Basic function to retrieve values from the subreport and trap for the error should it occur. See the "Steps to Solve Problem" section later in this article for an example of this technique.

STATUS

This behavior no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. Create a new, blank query based on the Customers table.

3. Drag the Customer ID and Company Name fields from the field list to the query grid.

4. In the criteria cell for the Company Name column, enter the following expression:

   Like "P*"

5. Save the query as Sorter.

6. Create a new report based on the Orders table. This report will be the subreport.

7. Drag the Freight field from the field list to the report's Detail section.

8. In the report's footer section, add a text box with the following properties:

   ControlSource: =Sum([Freight]) Name: Sum Freight

9. Save the report as SubRpt.

10. Create a new report based on the Sorter query. This report will be the main report.

11. From the Format menu, turn off the Page Header/Footer command. From the View menu, choose Sorting And Grouping. In the Field/Expression box, select Customer ID, and then set the Group Header property to Yes.

12. Drag the Customer ID field from the field list to the main report's Customer ID header section.

13. Drag the SubRpt report from the Database window to the detail section of the main report. Below the new subreport, add a label control with the caption "Total Order Amount Per Customer:" (without quotation marks) and a text box containing the following formula:

    =([SubRpt].Report![Sum Freight])

14. Set the subreport's LinkMasterFields and LinkChildFields properties to [Customer ID].

15. Save the main report as MainRpt.

16. Preview the main report. Note that in the record for company Paris Specialties, the error message "#Error" is displayed for the subreport reference ([SubRpt].Report![Sum Freight]).

Steps to Solve Problem

1. Create the a new Access Basic function called ErrAvoid():

         Function ErrAvoid (n As Variant)
            On Error GoTo Trap
            ErrAvoid = n
            Exit Function
         Trap:
            ErrAvoid = 0
            Resume Next
         End Function
   
   

2. Replace "=([SubRpt].Report![Sum Freight])" in step 13 of the previous procedure with:

         =ErrAvoid([SubRpt].Report![Sum Freight])
   

3. Preview the main report. Note that in the record for company Paris Specialties, $0.00 is now displayed instead of the "#Error" message. The ErrAvoid() function traps for errors caused by the subreport containing no values. When an error is encountered, a value of zero is assigned.