Automating Data Selection

One of the most common tasks to automate in a database application is filtering data. Particularly when a database contains thousands of records, users will rarely need to work with more than a few records at a time. If your edit forms always display all the records, performance can suffer greatly. So it’s a good idea to enable the user to easily specify a subset of records. This section examines four ways to do this.

Working with a Multiple-Selection List Box

You work with list boxes all the time in Windows and in Access. For example, the file list in Windows Explorer is a list box, the Access 2007 Navigation Pane is a list box, and the list of properties on any tab in the property sheet is a list box. In the property, you can select only one property from the list at a time. If you click a different property, the previous object is no longer selected-this is a simple list box. In Windows Explorer, you can select one file, select multiple noncontiguous files by holding down the Ctrl key and clicking, or select a range of files by holding down the Shift key and clicking-this is a multiple-selection list box.

Suppose you’re using the Conrad Systems Contacts application (Contacts.accdb) and you’re interested in looking at the details for several contacts at one time but will rarely want to look at the entire list. Start the application by opening the frmSplash form, select John Viescas as the User Name, and click Sign On (no password required). Click the Contacts button on the main switchboard form, and the application opens the

Select Contacts form (frmContactList). As shown in Figure 20–12, the frmContactList form contains a multiple-selection list box.

Note 

You won’t see the Select Contacts dialog box if the Don’t Show Contact List option is selected in John’s user profile. If the Contacts form opens when you click the Contacts button on the main switchboard, close the form and click the Users button. Clear the Don’t Show Contact List option in John’s profile, save the record, and close the form. You should now see the Select Contacts dialog box when you click the Contacts button on the main switchboard.

Figure 20–12: You can select multiple contact records to edit in the frmContactList form.

In this list box, the contacts are shown in alphabetic order by last name, and the list is bound to the ContactID field in the underlying table. You can edit any single contact by simply double-clicking the person’s name. You can move the highlight up or down by using the arrow keys. You can also type the first letter of a contact last name to jump to the next contact whose last name begins with that letter. You can hold down the Shift key and use the arrow keys to extend the selection to multiple names. Finally, you can hold down either the Shift key or the Ctrl key and use the mouse to select multiple names.

Figure 20–12 shows three contacts selected using the Ctrl key and the mouse. When you click the Edit button, the application opens the frmContacts form with only the records you selected. As shown in Figure 20–13, the caption to the right of the Record Number box indicates three available records and that the recordset is filtered.

Figure 20–13: After you select the records you want to edit in the frmContactList form, the application opens the frmContacts form displaying only those records.

To see how this works, you need to go behind the scenes of the frmContactList form. Click Exit on the main switchboard form to return to the Navigation Pane. (Click Yes in the message box that asks “Are you sure you want to exit?,” and click No if the application offers to create a backup for you.) Select frmContactList, and open the form in Design view, as shown in Figure 20–14. Click the list box control, and open its property sheet to see how the list box is defined. The list box uses two columns from the qlkpContacts query, hiding the ContactID (the primary key that will provide a fast lookup) in the first column and displaying the contact name in the second column. The key to this list box is that its Multi Select property is set to Extended. Using the Extended setting gives you the full Ctrl+click or Shift+click features that you see in most list boxes in Windows. The default for this property is None, which lets you select only one value at a time. You can set it to Simple if you want to select or clear multiple values using the mouse or the Spacebar.

Figure 20–14: The multiple-selection list box on the frmContactList form has its Multi Select property set to Extended.

If you scroll down to the Event properties, you’ll find an event procedure defined for On Dbl Click. The code for this event procedure (which is called when you double-click an item in the list box) runs only the cmdSome_Click procedure. Right-click the cmdSome command button (the one whose caption says Edit) and choose Build Event from the shortcut menu to jump to the cmdSome_Click procedure that does all the work, as shown here:

 Private Sub cmdSome_Click()  Dim strWhere As String, varItem As Variant    ' Request to edit items selected in the list box    ' If no items selected, then nothing to do    If Me!lstCName.ItemsSelected.Count = 0 Then Exit Sub    ' Loop through the items selected collection    For Each varItem In Me!lstCName.ItemsSelected      ' Grab the ContactID column for each selected item      strWhere = strWhere & Me!lstCName.Column(0, varItem) & ","    Next varItem    ' Throw away the extra comma on the "IN" string    strWhere = Left$(strWhere, Len(strWhere) - 1)    ' Open the contacts form filtered on the selected contacts    strWhere = "[ContactID] IN (" & strWhere & ") And (Inactive = False)"    DoCmd.OpenForm FormName:="frmContacts", WhereCondition:=strWhere    DoCmd.Close acForm, Me.Name  End Sub

When you set the Multi Select property of a list box to something other than None, you can examine the control’s ItemsSelected collection to determine what (if anything) is selected. In the cmdSome_Click procedure, the Visual Basic code first checks the Count property of the control’s ItemsSelected collection to determine whether anything is selected. If the Count is 0, there’s nothing to do, so the procedure exits.

The ItemsSelected collection is composed of variant values, each of which provides an index to a highlighted item in the list box. The For Each loop asks Visual Basic to loop through all the available variant values in the collection, one at a time. Within the loop, the code uses the value of the variant to retrieve the Contact ID from the list. List boxes also have a Column property, and you can reference all the values in the list by using a statement such as

 Me.ListBoxName.Column(ColumnNum, RowNum)

where ListBoxName is the name of your list box control, ColumnNum is the relative column number (the first column is 0, the second is 1 and so on), and RowNum is the relative row number (also starting at 0). The variant values in the ItemsSelected collection return the relative row number. This Visual Basic code uses column 0 and the values in the ItemsSelected collection to append each selected ContactID to a string variable, separated by commas. You’ll recall from studying the IN predicate in Chapter 7 that a list of values separated by commas is ideal for an IN clause.

After retrieving all the ContactID numbers, the next statement removes the trailing comma from the string. The final Where clause includes an additional criterion to display only active contacts. The DoCmd.OpenForm command uses the resulting string to create a filter clause as it opens the form. Finally, the code closes the frmContactList form. (Me.Name is the name of the current form.)

Providing a Custom Query By Form

Suppose you want to do a more complex search on the frmContacts form-using criteria such as contact type, company, or products owned rather than simply using contact name. You could teach your users how to use the Filter By Form features to build the search, or you could use Filter By Form to easily construct multiple OR criteria on simple tests. But if you want to find, for example, all contacts who own the Single User edition or whom you contacted between certain dates, there’s no way to construct this request using standard filtering features. The reason for this is that when you define a filter for a subform (such as the Events subform in frmContacts) using Filter By Form, you’re filtering only the subform rows. You’re not finding contacts who have only a matching subform row.

The only solution, then, is to provide a custom Query By Form that provides options to search on all the important fields and then build the Where clause to solve the search problem using Visual Basic code. To start, open the Conrad Systems Contacts application. (If you have exited to the Navigation Pane, you can start the application by opening frmSplash.) Sign on, click the Contacts button on the main switchboard form, and then click the Search button in the Select Contacts dialog box. You should see the fdlgContactSearch form, as shown in Figure 20–15.

Figure 20–15: You can design a custom Query By Form to perform a complex search.

Try selecting contacts whose last name begins with the letter M, whom you contacted between March 1, 2007, and June 15, 2007, and who own the BO$$ Single User product (from the Owns Product drop-down list). When you click the Search button, you should see the frmContacts form open and display two contacts.

To see how this works, you need to explore the design of the fdlgContactSearch form. Switch to the Navigation Pane (press F11), and open the form in Design view. You should see a window like that shown in Figure 20–16. Notice that the form is not bound to any record source. The controls must be unbound so they can accept any criteria values that a user might enter.

Figure 20–16: When you look at the fdlgContactSearch form in Design view, you can see that it has no record source.

The bulk of the work happens when you click the Search button. The code for the event procedure for the Click event of the Search button is shown here:

 Private Sub cmdSearch_Click()  Dim varWhere As Variant, varDateSearch As Variant  Dim rst As DAO.Recordset    ' Initialize to Null    varWhere = Null    varDateSearch = Null    ' First, validate the dates    ' If there's something in Contact Date From    If Not IsNothing(Me.txtContactFrom) Then      ' First, make sure it's a valid date      If Not IsDate(Me.txtContactFrom) Then        ' Nope, warn them and bail        MsgBox "The value in Contact From is not a valid date.", _          vbCritical, gstrAppTitle        Exit Sub      End If      ' Now see if they specified a "to" date      If Not IsNothing(Me.txtContactTo) Then        ' First, make sure it's a valid date        If Not IsDate(Me.txtContactTo) Then          ' Nope, warn them and bail          MsgBox "The value in Contact To is not a valid date.",  _            vbCritical, gstrAppTitle          Exit Sub        End If        ' Got two dates, now make sure "to" is >= "from"        If Me.txtContactTo < Me.txtContactFrom Then          MsgBox "Contact To date must be greater than " & _            "or equal to Contact From date.", _            vbCritical, gstrAppTitle          Exit Sub        End If      End If    Else      ' No "from" but did they specify a "to"?      If Not IsNothing(Me.txtContactTo) Then        ' Make sure it's a valid date        If Not IsDate(Me.txtContactTo) Then          ' Nope, warn them and bail          MsgBox "The value in Contact To is not a valid date.", _            vbCritical, gstrAppTitle          Exit Sub        End If      End If    End If    ' If there's something in Follow-up Date From    If Not IsNothing(Me.txtFollowUpFrom) Then      ' First, make sure it's a valid date      If Not IsDate(Me.txtFollowUpFrom) Then        ' Nope, warn them and bail        MsgBox "The value in Follow-up From is not a valid date.", _          vbCritical, gstrAppTitle        Exit Sub      End If      ' Now see if they specified a "to" date      If Not IsNothing(Me.txtFollowUpTo) Then        ' First, make sure it's a valid date        If Not IsDate(Me.txtFollowUpTo) Then          ' Nope, warn them and bail          MsgBox "The value in Follow-up To is not a valid date.", _            vbCritical, gstrAppTitle          Exit Sub        End If        ' Got two dates, now make sure "to" is >= "from"        If Me.txtFollowUpTo < Me.txtFollowUpFrom Then          MsgBox "Follow-up To date must be greater than " & _            "or equal to Follow-up From date.", _            vbCritical, gstrAppTitle          Exit Sub        End If      End If    Else      ' No "from" but did they specify a "to"?      If Not IsNothing(Me.txtFollowUpTo) Then        ' Make sure it's a valid date        If Not IsDate(Me.txtFollowUpTo) Then          ' Nope, warn them and bail          MsgBox "The value in Follow-up To is not a valid date.", _            vbCritical, gstrAppTitle          Exit Sub        End If      End If    End If    ' OK, start building the filter    ' If specified a contact type value    If Not IsNothing(Me.cmbContactType) Then      ' .. build the predicate      varWhere = "(ContactType.Value = '" & Me.cmbContactType & "')"    End If    ' Do Last Name next    If Not IsNothing(Me.txtLastName) Then      ' .. build the predicate      ' Note: taking advantage of Null propagation      '  so we don't have to test for any previous predicate      varWhere = (varWhere + " AND ") & "([LastName] LIKE '" & _        Me.txtLastName & "*')"    End If    ' Do First Name next    If Not IsNothing(Me.txtFirstName) Then      ' .. build the predicate      varWhere = (varWhere + " AND ") & "([FirstName] LIKE '" & _        Me.txtFirstName & "*')"    End If    ' Do Company next    If Not IsNothing(Me.cmbCompanyID) Then      ' .. build the predicate      ' Must use a subquery here because the value is in a linking table...      varWhere = (varWhere + " AND ") & _        "([ContactID] IN (SELECT ContactID FROM tblCompanyContacts " & _        "WHERE tblCompanyContacts.CompanyID = " & Me.cmbCompanyID & "))"    End If    ' Do City next    If Not IsNothing(Me.txtCity) Then      ' .. build the predicate      ' Test for both Work and Home city      varWhere = (varWhere + " AND ") & "(([WorkCity] LIKE '" & _        Me.txtCity & "*')" & _        " OR ([HomeCity] LIKE '" & Me.txtCity & "*'))"    End If    ' Do State next    If Not IsNothing(Me.txtState) Then      ' .. build the predicate      ' Test for both Work and Home state      varWhere = (varWhere + " AND ") & "(([WorkStateOrProvince] LIKE '" & _        Me.txtState & "*')" & _        " OR ([HomeStateOrProvince] LIKE '" & Me.txtState & "*'))"    End If    ' Do Contact date(s) next -- this is a toughie    '   because we want to end up with one filter on the subquery table    '   for both Contact Date range and FollowUp Date range    ' Check Contact From first    If Not IsNothing(Me.txtContactFrom) Then      ' .. build the predicate      varDateSearch = "tblContactEvents.ContactDateTime >= #" & _        Me.txtContactFrom & "#"    End If    ' Now do Contact To    If Not IsNothing(Me.txtContactTo) Then      ' .. add to the predicate, but add one because ContactDateTime includes      '  a date AND a time       varDateSearch = (varDateSearch + " AND ") & _         "tblContactEvents.ContactDateTime ` #" & _         CDate(Me.txtContactTo) + 1 & "#"    End If    ' Now do Follow-up From    If Not IsNothing(Me.txtFollowUpFrom) Then      ' .. add to the predicate      varDateSearch = (varDateSearch + " AND ") & _        "tblContactEvents.ContactFollowUpDate >= #" & Me.txtFollowUpFrom & "#"    End If    ' Finally, do Follow-up To    If Not IsNothing(Me.txtFollowUpTo) Then       ' .. add to the predicate       varDateSearch = (varDateSearch + " AND ") & _         "tblContactEvents.ContactFollowUpDate `= #" & Me.txtFollowUpTo & "#"    End If    ' Did we build any date filter?    If Not IsNothing(varDateSearch) Then      ' OK, add to the overall filter      ' Must use a subquery here because the value is in a linking table...      varWhere = (varWhere + " AND ") & _        "([ContactID] IN (SELECT ContactID FROM tblContactEvents " & _        "WHERE " & varDateSearch & "))"    End If    ' Do Product    If Not IsNothing(Me.cmbProductID) Then      ' .. build the predicate      ' Must use a subquery here because the value is in a linking table...      varWhere = (varWhere + " AND ") & _         "([ContactID] IN (SELECT ContactID FROM tblContactProducts " & _         "WHERE tblContactProducts.ProductID = " & Me.cmbProductID & "))"    End If    ' Finally, do the Inactive check box    If (Me.chkInactive = False) Then      ' Build a filter to exclude inactive contacts      varWhere = (varWhere + " AND ") & "(Inactive = False)"    End If    ' Check to see that we built a filter    If IsNothing(varWhere) Then      MsgBox "You must enter at least one search criteria.", _        vbInformation, gstrAppTitle      Exit Sub    End If    ' Open a recordset to see if any rows returned with this filter    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblContacts " & _      "WHERE " & varWhere)    ' See if found none    If rst.RecordCount = 0 Then      MsgBox "No Contacts meet your criteria.", vbInformation, gstrAppTitle      ' Clean up recordset      rst.Close      Set rst = Nothing      Exit Sub    End If    ' Hide me to fix later focus problems    Me.Visible = False    ' Move to last to find out how many    rst.MoveLast    ' If 5 or less or frmContacts already open,    If (rst.RecordCount < 6) Or IsFormLoaded("frmContacts") Then      ' Open Contacts filtered      ' Note: if form already open, this just applies the filter      DoCmd.OpenForm "frmContacts", WhereCondition:=varWhere      ' Make sure focus is on contacts      Forms!frmContacts.SetFocus    Else      ' Ask if they want to see a summary list first      If vbYes = MsgBox("Your search found " & rst.RecordCount & _        " contacts.  " & _        "Do you want to see a summary list first?", _        vbQuestion + vbYesNo, gstrAppTitle) Then        ' Show the summary        DoCmd.OpenForm "frmContactSummary", WhereCondition:=varWhere        ' Make sure focus is on contact summary        Forms!frmContactSummary.SetFocus      Else        ' Show the full contacts info filtered        DoCmd.OpenForm "frmContacts", WhereCondition:=varWhere        ' Make sure focus is on contacts        Forms!frmContacts.SetFocus      End If    End If    ' Done    DoCmd.Close acForm, Me.Name    ' Clean up recordset    rst.Close    Set rst = Nothing  End Sub 

The first part of the procedure validates the contact date from and to values and the follow-up date from and to values. If any are not valid dates or the from date is later than the to date, the code issues an appropriate warning message and exits.

The next several segments of code build up a WHERE string by looking at the unbound controls one at a time. If the corresponding field is a string, the code builds a test using the LIKE predicate so that whatever the user enters can match any part of the field in the underlying table, but not all the fields are strings. When the function adds a clause as it builds the WHERE string, it inserts the AND keyword between clauses if other clauses already exist. Because the variable containing the WHERE clause is a Variant data type initialized to Null, the code can use a+concatenation to optionally add the AND keyword. Note that because the ContactType field is a multi-value field, the code specifically searches the Value property of the field.

The underlying record source for the frmContacts form does not include either contact event or product information directly, so the procedure has to build a predicate using a subquery if you ask for a search by contact date, follow-up date, or product. In the case of contact date or follow-up date, the code builds a separate filter string (varDate-Search) because both fields are in the same table (tblContactEvents). If you ask for any date range check, the code builds criteria using a subquery that finds the ContactID from records in the tblContactEvents table that fall within the date range. For a search by product, the code builds criteria using a subquery that finds the ContactID from records in the tblContactProducts table that match the product you selected. Finally, if you leave the Include Inactive Contacts check box cleared, the code adds a test to include only records that are active.

After examining all the possible filter values the user could have entered, the code checks to see if there’s anything in the filter string (varWhere). There’s no point in opening the form without a filter, so the code displays a message and exits, leaving the form open to allow the user to try again.

The final part of the procedure builds a simple recordset on the tblContacts table used in both the frmContacts and frmContactSummary forms, applying the WHERE clause built by the code in the first part of the procedure. If it finds no records, it uses the MsgBox function to inform the user and then gives the user a chance to try again.

When you first open a Recordset object in code, its RecordCount property is 0" if the recordset is empty and is some value greater than 0" if the recordset contains some records. The RecordCount property of a Recordset object contains only a count of the number of rows visited and not the number of rows in the recordset. So if it finds some rows, the procedure moves to the last row in the temporary recordset to get an accurate count. When the record count is greater than 5, and the frmContacts form is not already open, the procedure uses the MsgBox function to give the user the option to view a summary of the records found in the frmContactSummary form or to display the records found directly in the frmContacts form. (As noted earlier, both forms use the same record source, so the code can apply the filter it built as it opens either form.) We’ll examine how the frmContactSummary form works in the next section.

Selecting from a Summary List

As you saw in the cmdSearch_Click procedure in the previous section, the user gets to make a choice if more than 5, rows meet the entered criteria. To examine this feature in more detail, make sure the frmContacts form is not open, and ask for a search of contacts with a Contact Type of Customer in the fdlgContactSearch form. The result should look like that shown in Figure 20–17, in which 30 contacts are categorized as customers.

Figure 20–17: This message box appears when the cmdSearch_Click procedure returns more than five rows.

When you click Yes, the cmdSearch_Click procedure opens the Contact Search Summary form (frmContactSummary), as shown in Figure 20–18. You can scroll down to any row, put the focus on that row (be sure the row selector indicator is pointing to that row), and then click the View Details button to open the frmContacts form and view the details for the one contact you selected. You can see that this is a very efficient way to help the user narrow a search down to one particular contact.

Figure 20–18: You can select a specific contact from the search summary form.

You can also double-click either the Contact ID or the Name field to see the details for that contact. Because this list is already filtered using the criteria you specified in the fdlgContactSearch form, the code that responds to your request builds a simple filter on the one Contact ID to make opening the frmContacts form most efficient. The code behind this form that responds to your request is as follows:

 ' Set up the filter  strFilter = "(ContactID = " & Me.ContactID & ")"  ' Open contacts filtered on the current row  DoCmd.OpenForm FormName:="frmContacts", WhereCondition:=strFilter  ' Close me  DoCmd.Close acForm, Me.Name  ' Put focus on contacts  Forms!frmContacts.SetFocus

Filtering One List with Another

You might have noticed when editing products on the Products tab in the frmContacts form (see Figure 20–1) that you can first choose a product type to narrow down the list of products and then choose the product you want. There are only 11 products in the sample application, so being able to narrow down the product selection first isn’t all that useful, but you can imagine how a feature like this would be absolutely necessary in an application that had thousands of products available for sale.

The secret is that the row source for the Product combo box is a parameter query that filters the products based on the product type you chose. When you use this technique in a form in Single Form view, all you need to do is requery the filtered combo box (in this case, the Product combo box) when the user moves to a new record (in the Current event of the form) and requery when the user chooses a different value in the combo box that provides the filter value (in the AfterUpdate event of the combo box providing the filter value).

However, using this technique on a form in Continuous Forms view is considerably more complex. Even though you can see multiple rows in Continuous Forms view, there is actually only one copy of each control on the form. If you always requery the Product combo box each time you move to a new row, the product name displayed in other rows that have a different product type will appear blank! When the value in a row doesn’t match a value in the list, you get a blank result, not the actual value of the field.

The way to solve this is to include the display name in the recordset for the form and carefully overlay each combo box with a text box that always displays the correct value regardless of the filter. You can open the fsubContactProducts form in Design view to see how we did this. Figure 20–19 shows you the form with the two overlay text boxes (CategoryDescription and ProductName) pulled down from the underlying combo boxes (Unbound and ProductID).

Figure 20–19: You can solve a filtered combo box display problem by overlaying text boxes.

Notice that the control source of the Product combo box is actually the ProductID field, but the combo box displays the ProductName field. Also, the Product Type combo box isn’t bound to any field at all-there is no CategoryDescription field in tblContact-Products-but it does display the CategoryDescription field from the lookup table. To make this work, you need to include the ProductName and CategoryDescription fields in the record source for this form. You don’t want the user to update these values, but you need them to provide the overlay display. These two text boxes have their Locked property set to Yes to prevent updating and their Tab Stop property set to No so that the user will tab into the underlying combo boxes and not these text boxes. Figure 20–20 shows you the qryContactProducts query that’s the row source for this form.

Figure 20–20: The qryContactProducts query provides the necessary ProductName and CategoryDescription fields from a related table so that you can display the values.

To make it all work correctly, several event procedures make sure the focus goes where necessary and that the filtered Product combo box gets requeried correctly. The code behind the fsubContactProducts form that does this is as follows:

 Private Sub CategoryDescription_GotFocus()   ' We have some tricky "overlay" text boxes here that   ' shouldn't get the focus. Move focus to the underlying   ' combo box if that happens.   Me.cmbCategoryDescription.SetFocus End Sub Private Sub cmbCategoryDescription_AfterUpdate()   ' If they pick a new Category, then requery the   '  product list that's filtered on category   Me.cmbProductID.Requery   ' Set the Product to the first row in the new list   Me.cmbProductID = Me.cmbProductID.ItemData(0)   ' .. and signal Product after update.   cmbProductID_AfterUpdate End Sub Private Sub Form_Current()   ' If we have a valid Category Description on this row...   If Not IsNothing(Me.CategoryDescription) Then     ' Then make sure the unbound combo is in sync.     Me.cmbCategoryDescription = Me.CategoryDescription   End If   ' Requery the product list to match the current category   Me.cmbProductID.Requery End Sub Private Sub ProductName_GotFocus()     ' We have some tricky "overlay" text boxes here that     ' shouldn't get the focus.  Move focus to the underlying     ' combo box if that happens.     Me.cmbProductID.SetFocus End Sub

As expected, the code requeries the Product combo box whenever you pick a new category (cmbCategoryDescription_AfterUpdate) or when you move to a new row (Form_Current). It also keeps the unbound combo box in sync as you move from row to row as long as the underlying record has a valid category. (A new record won’t have a related CategoryDescription until you choose a Product ID, so the code doesn’t update the unbound combo box on a new record.) Finally, if you try to click in CategoryDescription or ProductName, the GotFocus code moves you to the underlying combo box where you belong. Why didn’t we simply set the Enabled property for CategoryDescription and ProductName to No? If you do that, then you can’t ever click into the category or product combo boxes because the disabled text box overlaid on top would block you.

Note 

If you want to see what the filtered combo box looks like without the overlay, make a backup copy of Contacts.accdb, open the fsubContactProducts form in Design view, move the Category Description and Product Name text boxes down similar to Figure 20–19, and save the form. Now open the frmContacts form and click on the Products tab.