使用VBA On Change在MS Access表中查找记录

Question

我正在尝试修改发布在web https://www.microsoft.com/en-us/microsoft-365/blog/2012/05/03/using-a-combo-box-to-search-as-you-type/上的代码

其思想是通过查找人员的LastName来查找MS Access表单中的记录。

我的VBA如下所示，但我得到了编译错误:语法错误消息

Private Sub cboLastNameFind_Change()
' If the combo box is cleared, clear the form filter.
If Nz(Me.cboLastNameFind.Text) = "" Then
 Me.Form.Filter = ""
 Me.FilterOn = False

' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
ElseIf Me.cboLastNameFind.ListIndex <> -1 Then
Me.Form.Filter = "[LastName] = '" &
Replace(Me.cboLastNameFind.Text, "'", """) & "‘"
Me.FilterOn = True

' If a partial value is typed, filter for a partial company name match.
Else
    Me.Form.Filter = "[LastName] Like '*" & _
                 Replace(Me.cboLastNameFind.Text, "'", """) & "*‘"
Me.FilterOn = True

End If

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

问题似乎出在Me.Form.Filter = "LastName = '“&(至少，这是错误消息中突出显示的内容。任何想法和修复都非常欢迎。

谢谢，西蒙

Answer 1

显然，您引用的页面包含由文本处理器引起的多个错误(右单引号或左单引号，其中应该使用撇号，以及双引号，其中应该有两个单引号)。

人们永远不应该在Word等文本处理器之间复制粘贴代码。显然微软犯了这个错误。

修正后的代码如下：

Private Sub cboLastNameFind_Change()
' If the combo box is cleared, clear the form filter.
If Nz(Me.cboLastNameFind.Text) = "" Then
 Me.Form.Filter = ""
 Me.FilterOn = False

' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
ElseIf Me.cboLastNameFind.ListIndex <> -1 Then
Me.Form.Filter = "[LastName] = '" &
Replace(Me.cboLastNameFind.Text, "'", "''") & "'"
Me.FilterOn = True

' If a partial value is typed, filter for a partial company name match.
Else
    Me.Form.Filter = "[LastName] Like '*" & _
                 Replace(Me.cboLastNameFind.Text, "'", "''") & "*'"
Me.FilterOn = True

End If

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

Answer 2

不知道您是否知道；通过查找人员的LastName在MS Access窗体中查找记录是组合框控件的标准功能。当您将其拖动到窗体中时，向导弹出窗口为您提供查找选项-其中之一是从其绑定的记录源中查找窗体的记录。