vba Access 2013 表作为记录集并使用数据

vba Access 2013 tables as recordsets and using the data

提问by jordankoal

I have a form that is for data entry. I have a list box that has a list of all the products. I also have a second list box that has all the companies. I have a table for Customers, which has their name and an auto ID. I have a product list which also contains a name and an auto ID. I have a third table that lists what customers have which products.

我有一个用于数据输入的表格。我有一个列表框，其中包含所有产品的列表。我还有一个包含所有公司的第二个列表框。我有一张客户表，里面有他们的名字和一个自动 ID。我有一个产品列表，其中还包含一个名称和一个自动 ID。我有第三张表，列出了哪些客户拥有哪些产品。

Example:

例子：

tblCustomer
1    Company1
2    Company2
3    Company3

tblProducts
1    Product1
2    Product2
3    Product3

tblCustomerProducts

1    1    2 years
1    2    3 years
2    3    2 years

So it means 1 is the company, 1 is the product and they have it for 2 years

所以这意味着1是公司，1是产品，他们拥有2年

Now, when I do the entry form I am trying to open a recordset for both tables and loop through it and then when it finds a match it will put the corresponding number in the corresponding text box. This is what I have

现在，当我执行输入表单时，我试图为两个表打开一个记录集并循环遍历它，然后当它找到匹配项时，它会将相应的数字放入相应的文本框中。这就是我所拥有的

Private Sub Command15_Click()
Dim db As Database
Dim rst As Recordset   'Gets a variable ready to put a table into
Dim rst2 As Recordset  'Gets second variable ready to put a table into

Set db = CurrentDb()
Set rst = db.OpenRecordset("customer") 'Sets variable rst to the contents of the customer table
Set rst2 = db.OpenRecordset("product") 'Sets variable rst2 to the contents of the product table

Do While Not rst.EOF                                 'Loop through the customer table until it gets to the end of the file
    If rst!["customer_name"] = lstCustomerName Then  'If the contents of the field customer_name is equal to that of the company highlighted on the form
    txtCustomerFX.Value = rst!["id"]                 'Then make the value of the the CustomerFX box equal to the ID associated with that company

    rst.MoveNext
Loop

rst.Close

Do While Not rst2.EOF                               'Loop through the product table until it gets to the end of the file
    If rst2!["product_name"] = lstProductName Then  'If the contents of the field product_name is equal to that of the product highlighted on the form
    txtProductFX.Value = rst2!["id"]                'Then make the value of the the ProductFX box equal to the ID associated with that product

    rst.MoveNext
Loop

rst2.Close
End Sub 

It doesn't seem to be putting the data into the text boxes though.

不过，它似乎并没有将数据放入文本框中。

回答by pteranodon

You don't need to dive into the recordsets to match display names and ids like this. A combobox or listbox can be bound to a hidden column of ids and just display the names. User sees names, database sees numbers. With control wizards turned on (the default) try creating a new combobox on your junction form. Choose:

您不需要像这样深入研究记录集来匹配显示名称和 ID。组合框或列表框可以绑定到隐藏的 id 列并只显示名称。用户看到名字，数据库看到数字。启用控制向导（默认设置）后，尝试在您的连接窗体上创建一个新的组合框。选择：

• Choose "I want the combo box to get the values from another table or query"
• Choose tblCustomer
• Add idand customer_name
• Sort by customer_name
• Make sure the box to Hide key column is checked
• Choose a label

• 选择“我希望组合框从另一个表或查询中获取值”
• 选择 tblCustomer
• 添加id和customer_name
• 排序方式 customer_name
• 确保选中隐藏键列的框
• 选择一个标签

This sets up the properties of the combobox to store the ID in its value, but just display the name to the user. You can bind this field directly to the customer field on the the junction table and then eliminate the code (and the button) all together. Repeat for product, too!

这将设置组合框的属性以将 ID 存储在其值中，但仅向用户显示名称。您可以将此字段直接绑定到联结表上的客户字段，然后一起消除代码（和按钮）。对产品也重复一遍！