| Users of decision-support applications, especially managers, aren't likely to be able to or want to use Access's graphical Query Design window. Instead, most users prefer to pick criteria (SELECT query WHERE clause elements) from one or more lists of available options. One primary advantage of offering users a set of choices to construct a query is the ability to prevent execution of ad hoc queries that return an excessive number of rows. Accidentally returning thousands of rows or even worse a Cartesian product of a million rows or more can bring a multiuser application or the entire network to its knees. Network and database administrators call such events "queries from hell." Tip Combo boxes are the better choice for generating WHERE clause criteria because they occupy less room on forms than list boxes. Also, you can navigate quickly to a combo box item by typing the first few characters of the item in the combo box's text box element.
The following sections describe how to create an unbound form with two combo boxes that displays a list of orders from a specified country that include a particular product. Designing the Decision-Support Query Query design is one of the most important elements of decision-support applications. One primary objective of decision-support systems is fast response time. To return selected information quickly, the query design should be as simple as possible. Include in the query only those fields needed to display necessary information, plus the foreign key fields to be selected in the combo boxes. Follow these steps to create the minimal query for the customer-product-order information to be returned from the combo box selections: Open the Northwind sample database or your working copy of Northwind.mdb.  Create a new query in Design view and add the Customers, Orders, and Order Details tables.
Drag the CompanyName and Country fields of the Customers table, the OrderID and ShippedDate fields of the Orders table, and the ProductID of the Order Details table to the Query Design grid (see Figure 29.1). Figure 29.1. This query design delivers the data required to populate combo and list boxes.   Click Run to test your query, and then close and save your query as qryCombo1.
Creating the Form and Adding a List Box An Access list box is the most efficient control for displaying the read-only query result sets of decision-support applications. List boxes consume fewer computer resources than subforms, are easier for users to navigate, and have the properties and events needed to give your application drill-down capabilities. Drill down is the process of providing users with more detailed information about a specific item in the list. Later, the section "Drilling Down from a List Box Selection" shows you how to add drill-down capabilities to the form you create here.  For list and combo box basics, see "Adding Combo and List Boxes," p. 596.
To create an unbound form with a list box populated by qryCombo1, follow these steps:  Create a new unbound form in Design view. (Don't specify a Record Source for the form.) Adjust the size of the form to about 4.5 inches wide by 2.5 inches deep.
  Click the form selection button, and then click the Properties button for the form. Click the Format tab and set the Allow Datasheet View, Allow PivotTable View, and Allow PivotChart View property values to No. Set Scroll Bars to Neither, Record Selectors to No, and Navigation Buttons to No. Type Order Query with Criteria from Combo Boxes as the value of the Caption property.
  With the Control Wizards button depressed, add a list box from the Toolbox to the form. Adding the list box opens the first dialog of the List Box Wizard.
Select the I Want the List Box to Look Up the Values in a Table or Query option, and click Next. Select the Queries option in the View frame, select qryCombo1 (created in the preceding section) from the list, and click Next. Select the CompanyName field in the Available Fields list and click the > button to add the field to the Selected Fields list. Repeat the process for the OrderID and ShippedDate fields, and then click Next twice to bypass the sort dialog. Note You don't display the Country or Product ID in the list box because these fields are specified by combo box selection. Adjust the widths of the columns to suit the list headers and data. Click Next. Select OrderID as the column to uniquely identify the row. Click Next. Type Orders by Country and Product as the caption for the list box's label, and click Finish to add the list box to the form.  Move the label to the top of the list box, click the Bold button on the toolbar to make the label's caption more visible, and adjust the width of the label.
Select the list box label, open the Properties window, click the Other tab, and change the value of its Name property to lblList. Select the list box, click the All tab and change the Name property value of the combo box to lstOrders. Set Yes as the value of the Column Heads property and set Bound Column to 2 (see Figure 29.2). Figure 29.2. Setting OrderID (column 2) as the Bound Column property value prepares the form for later addition of the drill-down feature.   Click the Form View button to check the layout of the list box. Choose Windows, Size to Fit Form to set the dimensions of the form window. Your form appears as shown in Figure 29.3.
Figure 29.3. The lstOrders list box displays all rows of the query at this point.  Press Ctrl+S to save the form and name it frmCombo1.
Adding the Query Combo Boxes to the Form You need one combo box to select the country and another to select the product. Northwind.mdb doesn't have a Countries table, so the data source for the country combo box is the Country field of the Customers table. The data source for the product combo box is the Products table. For detailed combo box instructions, see "Using the Combo Box Wizard," p. 596.
To add the country and product combo boxes to the form, follow these steps:   Change to Design mode and add a combo box to the upper left of the form; the first dialog of the Combo Box Wizard opens.
Select the I Want the Combo Box to Look Up the Values in a Table or Query and click Next. With the Tables option selected, select Customers from the list and click Next. Select Country in the Available Fields list and click the > button to move Country to the Selected Fields list. Click Next. Open the first sort list and select Country. Click Next. Adjust the width of the Country column and click Next. Accept Country as the caption for the label and click Finish to add the combo box to the form. Select the Country label, click the Bold button, and adjust the position and size of the label (look ahead to Figure 29.4). Select the combo box, click the Data tab of the Properties window, and verify that the value of the Limit to List property is Yes. Click the Other tab of the Properties window and type cboCountry as the value of the Name property. Repeat steps 1 and 2. With the Tables option selected, select Products from the list and click Next. Select ProductID in the Available Fields list and click the > button to move ProductID to the Selected Fields list. Do the same for ProductName, and then click Next. Select ProductName as the sort order, and click Next. Adjust the width of the ProductName column to accommodate long product names, and click Next. Type Product as the caption for the label, and click Finish to add the combo box to the form. Click the Bold button and adjust the position and size of the label. With the text box component of the combo control selected, click the Other tab of the Properties window, and type cboProduct as the value of the Name property (see Figure 29.4). Figure 29.4. The final form design has the two combo boxes you use to select the country and product to populate the orders list box.  Choose View, Tab Order to open the Tab Order dialog. Click the Auto Order button to set a cboCountry, cboProduct, lstOrders sequence. Click OK to close the dialog. Click the Form View button and test both combo boxes (see Figure 29.5). Figure 29.5. At this point, the Country combo box has a row for each customer, instead of a row for each country.  Note Figure 29.5 is a double exposure created from two display captures. You can't open both combo boxes simultaneously. Press Ctrl+S to save the changes to frmCombo1.
The Country combo box in Figure 29.5 has an obvious defect: multiple instances of country names. These problems arise from the SQL statement that the Combo Box Wizard creates as the value of the combo box's Row Source property: SELECT Customers.CustomerID, Customers.Country FROM Customers ORDER BY [Country]; The Combo Box Wizard automatically includes the primary-key field of the table (CustomerID) as the bound column, so you must remove the Customers.CustomerID column from the SQL statement and modify cboCountry's properties to accommodate this change. ANSI SQL's DISTINCT or Access SQL's DISTINCTROW qualifier solves the duplication problem. For the differences between the two SELECT qualifiers, see "Writing SELECT Queries in SQL," p. 857.
To make the required changes to the Country combo box, do the following: Select the cboCountry combo box, and click the Properties button. Access 2003 lets you change most of the properties of combo boxes in Form view. Click the Data tab, and then edit the value of the Row Source property to the following: SELECT DISTINCT Country FROM Customers ORDER BY Country; The table name prefix for field names isn't needed because the query includes just one table. Note Make sure the Row/Source Type property value remains set to Table/Query after you make the change. Click the All tab and change the value of the Column Count property from 2 to 1. Remove the first 0"; element of the Column Widths property value, and set the Limit to List property value to Yes (see Figure 29.6). Figure 29.6. Change the Row Source to a SELECT query with the DISTINCT qualifier, change the list to display a single column, and set the Limit to List property value to Yes.  Note If you don't change the Column Count and Column Widths property values in steps 3 and 4, cboCountry displays an empty list. Open the modified combo box. As shown in Figure 29.7, the duplicates are removed and the country names are in alphabetical order. Close the form and save your changes.
Figure 29.7. The Country list displays a single row for each country in alphabetical order.  |
