CODEWITHSUNDEEP
ms-accessms-access-2007
Unexpected Pair of Colons
Unexpected Pair of Colons

Reputation: 769

How to update a field with information from a table based on another fields option

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.

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

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

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)

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.

Upvotes: 0

Views: 23813

Answers (2)

TSS
TSS

Reputation: 176

You need to use my favorite: dlookup

http://allenbrowne.com/casu-07.html

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123849

Here is one way to do it:

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;

It will produce results like this

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

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".

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] & """")

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.

Upvotes: 1

Related Questions