ACC: Error with Linked Crosstab Subform or Subreport
ID: Q104758
|
The information in this article applies to:
-
Microsoft Access versions 1.1, 2.0, 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
When you print or preview a report, you may receive the following error
message.
In Microsoft Access 97 and 7.0:
You can't use a pass-through query or a non-fixed-column crosstab
query as a record source for a subform or subreport.
In Microsoft Access 2.0:
The crosstab query underlying a subform or subreport must have
fixed column headings.
In Microsoft Access 1.1:
Reserved error (-1001); there is no message for this error.
CAUSE
Unless a crosstab query uses fixed column headings, you cannot preview or
print a main report with a bound subreport that is based on a crosstab
query.
RESOLUTION
To work around this behavior, add fixed column headings to the crosstab
query's properties as follows.
In Microsoft Access 97, 7.0 and 2.0:
With the crosstab query in Design view, click Query Properties on
the View menu. In the ColumnHeadings property, type the column
headings for your query.
In Microsoft Access version 1.1:
With the crosstab query in Design view, click Query Properties on
the View menu. In the FixedColumnHeadings property, type the column
headings for your query.
Another workaround is to delete the LinkChildFields and LinkMasterFields
properties of the subreport. You will then be able to run the report;
however, the report will show every record in the subreport.
MORE INFORMATION
Steps to Reproduce Behavior
- Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.1
and 2.0).
- Create the following crosstab query based on the Orders table. Save the
query as Xtab.
Query: Xtab
--------------------
Type: Crosstab Query
Field: EmployeeID
Total: Group By
Crosstab: Row Heading
NOTE: In versions 1.1 and 2.0, there is a space in the Employee ID
field name.
Field: ShipVia
NOTE: In versions 1.1 and 2.0, there is a space in the Ship Via
field name.
Total: Group By
Crosstab: Column Heading
Field: Freight
Total: Sum
Crosstab: Value
- Use the Report Wizard to create a new report based on the Xtab query.
Include all the query fields in your report, and sort by the EmployeeID
field. Save the report as XtabSub.
- Use the Report Wizard to create another new report based on the Xtab
query. Include only the EmployeeID field in this report, and sort by
the EmployeeID field. Save this report as XtabMain.
- Embed the XtabSub report in the detail section of the XtabMain report.
You can do this by dragging the subreport from the Database window to
the detail section of the main report (which must be open in Design
view).
- In Design view of the main report, view the Properties sheet for
the XtabSub report. Verify that the LinkChildFields and
LinkMasterFields properties are set to EmployeeID.
- Preview or print the XtabMain report. Note that you receive the error
message mentioned in the "Symptoms" section.
REFERENCES
For more information about Setting the ColumnHeadings property, search the
Help Index for "ColumnHeadings Property," or ask the Microsoft Access 97
Office Assistant.
Keywords : kberrmsg RptSub
Version : 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb