• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Combo box wildcard as I type (1 Viewer)

waka

Registered User.
Local time
Today, 01:23
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, 01:23
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
Today, 16:23
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, 01:23
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