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.