title: How to update a field with information from a table based on another fields option
body: <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>Product Name        Commodity
  --------------------------------------------
  Product #1          Soybean
  Product #2          Soybean Meal
  Product #3          Corn
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Commodity           HTSUS Value
  --------------------------------------------
  Soybean             1.1.1
  Soybean Meal        2.2.2
  Corn                3.3.3
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>      Product: Product #2 (Soybean Meal)
  HTS Commodity: 2.2.2 (Soybean Meal)
  </code></pre>
  
  <p>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.</p>