键入时访问带有结果的多字段搜索 [英] Access Multi-field Search with Results as you type

问题描述

早上好，

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

My question today is regarding multi-field search.

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

I have a split form (fields and individual record at the top, all data in datasheet view on the bottom). I have multiple fields I would like to search on, so that a user may find a particular person based on a number of criteria.

这是同事现在帮助我完全正常工作的VBA，我想通过允许它搜索更多内容而不只是添加 bit 功能.一个字段.

Here is the VBA that a colleague helped me with that works PERFECTLY right now, and I'd like to add just a bit more functionality to it by allowing it to search on more than just the one field.

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字段等.任何人都可以提供我需要使其工作的一点点代码吗?我一直在搜索多个论坛，视频，帖子等，但是随着我不断抛出错误，似乎没有什么区别.

Every time I type a letter, the search requeries and supplies only the information that meets that search criteria. What I would like it to do is filter even if it was a First_Name field or the SSN field, etc. Can anyone supply what bit of code I'd need to make it work? I've been searching through multiple forums, videos, posts, etc. and nothing seems to make a bit of difference as I keep throwing errors.

要使搜索能够跨越表单中的多个字段(假设有一个字段)，我需要执行OR语句?

What is the OR statement I need to enable my search to span multiple fields within my form (assuming there is one)?

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

*Note that I would like to do this without a search button, so I want to keep this as a Change() event, not an AfterUpdate().

谢谢！

EDIT

EDIT

代码太长，无法进行标准回复评论:

Code too long for standard reply comment:

此代码挂起.我可能将VBA设置错误.

This code hangs. I'm probably setting up VBA wrong.

'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

这是否会引起我崩溃的危险信号?

Does this throw any red flags as to why I'm getting a crash?

推荐答案

您只需要更改strFilter的定义.为了方便起见，我将使用其他变量.

You only need to change the definition of strFilter. For convenience I would use an additional variable.

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.

这篇关于键入时访问带有结果的多字段搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！