ACC2: "#Error" Message Referencing Subreport Values

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: 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.