HOW TO: Have List Box Respond to Entire String You Type in Access 2000

When you type characters in a list box, the list box selects the first item in the list that corresponds to the last keystroke that you type.

For instance, if you type "f", the list box selects the first item in the list that begins with "f". If you type "fa", the list box selects the first item in the list that begins with "a", even though you may expect it to select the first item that begins with "fa". This article describes two ways to have the list respond to the entire string of characters that you type.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Method 1: Use a Combo Box

Use a combo box instead of a list box. Using a combo box automatically selects the first item in the list based upon all characters that you type.

Method 2: Use an Unbound Text Box with a Visual Basic for Applications Procedure

Use an unbound text box to type in, and use a procedure on the Change event of the text box to set the value of the list box to the first item that matches the current text in the text box. To do so, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Open the sample database Northwind.mdb.
2. Create a new form called frmTestList with the following properties:

      Form: frmTestList
      -------------------------
      Caption: frmTestList
   
      ListBox
      -------------------------
      Name: lstCompanyList
      RowSource: SELECT CustomerID, CompanyName
                 FROM Customers
                 ORDER BY CompanyName
      Column Count: 2
      Column Widths: 0";1"
      Bound Column: 1
   
      TextBox
      ---------------------------
      Name: txtCompanyName
      Caption: Company Name
      OnChange: [Event Procedure]
   					

3. Add the following code to the Change event of the text box:

   Private Sub ctrlCompName_Change()
       Dim db As DAO.Database
       Dim rs As DAO.Recordset
       Dim strSQL As String
       Set db = CurrentDb
       strSQL = "SELECT CustomerID, CompanyName " & _
                "FROM Customers WHERE CompanyName LIKE '" & _
                Me!txtCompanyName.Text & "*'"
           
       Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
   
       If rs.RecordCount > 0 Then
           rs.MoveFirst
           Me!lstCompanyList = rs!CustomerID
       End If
   
       rs.Close
       Set rs = Nothing
       Set db = Nothing
   End Sub
   					

4. Save the form and open it in Form view.
5. In the text box, slowly type blo and note the selection in the list box as you type each character.

Note that you see the following names highlighted according to the number of characters that you type: