Combo box wildcard as I type (1 Viewer)

waka

Registered User.
Local time
Today, 12:37
Joined
Jul 15, 2009
Messages
20
Hi,

I'm trying to create a combo box where I can type in "oak" and see every plant that has oak in it's common name. I would get southern red oak, willow oak, jack oak, etc.

Ideally, the combo box would change choices as I typed. So if I type "al" I would start seeing "alderleaf", "Alaska cedar", and "American alumroot".

Right now I can type in "South" and I'd get anything that begins with "South" and anything that comes after alphabetically.

I found a similar thread and tried their suggestions of creating a change event. Here is the VBA for the combo box, cmbo_Common:

Private Sub cmbo_Common_Change()
Me.cmbo_Common.RowSource = _
"SELECT ID, Common " & _
"FROM tlu_Plants " & _
"HAVING Common LIKE '*" & Me.cmbo_Common.Text & "*' " & _
"ORDER BY Common;"

Me.cmbo_Common.Dropdown
End Sub

Private Sub cmbo_Common_AfterUpdate()
' Find the record by Common name that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![cmbo_Common], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cmbo_Common = Null
End Sub


Using this code, I see my full list when I click on the arrow. If I try to type a letter in I don't get any results (ie. I see a blank box). If I pick one of the plants originally listed, the record's form appears as it should, but then I can't select another plant in the combo box - it appears blank again.

Any help with my code would be greatly appreciated!
 

waka

Registered User.
Local time
Today, 12:37
Joined
Jul 15, 2009
Messages
20
I tried using the Search Tool, but for some reason can't get the search box to link to the listbox. Here's my code:

Private Sub QuickSearch_AfterUpdate()
' Find the record by Common name that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![QuickSearch], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Search_Change()
Dim vSearchString As String
vSearchString = Search.Text
Search2.Value = vSearchString
Me.QuickSearch.Requery
End Sub

I'm new to this so I apologize if I'm making dumb mistakes!
 

wazz

Super Moderator
Local time
Tomorrow, 03:37
Joined
Jun 29, 2004
Messages
1,711
what if you put the sql string into the row source of the combobox and for cmbo_Common_Change() just put Me.cmbo_Common.Requery?
 

waka

Registered User.
Local time
Today, 12:37
Joined
Jul 15, 2009
Messages
20
I successfully got the Search Tool to work! I realized I had forgotten to put in the

Like "*" & [Forms]![frm_Plants]![Search2] & "*"

In the combo box rowsource. No wonder why it wasn't linking! Hitting head...

Thanks John!
 

Users who are viewing this thread

Top Bottom