Error 2185 - Strange behaviour of textbox

[Babycat]

Dear all

I have an unbound textbox names: Txt_FnameFilter, it functions a search for each character keyed in via function: User_FnameFilterParameter (Txt_FnameFilter.text)
This error occurs when no search result found.
I have read about Error 2185, i understand that I can not access Txt_FnameFilter.text when Txt_FnameFilter is not an active control.
However, as the screenshot showing, the control already got focus, but why I still could not access Txt_FnameFilter.text?
It introduces runtime error when i type in immediately window.

Private Sub Txt_FnameFilter_KeyUp(Keycode As Integer, Shift As Integer)
'On Error GoTo ErrorHandler
Dim prevtext As String

    Txt_FnameFilter.SetFocus
    If Nz(Txt_FnameFilter, "") <> Nz(Txt_FnameFilter.text, "") Then ' this is to avoid searching when press non-character keys such arrow keys
        prevtext = Txt_FnameFilter.text
        User_FnameFilterParameter (Txt_FnameFilter.text)
        Txt_FnameFilter.SetFocus
        Txt_FnameFilter = prevtext
        Txt_FnameFilter.SelStart = Len(prevtext)
    End If
  
UserExitSF:
    Exit Sub
ErrorHandler:
    'MsgBox "There is an error occurred: " & Err.Number & ": " & Err.Description, vbOKOnly + vbCritical
    Resume Next
End Sub

.

 

[Gasman]

So show your code for User_FnameFilterParameter
It does not error on the .text line, before the function?, then it executes that function, then it complains?

 

[MajP]

I have discussed this "bug" before in detail. You need to check to see if 0 records are returned prior to filtering or modify the form design.

Solved - Help with dynamic search on form with read only recordset

I'm stumped, maybe someone else has an idea. Can you attach the db here to play with?

www.access-programmers.co.uk

 

[arnelgp]

consider also using the Change Event of the textbox.

 

[The_Doc_Man]

The problem is that .SelStart is a property that relates to how you would edit the text within that control. See here for explanation:

TextBox.SelStart property (Access)

Office VBA reference topic

docs.microsoft.com

Check the second remark under "Remarks" in the write-up.

Basically, for .SelStart to be available, you have to be editing in that control - which can only occur when the control has focus. Otherwise it is a meaningless property. This situation also occurs for .SelLength and .SelText properties.

What they have in common is that they represent a SELECTION - which significantly differs from selections in Word (for example). In Word you could programmatically define a selection (as zero or more arbitrary contiguous characters) and work on it strictly through VBA to make insertions, deletions, or font-related changes. But in Access, for a selection to exist at all, you have to be working in the area that contains that selection - which is why the control needs to have focus. And according to the error message, it does not.

You perhaps need to check the form's .KeyPreview property because if that is set, both .KeyPress and .KeyUp are FORM events, not control events. If you have .KeyPreview = TRUE then your key up event isn't bound to the control. See this reference:

Form.KeyUp event (Access)

Office VBA reference topic

docs.microsoft.com

Then under remarks, note the third paragraph. Also note the later paragraph that discusses the possibility that a KeyPress can occur for one control but the corresponding KeyUp can occur for a different control if (at the time) that keypress would change focus. (e.g. TAB key does this).

 

[MajP]

The Issue is 100% repeatable. If you search form is a continuous form and allow additions are set to no then the form goes completely blank. This is what causes the focus in the search box to be lost. If allow additions is set to true (does not make sense in a search form IMO) then there is no issue.

My solution is to "roll back" the search. If the user returns no records then do not allow that search and roll back to the previous search.

 

[Babycat]

So show your code for User_FnameFilterParameter
It does not error on the .text line, before the function?, then it executes that function, then it complains?

No, it stops at the yellow line of Txt_FnameFilter.SelStart while it goes through Txt_FnameFilter.Text without any error.
But when i type ?Txt_FnameFilter.Text in immediately window the error also occurs.

 

[Babycat]

I have discussed this "bug" before in detail. You need to check to see if 0 records are returned prior to filtering or modify the form design.

Solved - Help with dynamic search on form with read only recordset

I'm stumped, maybe someone else has an idea. Can you attach the db here to play with?

www.access-programmers.co.uk

Dear MajP

Yes, it seem almost my case, my program has exact symptom of that topic.
as @arnelgp suggested, I also tried on change event but it is same.

The most strange behaviour is that the textbox is already got focus, but vba code insists asking for the focus to access .Text or .SelStart
kind of access bug, i think

I am studying MajP code, it seem very promising in my case.

 

[MajP]

The code in this thread is not the solution. The purpose is to show the problem. I believe the other thread has a solution. However I demoed with a class module.

 

[MajP]

I do this in a class module so look at the findasyoutypeForm example. There is also combo boxes

Solved - FAYT for Multi Column combo code from MajP question

I have used the great code from MajP FAYT on a few forms and some work perfectly and a few have an issue. I read the post Populating Combobox with a lot of data and the link in there. The issues I am experiencing are When I refresh the underlying table (I click a button on my form and it...

www.access-programmers.co.uk

 

[The_Doc_Man]

No, it stops at the yellow line of Txt_FnameFilter.SelStart while it goes through Txt_FnameFilter.Text without any error.
But when i type ?Txt_FnameFilter.Text in immediately window the error also occurs.

See this article:

TextBox.Text property (Access)

Office VBA reference topic

docs.microsoft.com

The paragraph in Remarks after the purple-highlighted area reveals that <control>.Text isn't always available. Again, it relates to something that only exists when the control has focus. So if you try to get to the property and focus has somehow changed, <control>.Text won't be there.

Look at it this way: When you put focus on the textbox, the .Text property springs up and it has the associated properties .SelStart, .SelLength, and .SelText - all of which have to do with what is in .Text while its control has focus.

 

[The_Doc_Man]

This issue is really confusing on when it happens. To demo there are two seemingly identical forms. The filter code is identical. They were built at separate times. Somewhere they are not identical, but I cannot determine what property or setting is different and meaningful to this issue. One replicates this issue and the other works without problem

"FrmDoesNotWork" demos the issue. Type "ZZZ" and you will error on the third Z saying that the control (that you are typing in) needs to have the focus. Should not be possible to lose the focus in a control you are typing, if that error is even correct. Also notice the code sets the focus to the control prior to this error. This behavior is normally what I see I when building a form in this manner. To avoid this I use additional code in a class to handle this case. I check if records are going to be returned and then step back the filter if not.

"FrmWorks" does not error. The funny thing I built this to demo the issue and for some reason it did not throw the error. So I grabbed the "FrmDoesNotWork" to demo the issue. Point is there is some property or design that keeps the issue from happening, but I cannot tell you what that is. If someone can figure out what is different in the two forms that makes this one not susceptible to the issue, it would be appreciated.

As @arnelgp suggests the code used the change event, but I believe the issue is identical regardless of the event. If I remember when I first saw this I tried different events without success.

I looked at it and noticed one difference in behavior (i.e. other than the error message) that differs between the "Does" and "Does Not" cases. When the "Does" case takes the 3rd Z, it eliminates all records but still paints the form AND offers a blank record. In the "Does Not" case, the continuous form doesn't even show the empty record. So I poked around. I found that the "Allowxxxx" options for the two forms are different. The "Does Not" case doesn't allow additions or deletions but the "Does" case allows all actions. Therefore, I would look there first as the source of the difference.

I guess if you can't enter a new record and your 3rd Z in the filter has wiped out all extant records, you lose the continuous controls. Access suddenly falls into the case where the FORM takes focus because that section has no active controls.

 

[Babycat]

The code in this thread is not the solution. The purpose is to show the problem. I believe the other thread has a solution. However I demoed with a class module.

I meant your code in other thread, they can solve the issue

https://www.access-programmers.co.u...ead-only-recordset.320126/page-2#post-1792689

 

[Babycat]

I looked at it and noticed one difference in behavior (i.e. other than the error message) that differs between the "Does" and "Does Not" cases. When the "Does" case takes the 3rd Z, it eliminates all records but still paints the form AND offers a blank record. In the "Does Not" case, the continuous form doesn't even show the empty record. So I poked around. I found that the "Allowxxxx" options for the two forms are different. The "Does Not" case doesn't allow additions or deletions but the "Does" case allows all actions. Therefore, I would look there first as the source of the difference.

I guess if you can't enter a new record and your 3rd Z in the filter has wiped out all extant records, you lose the continuous controls. Access suddenly falls into the case where the FORM takes focus because that section has no active controls.

Good addressing, I keep watching and curious to know the root cause too.
Some time, my subform can paint the form without record, sometime it is totally blank, hope to know about them more clearly

 

[arnelgp]

because your Subform's AllowAddition property is set to No.
You should set it to Yes then Add Code to the BeforeInsert event
of the subform to Cancel adding new record:

private sub form_beforeInsert(cancel as integer)
cancel = -1
end sub

 

[Babycat]

See this article:

TextBox.Text property (Access)

Office VBA reference topic

docs.microsoft.com

The paragraph in Remarks after the purple-highlighted area reveals that <control>.Text isn't always available. Again, it relates to something that only exists when the control has focus. So if you try to get to the property and focus has somehow changed, <control>.Text won't be there.

Look at it this way: When you put focus on the textbox, the .Text property springs up and it has the associated properties .SelStart, .SelLength, and .SelText - all of which have to do with what is in .Text while its control has focus.

Hi Sr,

Take a look at attached photo in #1, in the immediately window, the textbox is already has focus, but .Text is still not accessible. That's strange

 

[MajP]

@Babycat,
I did forget how to replicate this issue. This isssue is 100% repeatable. It occurs when the form does not allow edits. I will go back and edit my demo to explain that.

he most strange behaviour is that the textbox is already got focus, but vba code insists asking for the focus to access .Text or .SelStart
kind of access bug, i think

This is a result of the form not having anything to display, and for some reason causes this issue. That is why it does not happen when allow additions is true. This is a modification of the what is done in the class module, which you can demo.

Here is an example solution. If the user types ZZ and tries one more Z, a message comes up and says no records returns. It rolls back the input one letter to ZZ.

Private Sub txtFilter_Change()
Dim sText As String
Dim rs As DAO.Recordset
Dim StrFilter As String
    On Error GoTo ErrHandler
   sText = Me!txtFilter.Text
   If sText <> "" Then
        StrFilter = "[lastName] Like '*" & sText & "*'"
        Me.Filter = StrFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    '------------------------------------------------------------------------------------------------------------------------------------------------------------
    '---------------------------------------------------------------------------- Solution with rollback-------------------------------------------------------

   'Roll back one letter
   If Me.Recordset.RecordCount = 0 Then
      MsgBox "No items matched filter " & txtFilter.Value, vbInformation, "No Items Found"
      Me.FilterOn = False 'needed to set focus on textbox
      DoEvents
      txtFilter.SetFocus
      txtFilter.Value = Left(txtFilter.Text, Len(txtFilter.Text) - 1)
      'try again
      txtFilter_Change
    End If
    '------------------------------------------------------------------------------------------------------------------------------------------------------------
    '---------------------------------------------------------------------------- End solution -----------------------------------------------------------------

    With Me.txtFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

 

[MajP]

FYI,
Something not quite working in that demo
If I Type ZZ the message come up and says no records with ZZ. It should roll back to Z automatically. See the correct behavior using the class module. I will have to look at it. Still useable, but forces the user to manually fix the extra letter.

The class demos a lot of other FAYTs.

 

[Babycat]

consider also using the Change Event of the textbox.

Hi @arnelgp

About event change remarks in microsoft site: Examples of this event include entering a character directly in the text box or combo box or changing the control's Text property setting by using a macro or Visual Basic.

I think, i am getting risk if inside event change, i set value for .Text, for example Textbox.Text = "acb", so the event change will fire again therefor endless loop, may be...
So what is the benefit of using Change Event instead of KeyUp event in this case?

 

[Babycat]

Hi @MajP

I have applied your code to my application, it works and help to avoid the error 2185.
However, I would prefer a blank window on my subform if filter returns no record.
So in my case, i found a solution that is moving the searchbox to parent form and apply filter like:

Forms!MainFrm!SubFrm.Form.Filter = SQLfilter

I likely remove seachbox on the subform, using the one on main form instead of.