PRB: "#ERROR" Message When Referencing Subreport Controls(2.0)
  
PSS ID Number: Q102521
Article last modified on 12-08-1995
 
1.00 1.10 2.00
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access versions 1.0, 1.1, 2.0
---------------------------------------------------------------------
 
SYMPTOMS
========
 
Moderate: Requires basic macro, coding, and interoperability skills.
 
Referencing a subreport control from a main report when the subreport
does not return any matching records results in a "#ERROR" message for the
referenced subreport control.
 
RESOLUTION
==========
 
Make sure that any subreport fields referenced in main reports have values
associated with them by adding a function to test for this condition. For
more information, see the "Steps to Work Around Behavior" section later in
this article.
 
STATUS
======
 
This behavior is by design.
 
MORE INFORMATION
================
 
Steps to Reproduce Behavior
---------------------------
 
1. Start Microsoft Access and open the sample database NWIND.MDB.
 
2. Create a new query based on the Customers table. Drag the Company Name
   field from the field list to the query grid. Type the following in the
   Criteria field for the column:
 
      Like "P*"
 
   Drag the Customer ID field from the field list to the next column on
   the query grid. Save the query as Sorter. Note that this query will
   limit the recordset to only customer records beginning with the letter
   "P", and includes a customer with no orders.
 
3. Create a new subreport based on the Orders table; call it SubRpt.
 
4. Drag the Freight field to the detail section of the subreport. In the
   report footer section, add a text box with the following formula
   as the ControlSource property:
 
      =Sum([Freight])
 
   Set the ControlName property of the text box to ToTFreight. The
   subreport will print the detailed freight items with a grand total for
   all freight charges.
 
5. Create a new report called Main Report based on the Sorter query.
 
6. From the View menu, choose Sorting And Grouping. In the dialog box,
   select Company Name from the Field/Expression combo box and set the
   Group Header property to Yes.
 
7. Drag the Company Name field to the new Company Name header
   section.
 
8. Drag the SubRpt subreport from the Database window to the detail
   section of Main Report. Below the subreport, add a label control
   with the caption "Total Order Amount Per Customer:" and a text box
   control with the ControlSource property set to the following formula:
 
      =([SubRpt].Report![ToTFreight])
 
   Set both the LinkMasterFields and the LinkChildFields properties
   for the subreport control to [Customer ID]. Setting these properties
   filters the subreport to display the Freight charges per customer.
 
9. Preview the report.
 
Note that Parisian Specialties (in version 1.x), or Paris Specialites (in
version 2.0), which has no orders, displays "#ERROR" for the referenced
subreport control.
 
Steps to Work Around Behavior
-----------------------------
 
1. Create the following new Access Basic function called ErrAvoid():
 
      Function ErrAvoid (n As Variant)
         On Error GoTo Trap
            ErrAvoid = n
            Exit Function
         Trap:
            ErrAvoid = 0
            Exit Function
      End Function
 
2. Replace "=([SubRpt].Report![ToTFreight])" line in step 8 of the "Steps
   to Reproduce Behavior" section  with the following line:
 
       =ErrAvoid([SubRpt].Report![ToTFreight])
 
3. Preview the report.
 
Note that Parisian Specialties, or Paris Specialites, now shows $0.00
dollars in sales, instead of "#ERROR." The ErrAvoid() function traps for
errors that are caused by the subreport control containing no values. When
an error is encountered, "Object Has No Value," then a value of zero, is
assigned to the main report control.
 
Additional reference words: 1.00 1.10 2.00 pounderror pounderr link master
KBCategory: kbusage
KBSubcategory: RptSub
=============================================================================
Copyright Microsoft Corporation 1995.
