如何使用基于另一个字段选项的表中的信息更新字段 [英] How to update a field with information from a table based on another fields option

问题描述

我正在使用Access 2007构建数据库.我在Access方面没有很多经验，而且我不确定如何完成此任务.

I'm building a database using Access 2007. I don't have a lot of experience with Access, and I'm not sure how to accomplish this task.

我已经建立了一个输入表单，并且有一个名为"Product"的字段，该字段是一个下拉列表，用于从Product表中提取信息.产品表的设置如下:

I've built an entry form, and have a field called "Product" which is a drop down that pulls the information from the Product table. The Product table is set up like this:

Product Name        Commodity
--------------------------------------------
Product #1          Soybean
Product #2          Soybean Meal
Product #3          Corn

我还有一个名为"HTS商品"形式的字段，该字段是一个下拉列表，用于从HTSUS表中提取信息.此字段已锁定，一旦有人从产品"字段中选择产品"，我希望该值会自动更新.这是HTSUS表:

I have another field in the form called "HTS Commodity" which is a drop down that pulls the information from the HTSUS table. This field is locked, and I would like the value to be automatically updated once someone selects a Product from the Product field. Here's the HTSUS table:

Commodity           HTSUS Value
--------------------------------------------
Soybean             1.1.1
Soybean Meal        2.2.2
Corn                3.3.3

当某人在产品"字段中选择一个项目时，它会显示产品名称.一旦他们选择了项目，我希望基于表格中的商品"列自动更新"HTS商品"字段，但是显示"HTSUS值"列.

When a person selects an item in the "Products" field, it shows the Product Name. Once they select the item, I'd like the field "HTS Commodity" to auto update based on the Commodity column from the tables, but display the HTSUS Value column.

      Product: Product #2 (Soybean Meal)
HTS Commodity: 2.2.2 (Soybean Meal)

我希望这是有道理的.我了解如何使用不同的构建器来创建Macro，但是我不确定该如何完成.我一直在尝试使用AfterUpdate()宏.感谢您的任何事先帮助.

I hope this makes sense. I understand how to create Macro's using the different builders, but I'm just not sure how to accomplish this. I've been attempting to use an AfterUpdate() macro. Thanks for any help in advance.

推荐答案

这里是一种方法:

首先创建一个名为HTSUS_by_Product的已保存查询，其SQL代码为

Start by creating a saved query named HTSUS_by_Product whose SQL code is

SELECT Product.[Product Name], HTSUS.[HTSTS Value]
FROM Product INNER JOIN HTSUS ON Product.Commodity = HTSUS.Commodity;

它将产生这样的结果

Product Name    HTSUS Value
------------    -----------
Product #1      1.1.1
Product #2      2.2.2
Product #3      3.3.3

现在，在表单上，​​假设您有一个名为cbxProductName的组合框，该组合框从[Product]表的[Product Name]字段中获取其值.当您从列表中选择产品1"时，cbxProductName.Text将是产品1".

Now, on your Form let's assume that you have a combo box named cbxProductName that gets its values from the [Product Name] field in the [Product] table. When you choose "Product #1" from the list then cbxProductName.Text will be "Product #1".

现在在表单上创建一个文本框，并将其命名为txtHtsus.将其Locked属性设置为Yes，然后输入以下内容作为其Control Source属性:

Now create a text box on the form and name it txtHtsus. Set its Locked property to Yes, and enter the following as its Control Source property:

=DLookUp("[HTSUS Value]","HTSUS_by_Product","[Product Name]=""" & [cbxProductName].[Text] & """")

现在，在cbxProductName组合框控件的After Update事件中，单击省略号按钮[...]，选择代码生成器"，然后向cbxProductName_AfterUpdate()过程添加.Requery语句，如下所示

Now, in the After Update event for the cbxProductName combo box control, click the ellipsis button [...], choose "Code Builder", then add a .Requery statement to the cbxProductName_AfterUpdate() procedure, like this

Private Sub cbxProductName_AfterUpdate()
Me.txtHtsus.Requery
End Sub

尝试一下，看看它是否对您有用.

Give that a try and see if it works for you.

这篇关于如何使用基于另一个字段选项的表中的信息更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！