ACC: Error with Linked Crosstab Subform or Subreport

• 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

You cannot preview or print a main report with a bound subreport that is based on a crosstab query unless the crosstab query uses fixed column headings.

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.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.1 and 2.0).

2. Create a crosstab query based on the Orders table. Save the query as Xtab.

         Query: Xtab
         --------------------
         Type: Crosstab Query
   

         Field: EmployeeID
   

         NOTE: In versions 1.1 and 2.0, there is a space in Employee ID.
   

            Total: Group By
            Crosstab: Row Heading
         Field: ShipVia
   
         NOTE: In versions 1.1 and 2.0, there is a space in Ship Via.
   
            Total: Group By
            Crosstab: Column Heading
         Field: Freight
            Total: Sum
            Crosstab: Value
   
   

3. Using the Report Wizard, 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.

4. Using the Report Wizard, 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 Properties 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