Combobox Filter

[Rustman]

I'm trying to filter a list using this set of instructions: Using a Combo Box to search as you type - Microsoft 365 Blog

Seems to work fine, except if I filter the list to zero results. When the filtered list goes to zero, it throws the following error and then stops working:

"Run-time error '2185': You can't reference a property or method for a control unless the control has the focus."

What I have implemented is exactly what is in that link, verbatim. How to I prevent that error from occurring?

[orange]

Might be quicker to resolve if you posted a copy of your database (zip format) showing the issue.

[Rustman]

Might be quicker to resolve if you posted a copy of your database (zip format) showing the issue.

Good point. It's attached. The form in question is the main form. If you use the search bar, you'll see that it works fine until the search returns 0 results. Then it errors out.

Company Contacts.zip

[Welshgasman]

Well I am as puzzled as you.
I could also get the error on your very first line of the event.
I added an extra set focus as I have seen a comment that it can lose focus under certain conditions, but that still errors out.

Private Sub cboFilter_Change()
    Me.cboFilter.SetFocus

    If Nz(Me.cboFilter.Text) = "" Then
        Me.Form.Filter = ""
        Me.FilterOn = False
    ElseIf Me.cboFilter.ListIndex <> -1 Then
        Me.Form.Filter = "[Name] = '" & _
        Replace(Me.cboFilter.Text, "'", "''") & "'"
        Me.FilterOn = True
    Else
        Me.Form.Filter = "[Name] Like '*" & _
        Replace(Me.cboFilter.Text, "'", "''") & "*'"
        Me.FilterOn = True
    End If
    Me.cboFilter.SetFocus
    'DoCmd.GoToControl ("cboFilter")
    'Me.cboFilter.SelStart = Len(Me.cboFilter.Text)
End Sub

[Welshgasman]

OK, I asked elsewhere and have been told

That once no records are available you cannot set or keep focus on that control.

This is due to it being unbound I believe? which now makes sense
So they suggest surrounding it with a recordset recordcount.? However I have been getting the error on the very first line as well when trying to rerun.
I've tried to compare the Northwind form with yours and the properties and apart from not having a rowsource and that form being a split form, I could not see any difference?

[Micron]

Remove the subform data source and the problem goes away. That means it has nothing to do with being unbound (which I don't buy anyway). If you put a msgbox line just after the last setfocus, this will report that combo has the focus msgbox Me.Activecontrol.Name
So something is taking focus away from the control. It has to be that monster query. I looked at it thinking there must be a calculation in it but there isn't. Then I see it's a bunch of subqueries as well but none of those have calculations in them - the theory being that a calculation is going to some other control to get a value to evaluate the expression/calculation.

The design is all wrong IMO and perhaps that is the underlying cause. Your subform is based on a spider web of outer joins and subqueries because data that should be in fields on a table is all busted up into tables. If you want to contact me, look up my phone, address, email, etc in some "members" or "contacts" table - not stitch it together from multiple tables.

[ssanfu]

I have been looking at the table designs/relationships also.

The sub form has that huge query and the filter combo box has the same query for the row source.
(BTW, The "States" table is missing "Virginia" - the table has only 49 states..)

The row source for the combo box could be a query with the table contacts that concatenates the First, Middle and Last Names (and use FullName, not NAME).
The sub form "MainActiveSsubform" record source could be a query with tables Contacts, Contact_Phone and Contact_Email. (shouldn't use hyphens in object names).

[Welshgasman]

I agree with all of the above, however I cannot see why it appears to work flawlessly in the Northwind DB and not in the O/P's DB, regardless of the bad structure?
I added the combo to the Customer list form as per the link and I cannot get it to fail when there are no records matching the data entered.