ACC2002: Some Fields Do Not Appear in the PivotTable or PivotChart Field List of Forms (282387)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282387
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you view a form in PivotTable view or PivotChart view, some fields are not displayed in the PivotTable or PivotChart field list.

CAUSE

There are three possible causes of this behavior:

  • There are no controls on the form that are bound to the field.
  • There is a control bound to the field on the form, but the Visible property for that control is set to No.
  • The field's column width is set to 0 in the form's datasheet view.

RESOLUTION

Make sure that the form contains at least one control that is bound to the field, and make sure the control's Visible property is set to Yes. Also make sure that the column is not hidden in datasheet view of the form. To unhide a column in datasheet view, follow these steps:
  1. In the Database window, under Objects, click Forms, and then click the form that is based on a table with hidden fields.
  2. Click the Open button in the Database window.
  3. On the View menu, click Datasheet View.
  4. On the Format menu, click Unhide Columns.
  5. In the Unhide Columns dialog box, select any field that you want to include in the PivotTable view or PivotChart view of the form, and then click Close.
  6. On the View menu, click PivotTable View.

    The field that is hidden in the underlying table is now visible in the field list for the PivotTable view of the form.
Additionally, you can programmatically unhide the field to the Pivot view field list. For example, this would be useful if you desired to make the field available in the Pivot views, but wanted it hidden in other views of the form. To programmatically unhide a field in the PivotTable or PivotChart field list, follow these steps:

  1. Follow steps 1 - 6 of the "Steps to Reproduce Behavior" section of this article. Notice that after you open the form in PivotTable view, the EmployeeID field is not available in the PivotTable Field List.
  2. On the View menu, click Design View.
  3. On the View menu, click Code to view the form's module.
  4. On the Tools menu, click References.
  5. Click to select the Microsoft Office Web Components 10.0 check box. If you do not see this entry listed, click the Browse button, and select the file OWC10.DLL, located in the folder C:\Program Files\Common Files\Microsoft Shared\Web Components\10.
  6. Click OK to close the References dialog box.
  7. Add the following code to the form's module:
          Private Sub Form_ViewChange(ByVal Reason As Long)
         Dim pTable As OWC10.PivotTable

         'Only run when the user switches into PivotTable
         'or PivotChart view
         If Reason = plViewReasonSetFocus Or Reason = -1 Then
            Set pTable = Me.PivotTable
            With pTable.ActiveView
               .FieldSets("EmployeeID").DisplayInFieldList = True

            End With
         End If
      End Sub
  8. On the File menu, click Close and Return to Microsoft Access.
  9. On the View menu, click PivotTable View. The EmployeeID field is available in the PivotTable Field List.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The following three requirements must be met for fields to be visible in the PivotTable or PivotChart field list:
  • A control bound to the field must exist in one of the form's sections in design view.

    -and-
  • The Visible property of that control must be set to Yes.

    -and-
  • In datasheet view, the column width for the field must be greater than zero.
If the form does not meet all three requirements, then the field will not be visible in the PivotTable or PivotChart field list. This problem is further complicated because the form's datasheet view is automatically inherited from the datasheet view of the underlying table or query. If you hide a column in datasheet view of a table or query by setting its column width to zero, by default the column will also be hidden in datasheet view for any new forms based on that object. This is true even if you placed the field in the form's detail section, and have set its Visible property to Yes. In a situation like this, the field will be missing unexpectedly from the Pivot view field list, while it is available in form view.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. In the Database window, under Objects, click Tables, then click Orders, and then click Open.
  3. Click any cell in the Employee column. On the Format menu, click Column Width. Set the Column Width to 0, and then click OK.

    NOTE: If you hide the field by clicking Hide Field on the Format menu, the field is hidden in the Datasheet view and the Pivot views based on the table. It is not, however, hidden in the Datasheet view and Pivot views of any form based on the table. The behavior described in this article only occurs if the field is hidden by reducing the column width to zero.

  4. On the File menu, click Save, and then close the Orders table.
  5. On the Insert menu, click AutoForm.

    The Employee field is visible on the form.
  6. On the View menu, click PivotTable View.

    The Employee field is not available in the PivotTable field list.

REFERENCES

For more information about PivotTable and PivotChart views of forms, click Microsoft Access Help on the Help menu, type Features that aren't available in Access 2000 in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbbug kbpending KB282387
©2003 Microsoft Corporation. All rights reserved.