键入时访问带结果的多字段搜索

Question

大家早上好，

我今天的问题是关于多字段搜索。

我有一个拆分表单(顶部是字段和单个记录，底部是数据表视图中的所有数据)。我有多个字段要搜索，这样用户就可以根据多个条件找到特定的人。

这是一位同事帮我做的VBA，它现在运行得很好，我想通过允许它在多个字段上搜索，为它添加更多的功能。

Private Sub txtSearch_Change()
Dim strFilter As String
On Error Resume Next
If Me.txtSearch.Text <> "" Then
strFilter = "[Last_Name] Like '*" & Me.txtSearch.Text & "*'"
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

每次我键入一封信，搜索都会重新查询并只提供符合该搜索条件的信息。我希望它做的是过滤，即使它是First_Name字段或SSN字段等。有人能提供我需要的代码吗？我一直在搜索多个论坛、视频、帖子等，但似乎没有什么不同，因为我不断地抛出错误。

我需要什么OR语句才能使搜索跨越表单中的多个字段(假设有一个字段)？

*请注意，我希望在没有搜索按钮的情况下执行此操作，因此我希望将其保留为Change()事件，而不是AfterUpdate()事件。

谢谢！

编辑

代码对于标准回复注释太长：

此代码挂起。我可能设置错了VBA。

'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

'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

这会给我造成车祸的原因带来危险吗？

Answer 1

首先，文本框点击事件，当您点击框时，它会清除文本并重置搜索(无需重置按钮)

Private Sub txtSearch_Click()
Me.txtSearch.SetFocus 'new line of code
Me.txtSearch.Text = ""
Me.Requery
With Me.txtSearch
.SetFocus
.SelStart
End With
End Sub

这是实际的搜索，将搜索多个字段

Private Sub txtSearch_Change()
Dim strFilter As String
Dim sSearch As String
On Error Resume Next
If Me.txtSearch.Text <> "" Then
sSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"
strFilter = "[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
If Me.Recordset.RecordCount = 0 Then 'new line of code
Me.Filter = "" 'new line of code
Me.FilterOn = False 'new line of code
Me.txtSearch.SetFocus 'new line of code
Me.txtSearch.Text = "" 'new line of code
Exit Sub 'new line of code
End If 'new line of code
With Me.txtSearch
.SetFocus
.SelStart = Len(Me.txtSearch.Text)
End With
End Sub

Answer 2

您只需要更改strFilter的定义。为了方便起见，我会使用一个额外的变量。

Dim sSearch As String
If Me.txtSearch.Text <> "" Then
' Avoid problems with search strings containing "'"
sSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"
' Add all fields you want to search with OR
strFilter = "[Last_Name] Like " & sSearch  & " OR [First_Name] Like " & sSearch ' etc.