1.14 Create a Query That Uses Case-Sensitive Criteria

2.8 Make a Simple "Searching" List Box

2.8.1 Problem

You'd like to create a text box/list box combination like the one in Windows Help. As you type in the text box portion of the control, you want the list box to scroll to match whatever's been typed so far. You know you could use a combo box for this, but the combo box keeps closing up. You want something that's permanently open.

2.8.2 Solution

Entering a portion of the value they're looking for and seeing the matches displayed as users type is an excellent way to find specific values in a list. You get the best of both worlds: the functionality of a combo box and the "permanently open" look of a list box.

The key to implementing this functionality is the text box's Change event. Every time the text in the text box changes, the code you'll use will automatically find the matching value in the associated list box. You'll be able to call a function that will handle all the work for you. In addition, because searching through indexed tables is so much faster than walking through dynasets (the results of running a query or a SQL expression), this solution offers two solutions to this problem: one for list boxes that are bound to tables and another for list boxes that are bound to queries or SQL expressions. Figure 2-15 shows frmSearchFind in action.

The methods you'll find in this solution apply only to bound list boxes.

Figure 2-15. Using Incremental Search on frmSearchFind

To test out the functionality, open the database 02-08.MDB and then open either frmSearchFind or frmSearchSeek. As you type in the text box, you'll see the associated list box scroll to match what you've typed. If you backspace to delete some characters, the list box will still match the characters that remain in the text box. When you leave the text box or click on an item in the list box, you'll see the full text of the chosen item in the text box. The functionality is the same no matter which form you use. frmSearchSeek will look up items faster, though, because it's guaranteed to use an index to do its work.

Follow these steps to build a form like frmSearchFind, which will use a query or SQL expression as the row source for the list box:

1. In your own database, create a new form that contains at least a text box and a list box. For the sake of this example, name the text box txtCompany and the list box lstCompany.

2. Set properties, as shown in Table 2-6.

    

   Table 2-6. Controls and properties for search project form

   Control type	Property	Setting
   Text box	Name	txtCompany
   	OnExit	[Event Procedure]
   	OnChange	[Event Procedure]
   List box	Name	lstCompany
   	AfterUpdate	[Event Procedure]
   	RowSource	qryCustomers
   	ColumnCount	2
   	ColumnWidths	0
   	BoundColumn	2

    

3. Import the table Customers and the query qryCustomers from 02-08.MDB.

4. Put the following code in the lstCompany_AfterUpdate event procedure:

   Private Sub lstCompany_AfterUpdate (  )    acbUpdateSearch Me!txtCompany, Me!lstCompany End Sub

5. Put the following code in the txtCompany_Change event procedure:

   Private Sub txtCompany_Change (  )    Dim varRetval As Variant    varRetval = acbDoSearchDynaset(Me!txtCompany, _      Me!lstCompany, "Company Name") End Sub

6. Put the following code in the txtCompany_Exit event procedure:

   Private Sub txtCompany_Exit (Cancel As Integer)    acbUpdateSearch Me!txtCompany, Me!lstCompany End Sub

7. Import the module basSearch from 02-08.MDB. This module contains the code that does all the work.

Every time you change the value in txtCompany, Access triggers txtCompany's Change event. The code attached to that event calls down into the common function, acbDoSearchDynaset. In general, the syntax for calling acbDoSearchDynaset is:

varRetval = acbDoSearchDynaset(textbox, listbox, "Field to search")

where textbox is a reference to the text box in which you're typing, listbox is the list box in which you're searching, and "Field to search" is the field in the list box's underlying record source through which you're going to search.

The function acbDoSearchDynaset creates a dynaset-type Recordset object, searches through it for the current value of the text box, then sets the value of the list box to match the value the code found in the underlying record source. Its source code is:

Public Function acbDoSearchDynaset(ctlText As Control, _  ctlList As Control, strBoundField As String) As Variant     ' Search through a bound list box, given text to find from     ' a text box. Move the list box to the appropriate row.     ' The list box can have either a table or a dynaset (a query      ' or a SQL statement) as its row source.     ' In:     '     ctlText: A reference to the text box you're typing into     '     ctlList: A reference to the list box you're looking up in     '     strBoundField: The name of the field in the underlying     '       table in which you're looking for values     ' Out:     '     Return value: Either 0 (no error) or an error variant     '       containing the error number          Dim rst As DAO.Recordset     Dim varRetval As Variant     Dim db As DAO.Database          On Error GoTo HandleErr          Set db = CurrentDb(  )     Set rst = db.OpenRecordset(ctlList.RowSource, dbOpenDynaset)     ' Use the .Text property, because you haven't left the control     ' yet. Its value (or its .Value property) isn't set until you     ' leave the control.     rst.FindFirst "[" & strBoundField & "] >= " & acbcQuote & _                   ctlText.Text & acbcQuote     If Not rst.NoMatch Then         ctlList = rst(strBoundField)     End If     varRetval = acbcErrNoError ExitHere:     acbDoSearchDynaset = varRetval     On Error Resume Next     rst.Close     Set rst = Nothing     Exit Function HandleErr:     varRetval = CVErr(Err)     Resume ExitHere End Function

The example in this solution is also set up so that if you leave the text box, it pulls in the currently selected item from the list box. That means that you can use Tab to leave the text box, and the code will place the value that matches as much as you've typed so far in the text box.

2.8.3 Discussion

Notice that the list box's ColumnCount property is 2 and the ColumnWidths property is 0 in this example. This occurs because the query used, qryCustomers, contains two columns, with the first column hidden in the list box. Because you're searching for the second column, that must be the bound column.

This example, as shown so far, uses a query as the data source for the list box. This method can really slow things down for large data sets, since it's not guaranteed that it will be able to use an index. If possible you should base your list box directly on a table instead, especially if your data set is much larger than a few hundred rows. In that case, you can use the Seek method, which is generally much faster than the FindFirst method used in this example. On the other hand, because it works with only a single table as its data source, it's a lot more limiting.

To use the Seek method, you'll need to change a few properties. To test it out, make a copy of frmSearchFind and call the new form frmSearchSeek. Change the RowSource property of your list box to be Customers, rather than qryCustomers. In addition, change the function that txtCompany calls from its Change event procedure to the following:

Private Sub txtCompany_Change (  )    Dim varRetval As Variant    varRetval = acbDoSearchTable(Me!txtCompany, _     Me!lstCompany, "Company Name", "Company Name")      End Sub

In this case, you'll be calling the acbDoSearchTable function, which searches through an indexed table instead of through an unindexed dynaset. In general, you'll call acbDoSearchTable with the following syntax:

intRetval = acbDoSearchTable(textBox, listBox, "BoundField", "IndexName")

where textbox is a reference to the text box in which you're typing, listbox is the list box in which you're searching, "BoundField" is the field in the list box's underlying record source through which you're going to search, and "IndexName" is the name of the index you're going to use. (Usually it'll just be "PrimaryKey", but in this example use "Company Name". This table is indexed both on the Customer ID field (the primary key) and the Company Name field; you're using the Company Name index.)

The code for acbDoSearchTable is almost identical to that for acbDoSearchDynaset, except that the table search uses the Seek method to search through an indexed recordset instead of the FindFirst method. Because it can use the index, it should be able to find matches much more rapidly than acbDoSearchDynaset.

 

Because acbDoSearchTable requires that the list box's record source be a table, it will trap for that error and return a nonzero value as an error variant if you try to use it with some other data source. In addition, the function will not work correctly if you mismatch the bound field and the index. That is, the bound field must be the only field in the selected index).

 

The code for acbDoSearchDynaset, acbDoSearchTable, and acbUpdateSearch is in the module basSearch. If you want to use this functionality in other applications, import that module into your application and follow the steps outlined earlier to set the properties for your text and list boxes.