Microsoft KB Archive/102521

From BetaArchive Wiki

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.