Microsoft KB Archive/104758
Article ID: 104758
Article Last Modified on 1/18/2007
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q104758
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:
In Microsoft Access 2.0:
In Microsoft Access 1.1:
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.
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.
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.
For more information about Setting the ColumnHeadings property, search the Help Index for "ColumnHeadings Property," or ask the Microsoft Access 97 Office Assistant.
Additional query words: you can t use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport
Keywords: kberrmsg kbprb KB104758