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_Name
and 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?