searching on a listbox

[cisy]

I have a combobox populated with a long list of names. When I type in the first few letters, the combobox control automatically searches the names for the closest match. I am wondering how I can make my listbox behave exactly like that. In a combobox, let say I type in hans, the control drops down to s, the last letter of my typing but what I actually want to search is hans.

[HansV]

List boxes in Access don't have this feature.

One option is to press the initial character repeatedly. For example, if you press H several times, you'll cycle through the list items beginning with H.

Otherwise, you'd have to add a text box - see Making a Searching List Box in Access 2007 (works in other versions too).

[cisy]

Hi Hans,

I coded my program differently without referring to the rowsource explicitly. It works fine once I have entered all the required digits for customer id and press the Enter key. It does not perform the search on each of the digit that I enter though. I tried the change event but it bombed out due to the null reference to the textbox. I am wondering if you can make it work better.

Thanks

[HansV]

There are several problems.

When the text box has the focus, you must refer to the current text in the text box (as it is being entered/edited by the user) as the Text property, not the Value property - the latter is the "stored" value which does not change while the user is editing the text, only when the user moves to another control or record.

The index of the list box is zero-based, it runs from 0 to ListCount - 1.

And a minor point: in the declaration

Dim intX, intCount, intL As Integer

only the variable intL is declared as an Integer. The others aren't assigned a specific type, so they are Variants. If you want to declare all three as Integer, you must use

Dim intX As Integer, intCount As Integer, intL As Integer

or (my preference)

Dim intX As Integer
Dim intCount As Integer
Dim intL As Integer

So try this version of the code:

Code: Select all

Private Sub SearchEntry()
    Dim intX As Integer
    Dim intCount As Integer
    Dim intL As Integer
    Dim blnFound As Boolean
    intCount = lstCustomer.ListCount
    intL = Len(txtID.Text)
    For intX = 0 To intCount - 1
        If Left(lstCustomer.ItemData(intX), intL) = txtID.Text Then
            lstCustomer = lstCustomer.ItemData(intX)
            blnFound = True
            Exit For
        End If
    Next
    If blnFound = False Then
        lstCustomer = lstCustomer.ItemData(0)
        MsgBox "Sorry, cannot find customer"
    End If
End Sub

Private Sub txtID_Change()
    SearchEntry
End Sub

[cisy]

Thanks Hans for pointing out the errors. So the dim statement works differently in VBA as what it does in VB?

Thanks again

[HansV]

The "old" programming language BASIC used the convention that all variables are Variants unless explicitly declared as something else, and this has been retained in VB6 and VBA. It has been changed in VB.Net - there, the line

Dim intX, intCount, intL As Integer

declares all three variables as integers, similar to the way declaring variables works in most other programming languages.