Filter combo box results with like operator as you type

Question

Hi Gurus of Access,

I have tried unsuccessfully to filter a combo box list results using the like operator. Don't know if it's possible. Here is my combo box rowsource

SELECT tblDancers.ID, IIf(IsNull([tblDancers].[Fname]),[tblDancers].[Sname],[tblDancers].[Fname] & "  " & [tblDancers].[Sname]) AS Dancer, tblDancers.BirthDate
FROM tblDancers
WHERE (((IIf(IsNull([tblDancers].[Fname]),[tblDancers].[Sname],[tblDancers].[Fname] & "  " & [tblDancers].[Sname])) Like "*" & [Forms]![frmDancers].[cboFindDancer].[value] & "*"))
ORDER BY tblDancers.Sname, tblDancers.Fname, tblDancers.BirthDate;

I would imagine that the rowsource/combobox needs to be requeried but then I get error 2118 "you must save the current field before you run the requery action"

Any workaround or am I approaching this badly?

Answer 1

Comboboxes can do AutoComplete, so I'm not sure what you're trying to accomplish. By that, I mean that you can setup your combo to do the "type ahead" without worrying with using the LIKE operator.

That said - where are you trying to set the combo's Rowsource? What event are you using?

---

-- Scott McDaniel, Microsoft Access MVP

Answer 2

Hi Scott,

African names are spelt so many ways for example Dhlamini or Dlamini. So if I'm looking for all the options of that name I'd like to type "lami" which using the like condition will give me all spellings whereas in a normal combo I'd have to try different combinations & when there's a queue of people waiting for me that's just added pressure & frustration.

So the sql above is the combo box data query. If I use the on change event to requery then I get the error 2118 as mentioned.

Answer 3

I'd recommend using a textbox combined with a listbox for this rather than a combo box.  (You can also include other parameters, such as First Name when filtering the results)

A problem with your code... the VALUE property of a control (as in your code) is the value that is stored in the table.  It does not change until *after* the control has updated.  If you want to see results 'as you type', you should use the TEXT property of the control, which you can use when the control has the focus such as in the Change Event.

Assuming the textbox/listbox scenario I described, your code in the CHANGE event would look something like this:

Me.MyListbox.RowSource = "SELECT LastName & ', ' & FirstName FROM MyTable WHERE LastName LIKE '*" & Me.txtLastName.TEXT & "*'"

Depending on the nature of your rowsource query and data, this might work smoothly, or it might be agonizingly slow.

---

Miriam Bizup Access MVP

Answer 4

Also, check out the technique shown here: Combos with Tens of Thousands of Records

Hope that helps...

Answer 5

>>>So the sql above is the combo box data query. If I use the on change event to requery then I get the error 2118 as mentioned.

According to your description, I have made a sample and reproduced your issue. So I suggest that you could set ComboBox RowSource again when OnChange event raise, refer to below code:

Private Sub cmbCity_Change()
  cmbCity.RowSource = "Select CityName from TB_City where CityName like '*" & Me.cmbCity.Text & "*'"
End Sub

Private Sub Form_Load()
   cmbCity.SetFocus
   cmbCity.RowSource = "Select CityName from TB_City where CityName like '*" & Me.cmbCity.Text & "*'"
End Sub

Answer 6

David you're my hero!

So what I've done is create 2 queries because of the IIf statements in my sql. The one has no where condition & that's the "load" status as well as the KeyPress event rowsource. The other including the "like" condition is the on_change event rowsource.

What I did is set AutoExpand to "No". Added the KeyPress event to have the following

If KeyAscii = 27 Then
    Me.cboFindDancer.RowSource = "qryDancerComboStart"
        Me.cboFindDancer.Text = ""
End If

in order to remove the filter in case the user escaped out then went back into the combo and also added the dropdown command in the on_change event.

It all works perfectly as if the combo box was designed that way. Thanks for the help

Answer 7

First of all thanks Hugh and David for the contribution to this control.

I have encountered a problem which I am not sure if you share also: apart from the first row of the datasheet, the combobox of all other rows doesn't 'requery' until you mouse over it.

any ideas please?