Microsoft KB Archive/288251

= ACC2002: &quot;#ERROR&quot; Message When You Reference Subreport Controls =

Article ID: 288251

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q288251



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 208718.



SYMPTOMS
When you reference a subreport control from a main report, if the subreport does not return any matching records, you may receive the following message


 * 1) ERROR

for the referenced subreport control.



CAUSE
When the subreport has no data, it is not available. Referencing an empty subreport results in an invalid operation.



RESOLUTION
Make sure that subreport fields that are referenced in a main report contain data by adding a function to test for this condition. See the &quot;Steps to Reproduce the Behavior&quot; and the &quot;Steps to Work Around the Behavior&quot; sections later in this article for an example of such a function.



Steps to Reproduce the Behavior
 Start Microsoft Access, and then open the sample database Northwind.mdb.  Create the following query based on the Customers table:   Query: Sorter ---  Type: Select Query

Field: CompanyName Criteria: Like &quot;P*&quot;

Field: CustomerID Save the query as Sorter. Note that this query limits the recordset to customer records that begin with the letter &quot;P&quot; and includes a customer with no orders.  Create a new report based on the Orders table and name it SubRpt.  Drag the Freight field to the detail section of the subreport. In the report footer section, add a text box, and then set the control's properties as follows:   ControlSource: =Sum([Freight]) ControlName: TotFreight The subreport prints the detailed freight items with a grand total for all freight charges. </li> Create a new report based on the Sorter query and name it Main Report.</li> On the View menu, click Sorting And Grouping. Click CompanyName under the Field/Expression box, and then set the GroupHeader property and the GroupFooter property to Yes.</li> Drag the CompanyName field to the new CompanyName header section.</li> Drag the SubRpt subreport from the Database window to the detail section of Main Report.</li> Add a text box to the CompanyName footer and set its ControlSource property to =([SubRpt].[Report]![TotFreight]). Set the Caption property of the label to Total Order Amount Per Customer.</li> Set both the LinkMasterFields and the LinkChildFields properties for the subreport control to CustomerID. Setting these properties filters the subreport to display the Freight charges for each customer.</li> Preview the report. Note that &quot;#ERROR&quot; is displayed for the references to the subreport control for Paris Specialties, which has no orders.</li></ol>

Steps to Work Around the Behavior
To test that subreport fields that are referenced in a main report contain data, follow these steps: <ol>  Replace the line <pre class="fixed_text">  =([SubRpt].[Report]![TotFreight]) in step 9 above with the following line: <pre class="fixed_text">  =IIF([SubRpt].Report.HasData=True, [SubRpt].Report![TotFreight], 0) </li> Preview the report.</li></ol>

Note that a zero value is displayed for Paris Specialties instead of the &quot;#ERROR&quot; message. The HasData property of the subreport returns True or False. You can use this property to eliminate the &quot;#ERROR&quot; message by returning a zero if the HasData property returns a False.

<div class="references_section">