Constraining Query Choices with Combo Boxes

Users of decision-support applications, especially managers, aren't likely to be able 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.

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 a form with two combo boxes that displays a list of orders from a specified country that includes a particular product.

Designing the Decision-Support Query

Query designs 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 selected in the combo boxes.

Follow these steps to create the minimal query for the information to be returned from the combo box selections:

1.	Open the Northwind sample database or create a working copy of Northwind.mdb by compacting it to another file, such as Nwind.mdb. (Using a working copy prevents inadvertent damage to Northwind.mdb.) ◂◂ See Compacting Databases
2.	Create a new query in Design view and add the Customers, Orders, and Order Details tables.
3.	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).
4.	Click the Datasheet button of the toolbar 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 provide your application with 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.

◂◂ See Using List Boxes and Combo Boxes

To create an unbound form with a list box, follow these steps:

1.	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 by 2 inches.
2.	Choose Select Form from the Edit menu, and then click the Properties button for the form. Click the Format tab and 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.
3.	With the Control Wizards button pressed, add a list box from the toolbox to the form. Adding the list box opens the first dialog of the List Box Wizard.
4.	Select the List Box Wizard's I Want the List Box to Look Up the Values in a Table or Query option, and click Next.
5.	Select the Queries option in the View frame, select qryCombo1 (created in the preceding section) from the list, and click Next.
6.	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. Note You don't display the Country or Product ID in the list box because these fields are specified by combo box selection.
7.	Adjust the widths of the columns to suit the list headers and data. Click Next.
8.	Select OrderID as the column to uniquely identify the row. Click Next.
9.	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.
10.	Move the label to the top of the list box and click the toolbar's Bold button to make the label's caption more visible.
11.	Select the list box, and change the Name property value of the combo box to lstOrders. Select Yes as the value of the Column Heads property (see Figure 29.2). Figure 29.2. The list box in Form Design view.
12.	Click the Form View button to check the layout of the list box. Choose Size to Fit Form from the W indows menu to set the dimensions of the form window. Your form appears as shown in Figure 29.3. Figure 29.3. The list box in Form view displaying the first few rows of the query result set of qryCombo1.
13.	Choose Save from the File menu and name your form 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 Customers table. The data source for the product combo box is the Products table.

◂◂ See "Using the Combo Box Wizard"

To add the two combo boxes to the form, follow these steps:

1.	Add from the toolbox a combo box to the upper left of the form; the first dialog of the Combo Box Wizard opens.
2.	Select the I Want the Combo Box to Look Up the Values in a Table or Query and click Next.
3.	With the Tables option selected, choose Customers from the list and click Next.
4.	Select Country in the Available Fields list and click the > button to move Country to the Selected Fields list. Click Next.
5.	Adjust the width of the Country column and click Next.
6.	Accept Country as the caption for the label and click Finish to add the combo box to the form.
7.	Adjust the position and size of the label, and then click the Bold button.
8.	Click the Data tab of the Properties window and set the value of the Limit to List property to Yes.
9.	Click the Other tab of the Properties window and type cboCountry as the value of the Name property.
10.	Repeat steps 1 and 2 (see Figure 29.4). Figure 29.4. The cboCountry and cboProduct combo boxes in Form Design view.
11.	With the Tables option selected, choose Products from the list and click Next.
12.	Select ProductID in the Available Fields list and click the > button to move ProductID to the Selected Fields list. Do the same for ProductName. Click Next.
13.	Clear the Hide Key Column check box and adjust the width of the ProductID and ProductName columns and click Next.
14.	Type Product as the caption for the label and click Finish to add the combo box to the form.
15.	Adjust the position and size of the label, and then click the Bold button.
16.	Click the Data tab of the Properties window and set the value of the Limit to List property to Yes.
17.	Click the Other tab of the Properties window and type cboProduct as the value of the Name property.
18.	Click the Form View button and test both combo boxes (see Figure 29.5). Figure 29.5. The two combo boxes open in Form view. Note Figure 29.5 is a double exposure created from two display captures. You can't open both combo boxes simultaneously.
19.	Choose Save from the File menu to save the changes to frmCombo1.

The Country combo box of Figure 29.5 has an obvious defect: multiple instances of country names that aren't in alphabetical order. These problems arise from the SQL statement that the Combo Box Wizard creates as the value of the Row Source property of the combo box:

SELECT DISTINCTROW [Customers].[CustomerID], [Customers].[Country]
   FROM [Customers];

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. Adding an ORDER BY Country clause provides the sort sequence. ANSI SQL's DISTINCT or Access SQL's DISTINCTROW qualifier solves the duplication problem.

◂◂ See Jet's DISTINCTROW and SQL's DISTINCT Keywords

To make the required changes to the Country combo box, do the following:

1.	Return to Form Design view, select the cboCountry combo box, and click the Properties button.
2.	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; You don't need to include the square brackets around table and field names, because neither includes spaces or other SQL-illegal characters. The table name prefix for field names isn't needed because the query includes only one table. Note Make sure that the Row/Source Type property value remains set to Table/Query after you make the change.
3.	Click the Format tab and change the value of the Column Count property from 2 to 1.
4.	Remove the first 0″; element of the Column Widths property value (see Figure 29.6).
5.	Click the Form view button and test your modified combo box. As shown in Figure 29.7, the duplicates are removed and the country names are in alphabetical order. Figure 29.6. Reducing the Column Count from 2 to 1 and eliminating the hidden column for the original query's CustomerID column. Figure 29.7. The alphabetized Country combo box with duplicates removed.

Adding Code to Create the Query's SQL Statement

Selections you make in the combo boxes return the values required for the WHERE clause criteria of the query that serves as the Row Source property of the lstOrders list box. Selecting an item in the combo list returns the value of the bound column to the combo box's Value property. The Row Source property value of the lstOrders list box created by the List Box Wizard is

SELECT DISTINCTROW [qryCombo1].[CompanyName],
      [qryCombo1].[OrderID], [qryCombo1].[ShippedDate]
   FROM [qryCombo1];

A model SQL statement that simplifies the query syntax, uses the combo box values, and sorts the rows in reverse date order (newest orders first) is

SELECT CompanyName, OrderID, ShippedDate
   FROM qryCombo1
   WHERE Country = cboCountry.Value AND
      ProductID = cboProduct.Value
   ORDER by ShippedDate DESC;

To write the VBA code to create the SELECT query based on combo box values and add instructions for the user, follow these steps:

1.	Choose Tab Order from the View menu to open the Tab Order dialog. Click the Auto Order button to set a cboCountry, cboProduct, lstOrders sequence. Click OK to close the dialog.
2.	Select the list box label and change the value of its Name property to lblList.
3.	Click the Code button to display the Class Module for the frmCombo1 form and add the following code for the SQL statement to the Declarations section, immediately below Option Explicit: Const strSQL1 = "SELECT CompanyName, OrderID, ShippedDate " & _ "FROM qryCombo1 WHERE Country = '" Const strSQL2 = "' AND ProductID = " Const strSQL3 = " ORDER by ShippedDate DESC;" Private strSQL As String Note The single quotation marks (') are required to set off String values within SQL statements. Numeric values don't require quotation marks.
4.	Add the following code for messages to the Declarations section: Const strMsg1 = "Click below to display updated order information" Const strMsg2 = "Select a product from the list" Const strMsg3 = "Select a country from the list"
5.	Select lstOrders from the Object list and select GotFocus from the Procedure list to create the Private Sub lstOrders_GotFocus() event-handler stub.
6.	Add the following code to the GotFocus stub to create the SQL statement for the list box's RowSource property, refresh the list box by applying the Requery method, and change the caption of the list box label to display the WHERE clause criteria: If Me!cboCountry.Value <> "" And Me!cboProduct.Value > 0 Then strSQL = strSQL1 & Me!cboCountry.Value & _ strSQL2 & Me!cboProduct.Value & strSQL3 Me!lstOrders.RowSource = strSQL Me!lstOrders.Requery Me!lblList.Caption = "Orders from " & _ Me!cboCountry.Value & " for " & _ Me!cboProduct.Column(1) End If Note A combo box or list box's Column( n ) property returns the value of the specified column. The first column ( n = 0) of cboProduct is ProductID; the second ( n = 1) is ProductName.
7.	Select cboCountry from the Object list and select AfterUpdate from the Procedure list to create the Private Sub cboCountry_AfterUpdate() event-handler stub.
8.	Add the following code to the AfterUpdate() stub to alter the caption of the list box label: If Me!cboProduct.Value > 0 Then Me!lblList.Caption = strMsg1 Else Me!lblList.Caption = strMsg2 End If
9.	Repeat steps 7 and 8 for the cboProduct combo box, but change the code for step 8 as follows: If Me!cboCountry.Value <> "" Then Me!lblList.Caption = strMsg1 Else Me!lblList.Caption = strMsg3 End If
10.	Choose Compile and Save All Modules from the Debug menu to test and save your code, then close the Class Module window.
11.	Select the list box, click the Data tab of the Properties window, and delete the default Row Source value so that the full result set of qryCombo1 doesn't appear when you open the form.
12.	Click the Form view button to run the code. If you previously selected country and product criteria, the form displays the query result set.

Listing 29.1 contains all code added in the preceding steps. If error messages arise when compiling your code or displaying the form, compare it with this listing.

Code Listing 29.1. VBA Code for the frmCombo1 Class Module as It Appears in the Editing Window

							Option Compare Database
Option Explicit

Const strSQL1 = "SELECT CompanyName, OrderID, ShippedDate " & _
   "FROM qryCombo1 WHERE Country = '"
Const strSQL2 = "' AND ProductID = "
Const strSQL3 = " ORDER by ShippedDate DESC;"
Private strSQL As String
							Const strMsg1 = "Click below to display updated order information"
Const strMsg2 = "Select a product from the list"
Const strMsg3 = "Select a country from the list"

Private Sub cboCountry_AfterUpdate()
   If Me!cboProduct.Value > 0 Then
							Me!lblList.Caption = strMsg1
   Else
      Me!lblList.Caption = strMsg3
   End If
End Sub

Private Sub cboProduct_AfterUpdate()
   If Me!cboCountry.Value <> "" Then
							Me!lblList.Caption = strMsg1
   Else
      Me!lblList.Caption = strMsg2
   End If
End Sub

Private Sub lstOrders_GotFocus()
   If Me!cboCountry.Value <> "" And Me!cboProduct.Value > 0 Then
      strSQL = strSQL1 & Me!cboCountry.Value & _
         strSQL2 & Me!cboProduct.Value & strSQL3
      Me!lstOrders.RowSource = strSQL
      Me!lstOrders.Requery
      Me!lblList.Caption = "Orders from " & _
         Me!cboCountry.Value & " for " & _
         Me!cboProduct.Column(1)
   End If
End Sub

Test your work by selecting values from the Countries and Product combo boxes, and then clicking the list box to display the query result set (see Figure 29.8).

Modifying User Interaction

Most applications require a specific action, such as clicking an object (typically a command button) to execute a query. In this example, lstOrders substitutes for a command button. If you want to update the query automatically when you select a country or product, add the following procedures to the frmCombo1 Class Module:

							Private Sub cboCountry_Change()
   Call lstOrders_GotFocus
End Sub
							Private Sub cboProduct_Change()
   Call lstOrders_GotFocus
End Sub
						

The problem with eliminating the need to click the list box to update the query is that changing an entry in the text box element of the combo box doesn't refresh the list box reliably. If users don't type values to make selections, using the Change event works. A disadvantage of use of the Change event in a multiuser environment is that a query automatically triggers from a change to either combo box, generating more network traffic and read locks on the underlying tables.

If you don't automate query execution by adding Change event handlers, you can reduce potential confusion regarding the currency of the data in the list box by clearing it when users select a new country or product. The following lines at the end of the two AfterUpdate event handlers clear the list box, including the column header text:

							Me!lstOrders.RowSource = ""
Me!lstOrders.Requery