Enter data into a table with data from a form showing two tables

[ribbs2521]

I have three tables;



Foods: ID, UPC, description, brand, (various nutritional details)
Recipes: ID, Recipe_Name, Description
Recipe_Ingredients: Recipe_ID, Food_ID, quantity, uom

I have a main form which shows the Recipe and is only tied to the Recipes table, then a sub form that shows the ingredients. The ingredients form is based on the Foods and Recipe_Ingredients tables WHERE Recipe_Ingredients.Recipe_ID=Recipes.ID.

I would like to be able to enter data into the ingredients sub form and have it create a new record in the Recipe_Ingredients table but for some reason it keeps giving me an error because the Food_ID field is not getting populated.

I have a combo box allowing the user to select a food based on the Foods.description field. Would I have to write some VBA to input the Food_ID field from the data related to this combo box or is there a property somewhere that allows for that? Basically I want my combo box that shows Foods.description to enter Foods.ID into the Recipe_Ingredients.Food_ID field.

Sorry if this is overly confusing. The problem is I can make it work by attaching a macro to that combo box but I'm not an advanced Access user so I'm not sure if there is some simple feature already built in for this sort of thing. Please let me know if you need further clarification or some SQL code.

[RuralGuy]

Are you using the LinkMaster/ChildFields of the SubFormControl?

[ribbs2521]

I have no idea what those fields are are so I will definitely investigate that so I am apprised of that feature.

[ribbs2521]

Okay, I looked up some information and now I understand what these fields do. It looks like access automatically created the connection for these fields when I created the form. The problem I have isn't with the main form linking with the sub form, that's okay. When I add a new ingredient the Recipe_Ingredients.Recipes_ID field is filled in from the parent form data but the Food_ID field is not.

I guess, to try to clarify further, if I already have an ingredient in the recipe, say it's a Bagel and it has an ID 1234, if I change Bagel to anything else, the Food_ID field is not updated. I need the Ingredient combo box to drop down with selections from Foods.Description but fill in the Recipe_Ingredients.Food_ID when the new record is added.

As I said, I can make this work by assigning a macro when the selection is changed but I wasn't sure this was the correct thing to do or maybe I am on the right track and it is the proper way to handle this situation. I am just trying to do things the right way now, rather than just getting it to work whichever way I know how.

[RuralGuy]

The LinkMaster/ChildFields can contain more than one field separated by a ";". Does that help?