ACC2000: Error with Linked Crosstab Subform or Subreport

Article translations Article translations
Article ID: 209218 - View products that this article applies to.
This article was previously published under Q209218
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

On This Page

Symptoms

When you print or preview a report, you may receive the following error message:
You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport.

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. For example, the following steps show how to fix the problem created in the "Steps to Reproduce Behavior" section of this article:
  1. Change the Field in the Xtab query (created in step 1 of the "Steps to Reproduce Behavior" section) from ShipVia to Shipper: "Shipper " & [ShipVia]
  2. On the View menu, click Properties.
  3. In the Column Headings box, type "Shipper 1","Shipper 2", "Shipper 3"
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

  1. Open the sample database Northwind.mdb.
  2. 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
    
       Field: ShipVia
          Total: Group By
          Crosstab: Column Heading
    
       Field: Freight
          Total: Sum
          Crosstab: Value
    					
  3. Use the Report Wizard to create a new report based on the Xtab query. Include all the query fields in your report. Save the report as XtabSub.
  4. 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.
  5. 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).
  6. In Design view of the main report, view the property sheet for the XtabSub report. Verify that the LinkChildFields and LinkMasterFields properties are set to EmployeeID.
  7. Preview or print the XtabMain report. Note that you receive the error message mentioned in the "Symptoms" section.

References

For more information about the ColumnHeadings property, click Microsoft Access Help on the Help menu, type ColumnHeadings property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209218 - Last Review: October 26, 2013 - Revision: 2.0
Applies to
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbnosurvey kbarchive kbdta kberrmsg kbprb KB209218

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com