Filter Listbox

[Anesthaesia]

Hi,

I want to filter items in a listbox as I type chrs in a textbox. Any idea on the easiest way to do this?

Example List:
Apple
Animal
Banana

Typing "a" in the text box will remove "banana" from the list, typing "ap" will leave a list with only "apple". This will happen as the chrs are typed, and not using a "filter" command button.

Any help would be great.

Cheers,
D

 

[strongm]

What have you tried so far?

 

[Anesthaesia]

The only thing I can think of is adding the contents to an array, but at that point I'm a bit stuck.

Also, the slow refresh rates of listboxes was a concern I had, so wasn't sure if there was a right and wrong way of doing it.

I assume the code will go in the textbox change event which should cause it to be filtered as keys are pressed.

 

[Anesthaesia]

I actually kind of answered my own question there...

I have populated 2 arrays, one which is created by filtering the 1st, which contains all entries.

This seems to work OK (listbox population slows it down a bit with a lot of records).

How do I filter the beginning of the word though? At the moment the filter applies to a chr anywhere in the word (so "a" includes "banana" in the first example

 

[BobRodes]

Ok, that's ingenious, but you can also do it with recordsets. Here's something I knocked together. You'll need a form with a combo box on it. Set the Style property to 1 - SimpleCombo.

Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SkipChange As Boolean

Private Sub Combo1_Change()
'The change event often gets fired when we don't want to use it, so very often you'll see a flag
'that prevents the code logic from being fired at the wrong time.  We want the change event
'every time the user changes the text in the combo box, but we don't want it every time the
'we change the text programmatically.
If SkipChange Then
    Exit Sub
End If
'Change the recordset's filter to the contents of the combo box, or if the combo box is empty,
'remove the filter.
rs.Filter = IIf(Combo1.Text = "", "", "name like '" & Combo1.Text & "%'")
PopulateListBox
End Sub

Private Sub Form_Load()
'Opens up a connection, uses it to open a recordset.  Recordset gets all the authors from the
'pubs sample database that ships with SQL Server, and puts them in a field called Name.
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'connection source is assumed to be an SQL Server instance on the local machine
cn.Open "Provider=sqloledb;Data Source=.;Initial Catalog=pubs;Integrated Security=SSPI;"
'select statement takes last name, adds a comma and a space, adds the first name, and stores
'the result in a field called Name.
rs.Open "select au_lname + ', ' + au_fname as Name from authors", cn
'do the initial population of the list box
PopulateListBox
End Sub

Private Sub PopulateListBox()
Dim cText As String
With Combo1
    'Store the existing text to a variable, since the Clear method removes it.
    cText = .Text
    .Clear
    'If you don't set the SkipChange flag here, you'll get an out of stack space error due to
    'an unintended recursive procedure call.  Setting the text property calls Combo1_Change,
    'which calls this proc, and so on until you run out of memory.  To demonstrate this,
    'comment out the SkipChange variable and examine the call stack when you get the error.
    SkipChange = True
    'Now, put back the text that we removed with the Clear method
    .Text = cText
    SkipChange = False
    'Then, put the cursor at the end of the string
    .SelStart = Len(.Text)
    'Here, we're going to exit if there are no matches to the string entered in Combo1, since
    'we don't have any repopulating to do.  Not to mention, we'd get an error at the MoveFirst
    'method if the recordset's empty.
    If rs.EOF And rs.BOF Then
        Exit Sub
    End If
    'Finally, repopulate the list box with the filtered recordset.
    rs.MoveFirst
    Do Until rs.EOF
        .AddItem rs(0)
        rs.MoveNext
    Loop
End With
End Sub

This runs quite comfortably quickly; I don't notice any delay at all.

HTH

Bob

 

[BobRodes]

I was also going to say, of course, I'm not working with a very big list. But I hit the submit button by mistake.