ACC: How to Create a Graph Bound to Two Combo Boxes (95/97)

• Microsoft Access versions 7.0, 97

SUMMARY

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

This article describes how to create a graph that is bound to two combo boxes. You control the data that is displayed in the graph by making selections from the combo boxes.

MORE INFORMATION

1. Open the sample database Northwind.mdb.

2. Create a new query in Design view, and add the following tables:

   Orders Order Details Products Categories

3. On the View menu, click Totals.

4. Add the following fields to the query 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
   
   

5. Save the query as qryGraph and close it.

6. Using the Chart Wizard, create a new form based on the qryGraph query.

7. In the "Which fields contain the data you want for the chart?" dialog box, add SumofFreight, ProductName and CategoryName to the Fields for Chart column, and then click Next.

8. In the "What type of chart would you like?" dialog box, click the Column Chart, and then click Next.

9. In the "How do you want to lay out the data in your chart?" dialog box, drag CategoryName to the Axis (bottom of graph) and drag ProductName to the Series (right side of graph), and then click Next.

10. In the "What title would you like for your chart?" dialog box, type Freight Dollars by Category, and then click Finish. The form will open in Form view.

11. On the View menu, click Form Design.

12. On the View menu, click Form Header/Footer.

13. Add two combo box controls to the Form Header section with the following properties:

    Combo Box:

             Name: SelectCat
             RowSourceType: Table/Query
             RowSource: Categories
             ColumnCount: 2
             ColumnWidth: 0";1"
             BoundColumn: 2
    
          Combo Box:
             Name: SelectYear
             RowSourceType: Value List
             RowSource: 1993;1994;1995;1996
    
    

14. Click the graph, and modify the following properties of the unbound object frame:

    Unbound Object Frame:

             Name: FreightGraph
             SizeMode: Zoom
             LinkChildFields: CategoryName;Year
             LinkMasterFields: SelectCat;SelectYear
             Width: 6.5"
             Height: 3.25"
    
    

15. Switch the form to Form view. Click different categories and years in each of the two combo boxes on the form and note that the contents of the graph change.

REFERENCES

For more information about graph control properties, search the Help Index for "graph," and then "Chart Control Properties," or ask the Microsoft Access 97 Office Assistant.