Making a Searching List Box in Access 2007

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to create a search list box by using a text box and a list box together, along with code that searches through a recordset or with code that searches directly through a table. (6 printed pages)

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007

Ken Getz, MCW Technologies, LLC

April 2009

The text box / list box combination in Windows Help gives users a way to view the possible matches in a list as they type into a search box. With a few controls and some code, you can do the same thing in a combo box.

Making A Searching List Box in Access 2007

Watch the Video

Length: 09:43 | Size: 10.4 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the sample database

An excellent way to find a value in a list is to view the possible matches to the text as you type. Running a search that way is optimal because it gives you the functionality of a combo box and the "permanently open" look of a list box.

The key to such an implementation is the Change event associated with the text box. Each time the text in the text box changes, the function that you call after the Change event automatically finds the closest matching value in the list box.

In addition, because searching through indexed tables is so much faster than walking through a recordset (the results of a query or a SQL expression), this implementation provides a common solution to two problems — the list box that is bound to tables and the list box that is bound to queries or SQL expressions.

Figure 1. Using incremental search in frmSearchFind.

Using incremental search in frmSearchFind

The methods in this solution apply only to bound list boxes.

To test the functionality, open the database CreateASearchingListBox.accdb and then open either frmSearchFind or frmSearchSeek. As you type in the text box, the associated list box scrolls to match what you typed. If you backspace to delete some characters, the list box matches the characters that remain in the text box. When you leave the text box or click an item in the list box, the full text of the chosen item appears in the text box. The functionality is the same in both forms, but frmSearchSeek looks up items slightly faster because it uses an index to do its work. The difference in speed becomes more apparent if your data source has a huge number of rows.

To build a form like frmSearchFind that uses a query or SQL expression as the row source for the list box, do the following:

In your own database, import the table named Customers, and the query named qryCustomers from the sample database, CreateASearchingListBox.accdb.
Examine the table and the query to make sure that you understand the data that each returns.

Figure 2. The sample table and query
Create a new form in your database 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.

Use the following table to specify properties for the controls.


TextBox	Name	txtCompany
	OnExit	[Event Procedure]
	OnChange	[Event Procedure]
ListBox	Name	lstCompany
	AfterUpdate	[Event Procedure]
	RowSource	qryCustomers
	ColumnCount	2
	ColumnWidths	0
	BoundColumn	2

Import the table Customers and the query qryCustomers from CreateASearchingListBox.accdb. Alternatively, create a similar table and query using your own data; the instructions here assume you have used the sample table and query.
Import the module basSearch from CreateASearchingListBox.accdb. This module contains the code that does all the work.

Add the following lstCompany_AfterUpdate event handler.

Private Sub lstCompany_AfterUpdate()
    UpdateSearch Me.txtCompany, Me.lstCompany
End Sub

Add the following txtCompany_Change event handler.

Private Sub txtCompany_Change()
    SearchRecordset Me.txtCompany, _
     Me.lstCompany, "Company Name"
End Sub

Add the following txtCompany_Exit event handler.

Private Sub txtCompany_Exit(Cancel As Integer)
    UpdateSearch Me.txtCompany, Me.lstCompany
End Sub

Each time that you change the value in txtCompany, Access triggers the Change event in txtCompany. The code attached to that event calls the common function, SearchRecordset. In general, the syntax for calling SearchRecordset is as follows.

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

Textbox is a reference to the search text box in the list box, listbox is the list box that contains the text box, and "Field to search" is the field in the list box's underlying record source through that the code searches.

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

Const constErrNoError = 0
Const constQuote = """"

Public Function SearchRecordset(ctlText As Control, _
 ctlList As Control, strBoundField As String) As Variant

    ' Search through a bound listbox, given text to
    ' find from a text box.
    ' Move the list box to the appropriate row.
    ' The listbox can have either a table or a dynaset
    ' (a query or an 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've not yet left the
    ' control.  Its value (or its .Value property) isn't
    ' set until you leave the control.
    
    rst.FindFirst "[" & strBoundField & "] >= " & _
      constQuote & ctlText.Text & constQuote
    
    ' Find a match? Set the ListBox to
    ' the correctly value.
    If Not rst.NoMatch Then
        ctlList = rst(strBoundField)
    End If
    varRetval = constErrNoError

ExitHere:
    SearchRecordset = 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 retains the currently selected item from the list box, even if you leave the text box. Thus, if you tab out of the text box after you type a fragment, the code places the closest match to that fragment from the list into the text box.

Read It

As you work through the code, note that the ColumnCount property in the list box is 2 and that the ColumnWidths property is 0. This occurs because the query used, qryCustomers, contains two columns, with the first column hidden in the list box. Because you're searching for data in the second column, it is that column that must be the bound column.

So far, this example uses a query as the data source for the list box. This method can slow the search through large data sets because the query might not be able to use an index.

If possible, base your list box directly on a table instead, especially if your data set is larger than a few hundred rows. If you base your list box on a table, you can use the Seek method, which is generally faster than the FindFirst method. One limitation to using the Seek method, however, is that it works only with a single table as its data source.

To use the Seek method, make a copy of frmSearchFind and call the new form frmSearchSeek. Then, change the RowSource property of your list box to Customers instead of qryCustomers, and change the function that txtCompany calls from its Change event procedure to the following.

Private Sub txtCompany_Change()
    SearchTable Me.txtCompany, _
     Me.lstCompany, "Company Name", "Company Name"
End Sub

In this implementation, you call the SearchTable function, which searches through an indexed table instead of through an unindexed recordset.

In general, use the following syntax to call SearchTable.

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

Textbox is a reference to the search text box in the list box, listbox is a reference to the list box that contains the text box, "BoundField" is the field in the list box's underlying record source that the code searches, and "IndexName" is the name of the index that you plan to use. Usually the index name will just be "PrimaryKey", but in this example use "Company Name". The indexed table is indexed both on the Customer ID field (the primary key) and the Company Name field. Use the Company Name index.

The code for SearchTable is almost identical to that for SearchRecordset, 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 find matches more rapidly than SearchRecordset.

Caution
Because SearchTable requires that the record source for the list box is 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 SearchRecordset, SearchTable, and UpdateSearch is in the module basSearch. If you want to use the code 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. In addition, if you import the sample code into a database created in Access 2000 or later, make sure that you use the References command on the Tools menu from within VBA to add a reference to the Microsoft DAO type library. By default, Access applications created in those versions do not include a reference to DAO, and the sample code in this demonstration requires that reference.

Making a Searching List Box in Access 2007

Code It

Read It

Explore It

Additional resources