Microsoft KB Archive/304178

= ACC2002: How to Create a PivotChart View That Is Bound to Combo Boxes =

Article ID: 304178

Article Last Modified on 9/26/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q304178



Moderate: Requires basic macro, coding, and interoperability skills.

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



SUMMARY
This article shows you how to create a PivotChart view that displays data based on values that you select from combo boxes.



MORE INFORMATION
To create a PivotChart view that displays data that you select from two combo boxes, follow these steps:

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.

 Start Access. On the Help menu, point to Sample Databases, and then click Northwind Sample Database. In the Database window, click Queries under Objects, and then click New.  In the Show Table dialog box, add the following tables, and then click Close.   Orders Order Details Products Categories  On the View menu, click Totals.  Add the following fields to the query design grid: <pre class="fixed_text">  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                   </li> Save the query as qryChart .</li> In the Database window, click Forms under Objects, and then click New.</li> 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.</li> 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.</li> Save the form as frmPivotSub, and then close it.</li> In the Database window, click Forms under Objects, and then click New.</li> In the New Form dialog box, click Design View, and then click OK.</li> On the View menu, click Form Header/Footer.</li>  Add two combo box controls to the form header section, and then assign the following properties to the combo boxes: <pre class="fixed_text">  Combo Box 1 Name: SelectCat Row Source Type: Table/Query Row Source: Categories Column Count: 2 Column Width: 0&quot;;7&quot; Bound Column: 2 <pre class="fixed_text">  Combo Box 2 Name: SelectYear Row Source Type: Value List Row Source: 1996;1997;1998;1999 </li> If the toolbox is not visible, click Toolbox on the View menu.</li> Make sure that the Control Wizards button is enabled, and then add a subform/subreport control to the detail section.</li> In the SubForm Wizard, click Use an existing form, click frmPivotSub, click Next, and then click Finish.</li>  Assign the following properties to the new subform: <pre class="fixed_text">  Link Child Fields: CategoryName;Year Link Master Fields: SelectCat;SelectYear Width: 6&quot; Heigh: 6.5&quot; </li> 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.</li></ol>

<div class="references_section">