ACC: "#ERROR" Message When Referencing Subreport Controls 95/97
ID: Q138906
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
If you reference a subreport control from a main report when the subreport
does not return any matching records, you may receive a "#ERROR" message
for the referenced subreport control.
NOTE: This article explains a technique demonstrated in the sample
files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0)
and RptSmp97.exe (for Microsoft Access 97). For information about how
to obtain these sample files, please see the following articles in the
Microsoft Knowledge Base:
Q145777 ACC95: Microsoft Access Sample Reports Available in Download Center
Q175072 ACC97: Microsoft Access 97 Sample Reports Available in Download Center
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 referenced in a main report contain data by
adding a function to test for this condition. See the "Steps to Reproduce
Behavior" and "Steps to Work Around Behavior" sections later in this
article for an example of such a function.
STATUS
This behavior is by design.
MORE INFORMATION
Steps to Reproduce Behavior
- Start Microsoft Access and open the sample database Northwind.mdb.
- Create a new query based on the Customers table. Drag the CompanyName
field from the field list to the query grid. Type the following line
in the Criteria field for the column:
Like "P*"
Drag the CustomerID field from the field list to the next column on
the query grid. Save the query as Sorter. Note that this query limits
the recordset only to customer records beginning with the letter
"P" and includes a customer with no orders.
- Create a new subreport 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 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.
- Create a new report based on the Sorter query and name it Main Report.
- On the View menu, click Sorting And Grouping. Click CompanyName in the
Field/Expression box and set the GroupHeader property to Yes.
- Drag the CompanyName field to the new CompanyName header section.
- Drag the SubRpt subreport from the Database window to the detail
section of Main Report. Below the subreport, add a label and set
its Caption property as follows:
Total Order Amount Per Customer:
- Add a text box and set its ControlSource property as follows:
=([SubRpt].Report![TotFreight])
- 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.
- Preview the report. Note that "#ERROR" is displayed for the references
to the subreport control for Paris Specialties, which has no orders.
Steps to Work Around Behavior
To test that subreport fields referenced in a main report contain data,
follow these steps:
- Replace the line
=([SubRpt].Report![TotFreight])
in step 9 above with the following line
=IIF([SubRpt].Report.HasData=True, [SubRpt].Report![TotFreight], 0)
- Preview the report.
Note that a zero value is displayed for Paris Specialties instead of the
"#ERROR" message. The HasData property of the subreport returns True or
False. You can use this property to eliminate the "#ERROR" message by
returning a zero if the HasData property returns a False.
REFERENCES
For more information about the HasData property, search the Help Index for
"HasData Property," or ask the Microsoft Access 97 Office Assistant.
Additional query words:
pound error
Keywords : kberrmsg RptSub
Version : 7.0 97
Platform : WINDOWS
Issue type : kbprb