• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 30. Working with ADO Recordsets,... > Altering the Sequence of Combo Box L...

Altering the Sequence of Combo Box Lists

The UNION query that creates the two Recordsets adds the (All) item at the beginning of the list. UNION queries are slower than conventional SELECT queries, so adding the (All) item by modifying the value list improves performance. Also, you can discourage users from running (All) queries by putting (All) at the end of the list. If USA is the most common country selection, you can move USA to the top of the list. To modify your value list code to make these changes, do the following:

  1. Return to Design view and open the VBA Editor if necessary.

  2. Add these two lines, which remove the UNION element of the query, to the Declarations section of the class module:

    Private Const strSQL8 = "SELECT DISTINCT Country FROM Customers " & _
       "ORDER BY Country"
    Private Const strSQL9 = "SELECT ProductID, ProductName FROM Products"

    The DISTINCT reserved word is required in the country SQL statement to remove duplicate rows; UNION queries automatically remove duplicate rows.

  3. Change the two .Source =strSQL statements to .Source =strSQL8 and .Source =strSQL9, respectively.

  4. Change the first strList ="" statement to

        strList = "USA;"
  5. Add this line after the first Loop statement:

        strList = strList & "(All);"
  6. Add this line immediately before the Me!cboProduct.RowSource =strList statement:

        strList = strList & "0;(All);"
  7. To prevent USA from appearing twice in the cboCountry list, wrap the strList = strList & rsfTemp.Fields(0) & ";" line with an IfThen statement:

    If rstTemp.Fields(0) <> "USA" Then
       strList = strList & rsfTemp.Fields(0) & ";"
    End If

    Listing 30.1 includes all the code of the Form_Load event handler.

  8. Press Ctrl+S to save your code, return to Access, and click the View button twice to test your modified combo boxes (see Figure 30.10).



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint