JamesGarrison-3189 avatar image
0 Votes"
JamesGarrison-3189 asked JamesGarrison-3189 commented

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

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
    If Len(fltrMemberName.text) = 0 Then
        Me.Form.Filter = ""
        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
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...

[memberName] like '*garx*'

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.

vba001.png (59.1 KiB)
vba002.png (35.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JamesGarrison-3189 avatar image
0 Votes"
JamesGarrison-3189 answered JamesGarrison-3189 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It's often not realised that applying a filter to a form reloads the form's recordset, as with requerying a form, hence the behaviour you have observed. If you need to refer to a value which will be affected by the reloading of the recordset the same technique should therefore be used as when requerying the form, and the value assigned to a variable before the form is requeried. Subsequent references to the value should therefore be to the variable, not to the object of which the value was a property.

0 Votes 0 ·

I understand all that now. However, there's still a bug.

Access should not be throwing that error on later iterations. Once it has thrown the error the first time, all subsequent references to the .text property throw the same error. The only way to clear the condition is to close and reopen the form.

0 Votes 0 ·

Plus, in my case, it is necessary to reset the control's SelStart property, so operating on a copy of the value is not useful. Otherwise, for some reason, Access selects the entire contents of the field, such that typing a subsequent character overwrites the previously entered data, when the expected behavior is that I can type into the field normally.

When you set the filter to a query that returns no rows, Access seems to latch itself into a state where you can no longer refer to .text on that control until the form is closed and reopened. This happens regardless of what the user does, such as manually click in the field. From that point forward, .text is inaccessible when it should be. This is the bug.

0 Votes 0 ·
KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered JamesGarrison-3189 commented

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)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, the Value property contains the "old" value, prior to the current change. I need the changed value.

Regardless, the control DOES have the focus currently, so the error is being thrown incorrectly. Plus the fact that afterwards any reference to .text fails is a strong indication that there is a bug in Access. Unless, of course, someone can provide an explanation of what is going on here. I suspect setting the Form.Filter property is the culprit, but there's nothing in the docs that suggest it should cause a problem.

0 Votes 0 ·