[Solved]Form/subform with different foreign keys to same tbl
Posted: Tue Mar 19, 2019 6:34 am
I have a slight twist on the recipe database concept. Here’s a quick example:
In my ingredients table I have things like:
Salt
Oregano
Garlic
Onion
Tomato puree
Spaghetti
Napoli sauce
Napoli sauce is an in-house ingredient that has a recipe. Let’s say the recipe includes:
Salt
Oregano
Garlic
Onion
Tomato puree
Now I want to have a recipe table for my in-house ingredients.
So I imagine the table construct would look like:
Ingredient table:
id_Ingredient: unique key
Ingredient_Name
some other fields
Recipe table:
id_Recipe: unique key
fk_Ingredient: the inhouse ingredient this recipe is for
Recipe_Description
Yield
Comments
Recipe_Ingredient table: (consists of multiple rows per recipe)
ID: unique key
fk_Recipe: referencing the main recipe table
fk_Recipe_Ingredient: this one refers to any specific ingredient in the ingredient table
Amount
Comments
I am struggling with creating a form/subform construct whereby mainform is the Recipe “header” and subform will be a table control that allows me to select ingredients from the Ingredient table.
When I insert a list box column in the table control I can’t seem to get it to select an Ingredient without it changing the “header” ingredient as well.
Not sure how better to describe this conundrum, but am happy to provide what ever extra details required to better describe this problem.
as usual, thank you in advance for your patience, understanding and assistance.
In my ingredients table I have things like:
Salt
Oregano
Garlic
Onion
Tomato puree
Spaghetti
Napoli sauce
Napoli sauce is an in-house ingredient that has a recipe. Let’s say the recipe includes:
Salt
Oregano
Garlic
Onion
Tomato puree
Now I want to have a recipe table for my in-house ingredients.
So I imagine the table construct would look like:
Ingredient table:
id_Ingredient: unique key
Ingredient_Name
some other fields
Recipe table:
id_Recipe: unique key
fk_Ingredient: the inhouse ingredient this recipe is for
Recipe_Description
Yield
Comments
Recipe_Ingredient table: (consists of multiple rows per recipe)
ID: unique key
fk_Recipe: referencing the main recipe table
fk_Recipe_Ingredient: this one refers to any specific ingredient in the ingredient table
Amount
Comments
I am struggling with creating a form/subform construct whereby mainform is the Recipe “header” and subform will be a table control that allows me to select ingredients from the Ingredient table.
When I insert a list box column in the table control I can’t seem to get it to select an Ingredient without it changing the “header” ingredient as well.
Not sure how better to describe this conundrum, but am happy to provide what ever extra details required to better describe this problem.
as usual, thank you in advance for your patience, understanding and assistance.