在Access db组合框中显示用户键入的任何文本上的记录 [英] Display records in access db combobox on any text typed by user

问题描述

我正在创建一个Access .accdb数据库，以从我所在部门的人员那里获取有关公司卡购买的信息。

I am creating an Access .accdb database to get information about company card purchases from personnel in my department.

我有一个可以填写的主要表格。他们的条目将填充一个表CCPurchForm。该表与包含其他表的查询结合在一起用于生成一个报告，该报告提供购买所需的所有信息。

I have a main form that they will fill out. Their entries will populate a table, CCPurchForm. That table combined with a query that includes other tables is used to generate a report that gives all of the information needed for a purchase.

我有一个组合框，用于选择每次购买的供应商。组合框的行源是单独的Vendors表的自动编号ID字段和Vendor Name字段。我将ID列设置为0，因此它不显示。组合框仅限于Vendors表中的记录。如果用户需要添加供应商，则他们必须使用另一种形式，因为我必须具有某些供应商信息。

I have a combobox that is used to select the vendor for each purchase. The Row Source for the combobox is the autonumber ID field and Vendor Name fields of a separate Vendors table. I have the ID column set to 0" so it does not show. The combobox is limited to the records in the Vendors table. If a user needs to add a vendor, they have to use another form because I have to have certain vendor information.

由于供应商的数量，供应商字段需要在用户键入文本时过滤结果。我想让组合框根据用户在组合框中键入的任何字符串来显示可能的匹配项，例如，如果他们键入 Bioscience，则组合框将显示 Biosciences，Ltd。， BD Biosciences，然后，用户单击正确的匹配项，供应商名称将显示在组合框中的表单上，并且ID号存储在CCPurchForm表的VendorID字段中。
选定的供应商还会在主窗体的子窗体中填充其他供应商信息。

Because of the number of vendors, the vendor field needs to filter results as the user types in text. I would like to have the combobox bring up possible matches based on any string of characters the user types in the combobox, for example if they type in "Bioscience" the combobox will show "Biosciences, Ltd.," "BD Biosciences," "New England Biosciences," etc. The user then clicks on the correct match and the Vendor Name shows on the form in the combobox and the ID number is stored in the VendorID field of the CCPurchForm table. The selected Vendor also populates additional vendor information in a subform on the main form.

我还有另一个组合框，该组合框基于购买类型表中的三个字段。每个购买类型，我的机构使用的购买类型代码和购买类型描述文本都有一个自动编号ID字段。 （我创建了ID字段来区分购买的子类型，因为我的机构将其归为一般类别（例如，机票购买和住宿购买之间的差异）。

I have another combobox that is based on three fields from a Purchase Type table. There is an autonumber ID field for each purchase type, the purchase type code used by my institution, and the purchase type description text. (I created the ID field to differentiate between sub-types of purchases because my institution lumps them into general categories (e.g. the difference between a purchase of airfare and a purchase of lodging).

我希望用户能够输入机构购买类型代码或文本来描述他们的购买（同样，可以是任何字符串），并降低组合框过滤器的结果，以便他们做出最终选择。（示例如果用户键入机票，则组合框将显示包含机票文本的每条记录，而不管其出现在文本中的位置如何，或者如果用户键入机构购买代码编号，则组合框都会显示所有包含该代码的记录。）然后，用于其选择的自动编号ID应存储在CCPurch表格表的PurchCodeID字段中。

I would like users to be able to type in either the institution purchase type code or text to describe their purchase (again, any string of characters) and have the combobox filter results down for them to make a final selection. (Example if the user types "airfare" the combobox brings up every record containing the text "airfare" no matter where it appears in the text or if they type in the institutions purchase code number it brings up all of the records that have that code.) The autonumber ID for their selection should then store in the PurchCodeID field of the CCPurch Form table.

是否有可能使表格中的组合框如我所描述的那样起作用以上吗？我已经搜寻了，找不到一个足够接近我的情况o寻找解决方案。我是VBA的新手，但如果我知道将其存储在哪里，就可以使用代码运行。

Is it possible to get the comboboxes in my form to function as I have described above? I’ve searched and searched and can’t find a situation close enough to mine to find a solution. I am a newbie at VBA, but can function ok with code if I know where to store it.

推荐答案

有一些

1. 创建一个名为 cboVendors


2. 在组合的属性中：设置 Column Count = 2 （ID，供应商）和 Column宽度= 0cm，5cm （相应地更改宽度）


3. 在 cboVendors 属性的事件选项卡中转到启动 事件，然后单击三个点 ... -将显示一个对话框，以选择生成器-选择代码生成器



4. 复制以下代码：

1. Create a combo called cboVendors
2. In the properties of the combo: Set Column Count = 2 (ID, Vendors) and Column Widths = 0cm, 5cm (change widths accordingly)
3. In the EVENT tab of the properties for cboVendors go to On Key Up event and click the three dots ... - a dialog box will show to choose builder - select Code Builder

4. Copy the code below:

'test number of characters entered - if greater then 2 then assign rowsource
If Len(Me.cboVendors.Text) > 2 Then

    'set the rowsource to match user search criteria
     Me.cboVendors.RowSource = "SELECT * FROM vendors WHERE vendors LIKE '*" & Me.cboVendors.Text & "*'"

    'show the search in real-time
     Me.cboVendors.Dropdown
Else
    'set to no
     Me.cboVendors.RowSource = ""
End If

您的结果应如下所示：

您也可以对其他组合搜索执行相同的操作。

You can do the same for your other combo search too.

这篇关于在Access db组合框中显示用户键入的任何文本上的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！