Solved Why does this error 2185 happen?

[Gasman]

Hi all,
This is for my education...again.

This link works perfectly in Northwind when implemented as described.
https://www.microsoft.com/en-us/mic...5/03/using-a-combo-box-to-search-as-you-type/

However when no record can be found the error 2185 (as shown in pic) happens, saying control must have focus, but the previous command is SetFocus.?
As you can see, I even tried GotoControl as well?

Private Sub cboFilter_Change()
    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

 

[Ranman256]

you gave us the error, but not WHERE the err happened. Which line?
but i guess if no records, then you cant goto a control on record line. Outside on the form is ok. (header/footer)

 

[arnelgp]

only when no record can be found, then it errors?
then before doing the code use DCount() if it will return any record.

 

[Gasman]

Thanks both.
@Ranman256, you are correct, sorry, I thought that was implicit by mentioning the previous line for SetFocus, it errors on the last line in the sub, where it attempts to set SelStart

Attached is the DB. it is not mine, I am merely trying to help someone, and I cannot see why this happens, and would like to know for my own sanity? I would probably use @MajP 's FAYT classes if I needed to search like this, but would still like to know the reason for the error.

The control is an unbound combo?

Form is MainPage

@arnelgp why would Access even care if there are records or not, as the control is unbound.?

 

[MajP]

@Gasman,
I can see if I find the thread that describes this problem, but this same thing drove me crazy. If you have an unbound control that you use to filter the form and do this on the change event naturally that control has focus you would think. However, as soon as there is no records in the form that control for some reason loses focus in the middle of the event. I think it moves to a bound control. Try moving the setfocus up to the top of the event. However, that is basically the work around that is needed. I will see if I can find the thread to show this in action and how to fix.

 

[Gasman]

@MajP
I found after the first error that if I chose End and tried again, it errored on the first line of that module, so I added SetFocus there as well, but it made no difference, that is why I an so confused. . What you say though, is how it seems to behave and ignore the setfocus command.

 

[MajP]

Not ideal but at least does not error
If Not Me.Recordset.RecordCount = 0 Then
Me.cboFilter.SelStart = Len(Me.cboFilter.Text)
Else
MsgBox "No records found ", vbInformation
Me.FilterOn = False
Me.Filter = ""
Me.cboFilter = ""
End If
End Sub

You can take a look at the FAYT Form class and see how I avoid this. If no records are found I roll back one character and alert the user
DOT
moves back to DO
DO

I tried to apply the same technique here and still could not get it to work. But it is the same issue. That once no records are available you cannot set or keep focus on that control.

 

[Gasman]

Not ideal but at least does not error
If Not Me.Recordset.RecordCount = 0 Then
Me.cboFilter.SelStart = Len(Me.cboFilter.Text)
Else
MsgBox "No records found ", vbInformation
Me.FilterOn = False
Me.Filter = ""
Me.cboFilter = ""
End If
End Sub

You can take a look at the FAYT Form class and see how I avoid this. If no records are found I roll back one character and alert the user
DOT
moves back to DO
DO

I tried to apply the same technique here and still could not get it to work. But it is the same issue. That once no records are available you cannot set or keep focus on that control.

Thank you @MajP

Still puzzled as to why it does appear to work in the Nortwind DB as per that link.

The link has incorrect double and single quotes, so here is the code if anyone wants to try and see.?

Private Sub cboFilter_Change()
    If Nz(Me.cboFilter.Text) = "" Then
        Me.Form.Filter = ""
        Me.FilterOn = False
    ElseIf Me.cboFilter.ListIndex <> -1 Then
        Me.Form.Filter = "[Company] = '" & _
        Replace(Me.cboFilter.Text, "'", "''") & "'"
        Me.FilterOn = True
    Else
        Me.Form.Filter = "[Company] 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

I implemented that code and it is fine when no records

 

[The_Doc_Man]

My take on it is that if you have no selection, you have no .SelStart (probably, no .SelLength either). While you might be able to set the focus, the .Selxxxxx properties depend on something being there.

I think that error message is misleading anyway, since I have successfully changed combobox properties even when it wasn't in focus - things like forecolor, backcolor, and bordercolor changes, and also the cbo.Undo method. (My complaint is less about the error than about how it is phrased.)

But I digress. That error exists because some dynamic properties of controls only exist when in focus. Things like a textbox's .Text property can return a different result than .Value since the textbox could be bound to something numeric. But .Text is only available when the textbox is in focus. I suspect it is because you are using .SelStart, which again is a dynamic property.

Here, I don't think it is the .SetFocus that is eating your lunch, but rather that with no selection available, you have a NULL in the combobox and thus .SelLength and .SelStart barf. Because we know that Access doesn't like nulls.

Therefore, I concur with those who suggest that you first verify that something came back in the combo before you try to do something that looks at what came back. Because you want to scrupulously avoid those nulls that occur when nothing comes back.

I kind of went around the mulberry bush there, but @Gasman, you asked about why you get this behavior. I hope I helped with that part of the issue.

 

[Gasman]

@The_Doc_Man
The combo is just unbound and used to filter a form, so it always has data in it?, whatever the user types in last?
As you say the control needs the focus to access the text property, but does not do so, even when specifically told to.?

Yet if applied to the Northwind DB it works as advertised, even when you get no records, so something subtle is going on here, and that is what piqued my interest.

 

[MajP]

Yet if applied to the Northwind DB it works as advertised, even when you get no records, so something subtle is going on here, and that is what piqued my interest

I will try my class on it, but I bet it has the same issue. I think there is a subtle issue. It may be because there are no other controls in the header especially no bound controls. As I said I tried everything I could think of to set focus.

 

[MajP]

Actually I do not get this. I have a fayt combo that filters the combo, or a FAYT form that uses a textbox to filter the form. So this set-up does not even make sense to me. Either filter the combo and then select to go to that record, or use a textbox to filter the form. One or the other or both, but this seems weird to me.

 

[Gasman]

For some reason, that CustomerList form in Northwind is a split form, with no controls in the non datasheet portion, which I did not even think you could do?

 

[MajP]

Not that it solves the problem, but using the class. You can have one or both. But I think the problem is the combo for the filter control.
FYI, compact and repair from 7m to 600k.

 

[CJ_London]

two thoughts -
1. I don't think the cboFilter has lost focus, so setting it again may be causing an issue
2. put doEvents before setfocus, just in case your app is still filtering

 

[MajP]

1. I don't think the cboFilter has lost focus, so setting it again may be causing an issue

This will never be a problem. You can always set focus if something already has focus

2. put doEvents before setfocus, just in case your app is still filtering

I tried that too and have that in my class. But seems that the cbo is the issue. As you can see from the textbox I posted it does not cause the same problem. My code is basically the same as this just encapsulated.

This is likely on purpose. If a form has no records then access may remove the focus from the controls, because there are no visible controls to have focus. You can refocus a textbox, but not the cmbo I do not know why.

 

[Gasman]

Thank you [User]Majp[/user] I'll pass that on.
I'm going to mark this solved now, so people do not waste any more time on it.

Very puzzling all the same, as it works fine here as demonstrated on the Customer List form.?

 

[MajP]

Very puzzling all the same, as it works fine here as demonstrated on the Customer List form.?

I have seen this issue several times and it is super confusing because the ctrl that calls the event no longer has the focus. It would seem impossible. But as soon as you filter out the records in the procedure then it loses focus. So it loses focus inside the event procedure. I pulled my hair out on this. In some cases you can reset the focus within the event, but this condition I could not.

 

[Gasman]

I have seen this issue several times and it is super confusing because the ctrl that calls the event no longer has the focus. It would seem impossible. But as soon as you filter out the records in the procedure then it loses focus. So it loses focus inside the event procedure. I pulled my hair out on this. In some cases you can reset the focus within the event, but this condition I could not.

I don't have that much hair to lose, but I'd work out another way if it was my DB.

Thank you all for having a look at it though, so at least I know I have not missed anything simple.

 

[CJ_London]

I would say this way of filtering implies a full table of data which is not good practice for a large dataset (say 10k records) which a) takes time to load and b) if a user types 3 chars in quick success will apply the filter 3 times before it 'settles down'.

better to start with an empty recordset then apply a timer (say 0.5 seconds) so that data is not fetched until the user has stopped typing. Then rather than filtering, apply criteria. That way a limited dataset is brought through. Even one character would (simplistically) reduce the number of records loaded from 10k to <400.