Wildcard "*" in combobox for searching

[Evecuz]

Hello guys,

I have a form in Excel working with a ComboBox. The Combo load the items through a database connection from a table in SQL server. It works fine by default getting the description of any item while you are typing.

My problem starts when I need to use a wildcard "*" to search and filter a bunch of items with some pattern. For instance: '*Acme*' or 'The Bo*'.

This is my current piece of code populating the combo and working. Hpow could I make this work with wildcard??????

-----------------------------------------------------------------------------------------------------------
Set Rs4 = New ADODB.Recordset
Rs4.ActiveConnection = conn2
'Call ActionImport
Rs4.Open "Select distinct Descriptions from Items where Description is not Null Order by Description"
Rs4.MoveFirst
With Me.ComboBox_Descriptions
.Clear
Do
.AddItem Rs4![Description]
Rs4.MoveNext
Loop Until Rs4.EOF
End With

Rs4.Close
conn2.Close
End Sub
-----------------------------------------------------------------------------------------------------------------------

Thanks so much in advance for any suggestion or advice

Evecuz

 

[SkipVought]

hi,

[highlight #FCE94F]sStr = "*Acme*"[/highlight]
Rs4.Open "Select distinct Descriptions from Items where Description [highlight #FCE94F]Like '" & sStr & "'[/highlight] Order by Description"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Also, in your code you assume you always will have some records in Rs4, but what about if your Select statement returns 0 records?

How about:

[blue]Dim strSQL As String[/blue]
Set Rs4 = New ADODB.Recordset
Rs4.ActiveConnection = conn2[green]
 'Call ActionImport[/green]

Me.ComboBox_Descriptions.Clear

With Rs4[blue]
    strSQL [/blue]= "Select distinct Descriptions from Items where Description is not Null Order by Description"
    .Open strSQL
    If .RecordCount > 0 Then   [green]'or
        'If .BOF <> .EOF Then[/green]
        .MoveFirst
        Do
            Me.ComboBox_Descriptions.AddItem ![Description]
            .MoveNext
        Loop Until .EOF
    End If
End With

Rs4.Close
conn2.Close
End Sub

---- Andy

There is a great need for a sarcasm font.