Special Edition Using Microsoft Office Access 2003

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 as follows:

SELECT 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 this:

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. Select frmCombo1 in the Database window and then click the Code button to display the Class Module for the frmCombo1 form in the VBA editor and add the following constant and variable declarations for the SQL statement to the Declarations section, immediately below Option Explicit:

   Private Const strSQL1 = "SELECT CompanyName, OrderID, ShippedDate " & _ "FROM qryCombo1 WHERE Country = ' " Private Const strSQL2 = " ' AND ProductID = " Private Const strSQL3 = " ORDER BY ShippedDate DESC;" Private strSQL As String

   Tip

   The single quotation marks (') are required to set off String values within SQL statements. Numeric values don't require quotation marks.

2. Add the following code for messages to the Declarations section:

   Private Const strMsg1 = "Select a product from the list" Private Const strMsg2 = "Select a country from the list"

3. Type Private Sub FillList and press Enter to create a subprocedure stub to fill the list box.

4. Add the following code to the FillList 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:

   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) If Me!lstOrders.ListCount = 0 Then Me!lblList.Caption = "No " & Me!lblList.Caption 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.

5. Select cboCountry from the Object list and select AfterUpdate from the Procedure list to create the Private Sub cboCountry_AfterUpdate() event-handler stub.

6. Add the following code to the AfterUpdate() stub to alter the caption of the list box label:

   If Me!cboProduct.Value >0 Then Call FillList Else Me!lblList.Caption = strMsg1 End If

7. Repeat steps 5 and 6 for the cboProduct combo box, but change the code for step 6 as follows:

   If Me!cboCountry.Value <>"" Then CallFillList Else Me!lblList.Caption =strMsg2 End If

8. Select Form from the Object list and Activate from the Procedure list to create a Form_Activate event-handling stub.

9. Add the following code to Form_Activate to generate the list from persisted country and product selections:

   If Me!cboCountry.Value <>"" AndMe!cboProduct.Value >0 Then CallFillList Else Me!lblList.Caption = strMsg2 End If

10. Choose Debug, Compile ProjectName to verify the VBA code you added. If compilation errors occur, check your code against Listing 29.1.

11. Click the View Microsoft Access button to return to Form Design view, select the lstOrders 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. Increase the width of lblList to match the width of the list box.

13. Change to Form view 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.

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

Option Compare Database Option Explicit Private ConststrSQL1 = "SELECT CompanyName, OrderID, ShippedDate " &_ "FROM qryCombo1 WHERE Country ='" Private ConststrSQL2 ="' AND ProductID =" Private ConststrSQL3 =" ORDER BY ShippedDate DESC;" PrivatestrSQL As String Private ConststrMsg1 ="Select a product from the list" Private ConststrMsg2 ="Select a country from the list" Private SubcboCountry_AfterUpdate() If Me!cboProduct.Value >0 Then CallFillList Else Me!lblList.Caption =strMsg2 End If End Sub Private SubcboProduct_AfterUpdate() If Me!cboCountry.Value <>"" Then CallFillList Else Me!lblList.Caption =strMsg1 End If End Sub Private SubFillList() 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) If Me!lstOrders.ListCount =0 Then Me!lblList.Caption ="No " & Me!lblList.Caption End If End Sub Private SubForm_Activate() If Me!cboCountry.Value <> "" And Me!cboProduct.Value > 0 Then Call FillList Else Me!lblList.Caption =strMsg2 End If End Sub

Save your form, and then test your work by selecting values from the Country and Product combo boxes to display the query result set (see Figure 29.8). You can type a few letters in the Country or Product list boxes, and then press Enter to fire the AfterUpdate event for the closest matching item.