Page 1 of 1

[Solved]Form/subform with different foreign keys to same tbl

PostPosted: Tue Mar 19, 2019 6:34 am
by xkanuk
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.

Re: Form/subform with different foreign keys to same table

PostPosted: Tue Mar 19, 2019 6:07 pm
by UnklDonald418
Please save us all some time and effort, upload a sample of your database.

Re: Form/subform with different foreign keys to same table

PostPosted: Wed Mar 20, 2019 2:02 am
by xkanuk
So sorry that I didn't think to provide a sample. In creating the sample I actually fixed the problem. On the table control I had "inserted" a listbox column instead of replacing the id_Ingredient column with a listbox column. I have the form working as intended and am now working out how to add additional information. I am attaching the working sample for closure.