ACC2002: How to Create a PivotChart View That Is Bound to Combo Boxes (304178)

MORE INFORMATION

1. Start Access.
2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
3. In the Database window, click Queries under Objects, and then click New.
4. In the Show Table dialog box, add the following tables, and then click Close.

      Orders
      Order Details
      Products
      Categories
   					

5. On the View menu, click Totals.
6. Add the following fields to the query design grid:

      Field: Freight
      Table: Orders
      Total: Sum
   
      Field: ProductName
      Table: Products
      Total: Group By
   
      Field: CategoryName
      Table: Categories
      Total: Group By
   
      Field: Year: CStr(Nz(Year([OrderDate])))
      Total: Group By
   					

7. Save the query as qryChart.
8. In the Database window, click Forms under Objects, and then click New.
9. In the New Form dialog box, click AutoForm: PivotChart, click qryChart in the Choose the table or query where the object's data comes from box, and then click OK.
10. Move the SumofFreight field to the Drop Data Fields Here area, move the ProductName field to the Drop Series Fields Here area, and then move the CategoryName field to the Drop Category Fields Here area.
11. Save the form as frmPivotSub, and then close it.
12. In the Database window, click Forms under Objects, and then click New.
13. In the New Form dialog box, click Design View, and then click OK.
14. On the View menu, click Form Header/Footer.
15. Add two combo box controls to the form header section, and then assign the following properties to the combo boxes:

       Combo Box 1
       Name: SelectCat
       Row Source Type: Table/Query
       Row Source: Categories
       Column Count: 2
       Column Width: 0";7"
       Bound Column: 2
    					

       Combo Box 2
       Name: SelectYear
       Row Source Type: Value List
       Row Source: 1996;1997;1998;1999
    					

16. If the toolbox is not visible, click Toolbox on the View menu.
17. Make sure that the Control Wizards button is enabled, and then add a subform/subreport control to the detail section.
18. In the SubForm Wizard, click Use an existing form, click frmPivotSub, click Next, and then click Finish.
19. Assign the following properties to the new subform:

       Link Child Fields: CategoryName;Year
       Link Master Fields: SelectCat;SelectYear
       Width: 6"
       Heigh: 6.5"
    					

20. Switch the form to Form view. Click different categories and years in each of the two combo boxes on the form.
    
    Note that the contents of the PivotChart view change. If you select 1999 as the year, the PivotChart view is blank because there is no data for that year.