ACC2000: How to Have List Box Respond to Entire String You Type

ID: Q218621

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SUMMARY

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

Open the sample database Northwind.mdb.

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]

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

Save the form and open it in Form view.

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:


   Entry      Selected Item
   -----      -----------------------
   b          Berglunds snabbkop
   bl         Blauer See Delikatessen
   blo        Blondel pere et fils

Additional query words: inf

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto