I have a MS Access database with three tables:
Foods (Id, Food_Name, Food_Desc)
Ingredients (Id, Ingredient_Name)
IngredientsFoods_mm (Id, Id_FoodsFK, Id_IngredientsFK)
...in a many-to-many relationship, where
IngredientsFoods_mm is the table that links the other two.
Now, I made a form with a subform to assign ingredients to foods. In the main form there are the
Food_Desc fields and in the subform there is the
Ingredient_Name field (a combo box). Seems to work well, but the problem is that when I enter an ingredient that is already in the
Ingredients table, instead of assign the appropriate record, it creates a duplicate record.
I tried to avoid this by setting an index without duplicates in the
Ingredient_Name field, but then Access tells me that that would create an integrity error.
The question is, how can I avoid the creation of duplicate records in the
Ingredients table when entering info from the subform?