Solved Help to modify code to avoid “Error code 2185” when dynamically filtering records using an unbound text box (1 Viewer)

[SurreyNick]

I am getting a 2185 error code when dynamically filtering records in a form from an unbound text box.

“Error… 2185 – You can’t reference a property or method for a control unless the control has focus”

The filter routine works fine until no records match the string in the text box and that’s when I get the error message which also locks up the form so I can’t continue and am forced to reload it.

I am only just getting to grips with VBA so I’m still at the stage of using code others have shared and don’t understand it well enough to debug/refine it effectively. I think what is happening is, that the lines of code which are meant to retain the search string entered into the text box and place the cursor at its end, is also populating the text box with the error message string and then holding onto that string locking the form up. The thing is these lines of code are needed in order to hold onto the search string after each application of the filter and cycle of the sub routine.

With my limited knowledge the only work around I have been able to come up with is to execute a reload of the form using the DoCmd method in the error handler section of the code and that’s not an ideal solution. Ideally I’d like to modify the code to avoid the Error 2185 message when zero records match the string and instead initiate a vbOKonly message box that alerts the user that there are no matching records and on clicking OK clears the text box ready to start again – obviously without locking the form up.

Can anyone help me with this?
The code routine is:

Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler
Dim filterText As String

If Len(txtNameFilter.Text) > 0 Then
filterText = txtNameFilter.Text
Me.Form.Filter = "[RecoverLostStudentQ]![Forename] Like '*" & filterText & "*' OR [RecoverLostStudentQ]![Surname] LIKE '*" & filterText & "*'"
Me.FilterOn = TRUE
txtNameFilter.Text = filterText
txtNameFilter.SelStart = Len(txtNameFilter.Text)
Else
Me.Filter = ""
Me.FilterOn = FALSE
txtNameFilter.SetFocus
End If
Exit Sub

errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
DoCmd.Close
DoCmd.OpenForm "RecoverLostStudent"
End Sub

 

[MajP]

The filter routine works fine until no records match the string in the text box and that’s when I get the error message which also locks up the form so I can’t continue and am forced to reload it.

The reason for that is that when no records are in the subform the text box loses focus. I do not know why, but that is how it works. You need to reset the focus
To be safe just before everywhere you call txtNameFilter.text
add the line
txtNameFilter.setfocus

 

[MajP]

Untested, but try this

Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler
Dim filterText As String
me.txtNameFilter.setfocus
If Len(txtNameFilter.Text) > 0 Then
  filterText = txtNameFilter.Text
  Me.Form.Filter = "[RecoverLostStudentQ]![Forename] Like '*" & filterText & "*' OR [RecoverLostStudentQ]![Surname] LIKE '*" & filterText & "*'"
  Me.FilterOn = TRUE
  txtNameFilter.value = filterText
  me.txtnamefilter.setfocus
  txtNameFilter.SelStart = Len(txtNameFilter.Text)
  if me.recordset.recordcount = 0 then
    msgbox "No records meet search"
   me.txtnameFilter = null
  end if

Else
  Me.Filter = ""
  Me.FilterOn = FALSE
  txtNameFilter.SetFocus
  End If
Exit Sub

errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
DoCmd.Close
DoCmd.OpenForm "RecoverLostStudent"
End Sub

 

[SurreyNick]

Alas MajP it has not solved the problem.
Pared down db attached with the original routine and your adapted routine included in the class module. I have disabled the DoCmd method that was reloading the form and neither routine stops the form locking up.
Any other suggestions?
Nick.

 

[MajP]

might be overkill but give this a try

Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler
Dim filterText As String
Me.txtNameFilter.SetFocus
If Len(txtNameFilter.Text) > 0 Then
filterText = txtNameFilter.Text
Me.Form.Filter = "[RecoverLostStudentQ]![Forename] Like '*" & filterText & "*' OR [RecoverLostStudentQ]![Surname] LIKE '*" & filterText & "*'"
Me.FilterOn = True
txtNameFilter.Value = filterText
If Me.Recordset.RecordCount = 0 Then
MsgBox "No records meet search"
Me.Filter = ""
Me.FilterOn = False
Else
Me.txtNameFilter.SetFocus
txtNameFilter.SelStart = Len(txtNameFilter.Text)
End If
Else
Me.Filter = ""
Me.FilterOn = False
txtNameFilter.SetFocus
End If
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub

 

[MajP]

This may be better. It moves you back one character instead of clearing the textbox

  On Error GoTo errHandler
  Dim filterText As String
  Me.txtNameFilter.SetFocus
  If Len(txtNameFilter.Text) > 0 Then
    filterText = txtNameFilter.Text
     Me.Form.Filter = "[RecoverLostStudentQ]![Forename] Like '*" & filterText & "*' OR [RecoverLostStudentQ]![Surname] LIKE '*" & filterText & "*'"
     Me.FilterOn = True
     txtNameFilter.Value = filterText
  If Me.Recordset.RecordCount = 0 Then
    MsgBox "No records meet search"
    If Len(Me.Filter) > 0 Then
      filterText = Left(filterText, Len(filterText) - 1)
      Me.txtNameFilter = filterText
      Me.Form.Filter = "[RecoverLostStudentQ]![Forename] Like '*" & filterText & "*' OR [RecoverLostStudentQ]![Surname] LIKE '*" & filterText & "*'"
      txtNameFilter.SelStart = Len(txtNameFilter.Text)
    End If
   Else
     Me.txtNameFilter.SetFocus
     txtNameFilter.SelStart = Len(txtNameFilter.Text)
   End If
Else
  Me.Filter = ""
  Me.FilterOn = False
  txtNameFilter.SetFocus
End If
Exit Sub

errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub

 

[SurreyNick]

Oh. Yes. That's super! It works a treat
I wish I could have done that myself. Thanks MajP, you're a gent.
Nick