MS Access 2007 Form fields to update based on another form field


I am trying to do something that seems very easy but with no success.

I have 2 tables:  Table1 has a field product.  Table2 has a field product, productfamily, etc.
I have a bound form with a combo box that is controlled with table1.product.  When the user selects the product from the list in the combo box I want the next field in my form to show the cooresponding productfamily for that product that was chosen.

I have tired this:  On the form in the combo box that list the products I have an AfterUpdate event that says me.combo2.requery
Then for the second combo field (which I want the product family to display in) I have set up a query on the row source property.  But it is not working.  I may not be setting up the second combo box correctly or the query might not be right.  

I can select my product from the first combo field on the form but the second combo box just displays a blank.

I am totally new to Access 2007 and I am OK at Access.

I appreciate your time and help.
Thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What table are you trying to edit with your form?  Are you trying to allow the user to change the product family, etc., for a given selected product?  In which case you're actually editing the record on Table2.

or are you editing something else, (i.e., Table3) and you just want to pull in the relevant data from table 2 based on the product selection -- for read only purposes on the form.

In the former case, you would actually be saving the new product/product family combination to table2.  In the latter case you would be saving the product reference to table3 and the information from table2 is strictly informational.

The solution may differ slightly depending on what you're trying to do...
MeowserMAuthor Commented:
It would be the second case where it only shows for informational purposes.  I figure that I don't need to save the information in a table because if I wanted to report on it later I could just do that in a query.

So I would not be editing a record.

Hope that makes sense.

MeowserMAuthor Commented:
I figured it out - I was making it harder than it had to be (surprise)

I didn't need a second combo box I just used a text box and put the following code as my AfterUpdate event on the first combo box:

     Me.txtproductfamily.Value = "SELECT ProductFamily FROM" & _
                            " Products WHERE Product = " & Me.cmbProduct & _
                            " ORDER BY ProductFamily"
Answer_dude - Thank you for your time.  I will assign points to you for your help.

Thanks again.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.