All, I was provided some help, but now I'm having an issue whereby if there's a value not in my form, it's crashing the DB. For example, if I search my last name (Dutcher), I come up. If I search my first name (William), I'm in the list of people. If I put in my SSN, I show up. But if I put in, for example, "Dutcher " (with a space at the end), the form shuts down. Any ideas how to fix this? Can anyone spot what the issue is?
Code:
'This code works great, but if I put in a space character, it crashes the DB
Private Sub txtSearch_Change()
Dim strFilter As String
Dim sSearch As String
If Me.txtSearch.Text <> "" Then
sSearch = "'*" & Replace(Me.txtSearch.Text"'", "''") & "*'"
strFilter2 = "[Last_Name] Like " & sSearch & " OR [First_Name] Like " & sSearch & " OR [SSN] Like " & sSearch
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
With Me.txtSearch
.SetFocus
.SelStart = Len(Me.txtSearch.Text)
End With
End Sub
Code:
'This code is what I have that will reset the textbox to blank and requery, giving me all the people in the DB
Private Sub txtSearch_Click()
Me.txtSearch.Text = ""
Me.Requery
With Me.txtSearch
.SetFocus
.SelStart = Len(Me.txtSearch.Text)
End With
End Sub
*****I was suggested the following additions to above, but crashes even harder*****
Code:
'This code works great, but if I put in a space character, it crashes the DB
Private Sub txtSearch_Change()
Me.txtSearch.Text = Trim(Me.txtSearch.Text)
Dim strFilter As String
Dim sSearch As String
If Me.txtSearch.Text <> "" Then
sSearch = "'*" & Replace(Me.txtSearch.Text"'", "''") & "*'"
strFilter2 = "[Last_Name] Like " & sSearch & " OR [First_Name] Like " & sSearch & " OR [SSN] Like " & sSearch
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
With Me.txtSearch
.SetFocus
.SelLength = 0
.SelStart = Len(Me.txtSearch.Text)
End With
End Sub
Code:
'This code is what I have that will reset the textbox to blank and requery, giving me all the people in the DB
Private Sub txtSearch_Click()
Me.txtSearch.Text = ""
Me.Requery
With Me.txtSearch
.SetFocus
.SelStart = Len(Me.txtSearch.Text)
End With
End Sub
Any help is very appreciated!!!