vba Access 2007，文本框搜索框，如右上角的 Facebook 名称搜索框

Access 2007, Textbox search box like the Facebook name search box on the top right

提问by

so basically I have an AddCompany Form, where theres a textbox [CompanyName], i want to type a new company name in there, but meanwhile check if theres an already existing one.

所以基本上我有一个 AddCompany 表单，其中有一个文本框 [CompanyName]，我想在那里输入一个新的公司名称，但同时检查是否已经存在一个。

So for example,say i want to type Microsoft, when i type M, the textbox shows whole bunch of other names with M, and then keeps going until I type finish typing microsoft. Basically jsut how the facebook search box works.

例如，假设我想键入 Microsoft，当我键入 M 时，文本框会显示一大堆其他带有 M 的名称，然后一直继续，直到我键入完成键入 microsoft。基本上就是 facebook 搜索框的工作原理。

How do i implement this on Microsoft Access 2007?? could it be in the on dirty event/on change/On key down event???

我如何在 Microsoft Access 2007 上实现这个？可能是在脏事件/更改/按键按下事件中吗？？？

please enlighten me!!

请赐教！！

Thank you very much!!!

非常感谢！！！

回答by David-W-Fenton

A much simpler version of the same thing can be done with the Access wizards. If you turn on the form wizards in form design and click the Combo Box button and point to a location in the header of your form, you'll automatically get a choice to create a combo box that will look up a record that matches something listed in the dropdown list.

使用 Access 向导可以完成相同操作的更简单版本。如果您在表单设计中打开表单向导并单击“组合框”按钮并指向表单标题中的某个位置，您将自动选择创建一个组合框，该组合框将查找与所列内容匹配的记录在下拉列表中。

However, keep these things in mind:

但是，请记住以下几点：

1. it works only when you've bound your form to an entire table (which is not recommended for large recordsets)

2. the code it creates is horrendously BAD.

1. 它仅在您将表单绑定到整个表时才有效（不推荐用于大型记录集）

2. 它创建的代码非常糟糕。

There are a number of ways to solve this problem. If you're happy binding your form to the entire table, bookmark navigation (as with the code created by the wizard) will be fine. However, I recommend you use this code instead of the wizard code (assumes the combo box has a bound column with the PK ID of the record you're trying to find):

有多种方法可以解决这个问题。如果您愿意将表单绑定到整个表格，书签导航（与向导创建的代码一样）就可以了。但是，我建议您使用此代码而不是向导代码（假设组合框有一个绑定列，其中包含您要查找的记录的 PK ID）：

  Private Sub MyComboBox_AfterUpdate()
    If IsNull(Me!MyComboBox) Then Exit Sub
    With Me.RecordsetClone
      .FindFirst "[MyID]=" & Me!MyCombBox
      If Not .NoMatch Then
         If Me.Dirty Then Me.Dirty = False
         Me.Bookmark = .Bookmark
      End If
    End With
  End Sub

The combo box would need to use a SQL rowsource, and it would be something like:

组合框需要使用 SQL 行源，它类似于：

  SELECT CompanyID, CompanyName FROM Company ORDER BY CompanyName

And you'd define the combo box to have 2 columns, the first one the bound column, and you'd set the width for the first column to 0. The wizard walk you through doing this and set it up for you. The only thing it does wrong is write really bad code to do so.

并且您将组合框定义为具有 2 列，第一列是绑定列，并将第一列的宽度设置为 0。向导将引导您完成此操作并为您进行设置。它做错的唯一一件事就是为此编写了非常糟糕的代码。

Now, if you're not searching for a unique value, it gets more complicated, and you might want to use a different approach. Suppose you have a form that displays people and you want to see the ones for a particular company. In that case, you might want to filter by CompanyName. In that case, instead of doing a Find operation as outlined above, you might want to apply a filter. In that case the AfterUpdate event of your combo box would be something like this:

现在，如果您不搜索唯一值，它会变得更加复杂，您可能需要使用不同的方法。假设您有一个显示人员的表单，并且您想查看特定公司的人员。在这种情况下，您可能希望按 CompanyName 进行过滤。在这种情况下，您可能想要应用过滤器，而不是执行上述的 Find 操作。在这种情况下，组合框的 AfterUpdate 事件将是这样的：

  Private Sub MyComboBox_AfterUpdate()
    If IsNull(Me!MyComboBox) Then Exit Sub
    Me.Filter = "[CompanyName]=" & Chr(34) & Me!MyComboBox & Chr(34)
    Me.FilterOn = True
  End Sub

Now, in that case, your combo box would have but one column, with the company name and no hidden ID field, so it's somewhat different.

现在，在这种情况下，您的组合框将只有一列，包含公司名称且没有隐藏 ID 字段，因此有些不同。

I don't like programming this kind of filtering as it's provided by the Access UI (you can right click on the CompanyName field and in the shortcut menu that pops up, type in the company name you want to filter for, including wildcards, ? and *). Also, it can get confusing when you try to filter when there's already a filter in place. If you're viewing just CitiCorp and then you filter by JP Morgan Chase, you won't get anything.

我不喜欢编程这种过滤，因为它是由 Access UI 提供的（您可以右键单击 CompanyName 字段，然后在弹出的快捷菜单中，输入要过滤的公司名称，包括通配符，?和 *）。此外，当您在已经有过滤器的情况下尝试过滤时，它会变得混乱。如果您只查看 CitiCorp，然后按 JP Morgan Chase 进行筛选，您将一无所获。

Instead, I'll tend to change the Recordsource of the form instead of applying a filter. This means each search will give you a new resultset and is the most efficient approach. It does make things a little bit more complicated in terms of returning to your original starting point, but in a properly-designed app, you should probably be presenting only subsets of data to the users in the first place, so the problem this approach "causes" is one that you'd need to resolve to build a proper and efficient user interface that retrieves only the data that the user really needs.

相反，我倾向于更改表单的 Recordsource 而不是应用过滤器。这意味着每次搜索都会为您提供一个新的结果集，并且是最有效的方法。在返回到您的原始起点方面，它确实使事情变得更加复杂，但是在一个设计合理的应用程序中，您可能应该首先只向用户呈现数据的子集，所以这种方法的问题是“原因”是您需要解决的一个问题，以构建一个适当且高效的用户界面，该界面仅检索用户真正需要的数据。

回答by eric.christensen

The forms in Access have event handler functions. So couldn't you use autocompletion code added to these functions for this functionality? I found a book on Google Bookswhich discusses this.

Access 中的表单具有事件处理函数。因此，您不能使用添加到这些函数中的自动完成代码来实现此功能吗？我在Google Books上找到了一本书讨论了这个问题。

I can think of the following code which would need to be added to the event handler function:
1. SQL to find existing potential matches
2. Code generated drop-down box to display potential matches

我可以想到需要添加到事件处理函数中的以下代码：
1. 查找现有潜在匹配项的 SQL
2. 代码生成的下拉框以显示潜在匹配项

回答by Matthew Jones

In general, this feature is called auto-completion (I've also heard find-as-you-type). There is an article giving an example of how to do this in Access on this page.

通常，此功能称为自动完成（我也听说过 find-as-you-type）。有一篇文章给出了如何在此页面上的Access 中执行此操作的示例。