Solved Error Trap for textbox search (1 Viewer)

[mib1019]

Hi all,
Probably and easy fix, but I'm not sure what that its. Thanks in advance for advice.

I have a TextBox in header of two continuous forms that I use to filter the form as I type. As long as I don't make any mistakes it works just great. But if I add a letter to the name that results in no records, Access gives me an error.

Here is the code:

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Private Sub txtFilter_Change()
  ' If the text box is cleared, clear the form filter.
  If Nz(Me.txtFilter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  ' If a partial value is typed, filter for a partial company name match.
  Else
     Me.Form.Filter = "[CompanyName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*' OR [ContactName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*'"
     Me.FilterOn = True
  End If

  ' Move the cursor to the end of the combo box.
  Me.txtFilter.SetFocus
  Me.txtFilter.SelStart = Len(Me.txtFilter.Text)
End Sub

The error happens on the last line of the sub.

Thanks!

 

[Isaac]

What error does it give?

It errs on this line? Me.txtFilter.SelStart = Len(Me.txtFilter.Text)
?

 

[arnelgp]

you should probably Test if it will return AtLeast a record:

Private Sub txtFilter_Change()
  ' If the text box is cleared, clear the form filter.
  If Nz(Me.txtFilter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  ' If a partial value is typed, filter for a partial company name match.
  Else
      If DCount("1", "yourTable", "[CompanyName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*' OR [ContactName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*'") > 0 Then
         Me.Form.Filter = "[CompanyName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*' OR [ContactName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*'"
         Me.FilterOn = True
    Else
        Msgbox "No record found"
    End If
  End If

  ' Move the cursor to the end of the combo box.
  Me.txtFilter.SetFocus
  Me.txtFilter.SelStart = Len(Me.txtFilter.Text)
End Sub

 

[mib1019]

What error does it give?

It errs on this line? Me.txtFilter.SelStart = Len(Me.txtFilter.Text)
?

Yes, I error on the line before End Sub.

 

[mib1019]

you should probably Test if it will return AtLeast a record:

Private Sub txtFilter_Change()
  ' If the text box is cleared, clear the form filter.
  If Nz(Me.txtFilter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  ' If a partial value is typed, filter for a partial company name match.
  Else
      If DCount("1", "yourTable", "[CompanyName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*' OR [ContactName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*'") > 0 Then
         Me.Form.Filter = "[CompanyName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*' OR [ContactName] Like '*" & Replace(Me.txtFilter.Text, "'", "''") & "*'"
         Me.FilterOn = True
    Else
        Msgbox "No record found"
    End If
  End If

  ' Move the cursor to the end of the combo box.
  Me.txtFilter.SetFocus
  Me.txtFilter.SelStart = Len(Me.txtFilter.Text)
End Sub

Thank you for this. I will give it a try today.
MIB1019

 

[Isaac]

One thing you might try is moving the focus to another control, then back to the textbox.

 

[mib1019]

you should probably Test if it will return AtLeast a record:

arnelgp, your suggestion of looking for at least one match works perfectly. Thanks everyone, as always!
MIB1019