Microsoft KB Archive/114513

= ACC2: "#Error" Message Referencing Subreport Values =

Article ID: 114513

Article Last Modified on 6/25/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q114513



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



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



Steps to Reproduce Behavior
 Open the sample database NWIND.MDB. Create a new, blank query based on the Customers table. Drag the Customer ID and Company Name fields from the field list to the query grid. In the criteria cell for the Company Name column, enter the following expression:

Like "P*"  Save the query as Sorter.</li> Create a new report based on the Orders table. This report will be the subreport.</li> Drag the Freight field from the field list to the report's Detail section.</li>  In the report's footer section, add a text box with the following properties:

<pre class="fixed_text">      ControlSource: =Sum([Freight]) Name: Sum Freight </li> Save the report as SubRpt.</li> Create a new report based on the Sorter query. This report will be the main report.</li> 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.</li> Drag the Customer ID field from the field list to the main report's Customer ID header section.</li>  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:

<pre class="fixed_text">      =([SubRpt].Report![Sum Freight]) </li> Set the subreport's LinkMasterFields and LinkChildFields properties to [Customer ID].</li> Save the main report as MainRpt.</li> 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]).</li></ol>

Steps to Solve Problem
<ol>  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 </li>  Replace "=([SubRpt].Report![Sum Freight])" in step 13 of the previous procedure with:

<pre class="fixed_text">     =ErrAvoid([SubRpt].Report![Sum Freight]) </li> 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.</li></ol>

Additional query words: pounderror

Keywords: kberrmsg kbprb kbusage KB114513

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.