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?
MeowserMConnect With a Mentor Author 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.
answer_dudeConnect With a Mentor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.