Form Filter returning no records causes Error 2185: Can't reference a property for a control unless the control has the focus

Question

I have a continuous form with a simple query as the data source.

In the form header I have an unbound control into which the user types a partial search string.

In the onChange for that control I update the form filter dynamically. This all works exactly as expected. The image above shows the form after entering the search string gar. At each keystroke the result set narrowed down to the appropriate rows. Here's the form's entire code module.

   Option Compare Database  
   Option Explicit  
     
   Private Const wildcards As String = "*?"  
     
   Private Sub fltrMemberName_Change()  
       Dim fltr As String  
       fltrMemberName.SetFocus  
       If Len(fltrMemberName.text) = 0 Then  
           Me.Form.Filter = ""  
       Else  
           fltr = fltrMemberName.text  
           Me.Form.Filter = makeFilter("memberName", fltr)  
           fltrMemberName.SelStart = Len(fltrMemberName.text) <<<<< Error Here  
       End If  
       Me.FilterOn = True  
   End Sub  
     
   Private Sub Form_Load()  
       Me.Filter = ""  
       Me.FilterOn = True  
       fltrMemberName.SetFocus  
   End Sub  
     
   Private Function makeFilter(field As String, ByVal fltr As String) As String  
       If Not InStr(1, wildcards, Left(fltr, 1)) Then fltr = "*" & fltr  
       If Not InStr(1, wildcards, Right(fltr, 1)) Then fltr = fltr & "*"  
       makeFilter = "[" & field & "] like '" & fltr & "'"  
   End Function  

Everything works fine UNTIL: the user types a character which causes the filter to return no records. At that point, I see:

So in the immediate window...

   ?me.Form.Filter  
   [memberName] like '*garx*'  
   ?me.FilterOn  
   True  
   ?me.ActiveControl.Name  
   fltrMemberName  

Huh? Even VBA itself thinks the control has the focus, but it still complains it DOESN'T have the focus. Clearly the error message is wrong.

I even added fltrMemberName.SetFocus immediately before the failing statement, but this doesn't fix the issue.

So, what is the REAL problem here?

Obviously, I can trap that error and ignore it, but I have seen this error pop up in similar contexts, all having to do with OnChange and keyboard events.
I'd like to understand what's actually happening here, so I can learn how to get around it.

EDIT: It gets worse...

----------------------

I add On Error Resume Next just before the failing statement. That gets past the error, but from that point onward the same error occurs on ANY attempt to reference the .text property of that control. Even entering Me.ActiveControl.Text in the immediate window results in Error 2185, which is oxymoronic. How can the control with the focus not have the focus?

At this point, the only remedy I've found is to close and reopen the form. Nothing else resets the error condition, and on subsequent entries to the onChange method, the initial .setFocus doesn't seem to do anything.

How do I get some attention from Microsoft on this one? Don't tell me to use the Feedback Hub, I've tried that several times with other issues in the last year, and it's just a black hole. Stuff goes in but absolutely nothing comes out in my experience.

Answer 1

After more research I've determined that the culprit is the act of changing the state of the form's filter.

A change to Form.Filter or Form.FilterOn that causes a new filter to be applied has the following side-effects:

• The change to the current control is committed (even if that control is unbound)
• The committed change fires BeforeUpdate, AfterUpdate, Exit and LostFocus.
• The focus moves to the first enabled control in the first record of the continuous form

If the code that changes the filter is in the Change() handler, the other events fire, but when control returns to the Change() handler proceeds as if nothing happened
until the applied filter fails to return any rows.

In that case, and only in that case, on the next attempt to reference the control's .text property Error 2185 is thrown, and from that point on until the form is closed and reopened all attempts to refer to the .text property fail with Error 2185.

I have created a detailed trace using Debug.Print statements that demonstrates what is going on, and am now convinced this is an undocumented side-effect of setting filter criteria that cause the form to return no rows.

So, my "workaround" is to preview the filter by applying it first to a RecordsetClone of the form and checking the RecordCount. If the value is zero, the filter is not set, which prevents the error from happening.

Answer 2

To reference the Text property of a control the control must be the current active control. However, you probably don't need to reference the Text property at all. Referencing the Value property does not need the control to be the active control. The Value property is the default property, so can be omitted. The reference would then simply be Len(fltrMemberName)