Solved Error Trap for textbox search (1 Viewer)

mib1019

Member
Local time
Today, 06:28
Joined
Jun 19, 2020
Messages
88
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/

Code:
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!
 
Last edited by a moderator:

Isaac

Lifelong Learner
Local time
Today, 05:28
Joined
Mar 14, 2017
Messages
8,738
What error does it give?

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:28
Joined
May 7, 2009
Messages
19,169
you should probably Test if it will return AtLeast a record:
Code:
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

Member
Local time
Today, 06:28
Joined
Jun 19, 2020
Messages
88

Attachments

  • Capture.PNG
    Capture.PNG
    15.3 KB · Views: 154

mib1019

Member
Local time
Today, 06:28
Joined
Jun 19, 2020
Messages
88
you should probably Test if it will return AtLeast a record:
Code:
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

Lifelong Learner
Local time
Today, 05:28
Joined
Mar 14, 2017
Messages
8,738
One thing you might try is moving the focus to another control, then back to the textbox.
 

Users who are viewing this thread

Top Bottom