Special Edition Using Microsoft Office Access 2003

List and combo boxes offer various properties and methods that are accessible only through VBA code. The next two sections describe programming techniques that take advantage of additional list and combo box features.

Iterating List Box Items and Selecting an Item

Access list boxes share many common properties with the native ListBox control of Visual Basic 5.0 and earlier. The ListCount property returns the number of items in the list, the ItemData or Column property returns a value from the list, and the Selected property sets or returns whether the row is selected. This example emphasizes a product in the Line Items list box by automatically selecting the row corresponding to the cboProduct selection. The Column property is more versatile than the ItemData property; ItemData is restricted to values in the bound column.

Follow these steps to add the code required to automatically select a product in the lstLineItems list box:

1. Add this statement to the Declarations section of the frmDrillDown Class Module:

   PrivateintCtr As Integer

2. Add these lines immediately above the End If statement of the lstOrders_DblClick event handler:

   With Me!lstLineItems For intCtr =0 To.ListCount -1 If.Column(0, intCtr) = Me!cboProduct.Column(1) Then .Selected(intCtr) = True Exit For End If NextintCtr End With

   The optional second argument of the Column property specifies the row. The If... Then statement determines a match between the text values of the ProductName columns of lstLineItems and cboProduct.

3. Open the form in Form view. Double-click one of the order items to fill lstLineItems and automatically select the specified product (see Figure 29.12).

Tip

You can use code similar to what's in this example to emulate a SELECT query against the content of any list box or combo box. Selecting a list box item ensures that the item is visible in the text box, regardless of its location in the list.

Adding an Option to Select All Countries or Products

It's often useful to give users the option to pick all items represented by combo box selections. In this chapter's example, selecting all countries or all products (but not both) represents an enhancement to the application. How you add an "(All)" choice to cboCountry and cboProduct and write the code for the appropriate SELECT query to fill lstOrders isn't obvious, at best.

A UNION query is the most straightforward way to add custom rows to a combo or list box populated by an SQL statement. You specify your own values for each column returned by the SELECT query to which the UNION clause applies. The Jet UNION query to populate cboCountry is as follows:

SELECT Country FROM Customers UNION SELECT '(All)' FROM Customers ORDER BY Country;

You don't need the DISTINCT modifier of the original SELECT statement because UNION queries don't return duplicate rows. The '(All)' custom item is surrounded with parentheses because ( sorts before numerals and letters, making (All) the first item in the list. The Customers table has no (All) record, but all UNION queries require a FROM TableName clause.

For UNION query syntax, see "Using UNION Queries and Subqueries," p. 452.

Similarly, the UNION query to fill cboProduct is as follows:

SELECT ProductID, ProductName FROM Products UNION SELECT 0, '(All)' FROM Products ORDER BY ProductName;

Here you must supply a numeric value in this case 0 for the first column of the query (ProductID) and the '(All)' string value for the second column (ProductName). UNION queries require that both SELECT statements return the same number of columns, and all rows of each column must be of the same field data type. ProductID is an AutoNumber field, which starts with 1 unless you make the effort to begin autonumbering with a higher value.

In addition to adding the (All) item to the combo boxes, you must alter your SELECT queries to populate lstOrders when you select (All). In the all-countries case, the Jet SELECT query is as follows:

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

For the all-products situation, the Jet query is the following:

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

The preceding changes require you to add logic to detect when you select (All) and change the assembly of the SQL statement to suit. The following steps add the (All) selection to both combo boxes:

1. Make a copy of frmDrillDown as frmDrillDownAll, and open the copy in Form Design view.

2. Select cboCountry, and change its Row Source property value to the following:

   SELECT Country FROM Customers UNION SELECT '(All)' FROM Customers ORDER BY Country;

3. Select cboProduct and change its Row Source property value to the following:

   SELECT ProductID, ProductName FROM Products UNION SELECT 0, '(All)' FROM Products ORDER BY ProductName;

4. Click the Code button, and add the following lines to the Declarations section to create the SQL statement that populates the lstOrder list and to prevent returning all orders:

   Private ConststrSQL5 ="SELECT Customers.CompanyName, " &_ "Orders.OrderID, Orders.ShippedDate FROM (Customers " &_ "INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) " &_ "INNER JOIN [Order Details] ON Orders.OrderID = " &_ "[Order Details].OrderID " Private ConststrSQL6 ="WHERE Country = '" Private ConststrSQL7 ="WHERE ProductID = " Private ConststrMsg6 ="You can't select (All) countries and products"

5. Replace the If...End If code at the beginning (and before the With) of the cboCountry_AfterUpdate event handler as follows to indicate that you can't execute a query that returns all orders:

   If Me!cboProduct.Value > 0 Then Me!lblList.Caption =strMsg1 CallFillList Else If Me!cboCountry.Value ="(All)" Then MsgBoxstrMsg6 Else Me!lblList.Caption =strMsg2 CallFillList End If End If

6. Replace the If...End If code at the beginning (and before the With) of the cboProduct_AfterUpdate event handler to the following:

   If Me!cboCountry.Value <>"" Then If Me!cboCountry.Value ="(All)" And _ Me!cboProduct.Value =0 Then MsgBoxstrMsg6 Else Me!lblList.Caption =strMsg1 CallFillList End If Else Me!lblList.Caption = strMsg2 CallFillList End If

7. Replace the first two lines of the FillList subprocedure (above the Me!lstOrders.RowSource = strSQL line) with the following:

   If Me!cboProduct.Value =0 Then strSQL =strSQL5 &strSQL6 & Me!cboCountry.Value &_ "'" & strSQL3 ElseIf Me!cboCountry.Value ="(All)" Then strSQL =strSQL5 &strSQL7 &Me!cboProduct.Value &_ strSQL3 Else strSQL = strSQL1 & Me!cboCountry.Value &_ strSQL2 & Me!cboProduct.Value &strSQL3 End If

8. Choose Debug, Compile to check your code and fix any VBA statements that won't compile.

9. Return to Access, close the form, and reopen it in Form view. Select a product in cboProduct and (All) in cboCountry to verify your additions, and double-click lstOrders (see Figure 29.13). Reverse the process, by selecting a country, and then selecting (All) products.

   Figure 29.13. Select a product and then (All) to display all orders shipped that include the specified product.