This should be simple enough

[MattBowe]

I have been requested to build a simple recipe database where a user may query by ingredient and receive a listing of all recipes that contain that ingredient. Or query by recipe and receive back all ingredients that complete the recipe.

The recipes need to be identified by a meal type (breakfast, lunch, or snack).

Every single ingredient is required to have one associative ingredient (ingredient = salt, associative ingredient = sodium).

My tables so far as follows:

tblRecip
RecipeID (pk)
RecipeName
MealID (fk from tblMeal)
MealName
Manufactor
ProductNumber
MainIngredient
Notes

tblMeal
MealId (pk)

tblIngredient
IngredientID (pk)
IngredientName

tblAssociativeIngredient
AssociativeIngredientID (pk)
AssociativeIngredientName

tblRecipeIngredient
RecipeIngredientID (pk)
RecipeID (fk fro tblRecipe)
IngredientId (fk from tblIngredient)
AssociativeIngredientId (fk from tblAssociativeIngredient)



Here is where I am having trouble:

A user interface (form) should allow the entry of a recipe, select in its meal type (from tblMeal) and populate other required fields from tblRecipe.

I need a subform (tblRecipeIngredients) that will allow the user to enter in ingredients and associative ingredients that will be linked to the recipe record they create. Those values should remain/be available for future recipe records (combo box/list?).

How do I link the subform? Within the tables, what do I use for the display control, row source type, and row source. I have tried various combinations and nothing seems to work.

 

[haj1503]

Hi MattBowe,

Hopefully this revision will solve your problem.

Bets regards,

 

[MattBowe]

I'm getting there, but..

Thanks for the response. However, how do I make the subform so that the user can add an ingredient name, not the ingredient ID.

A user is required to enter in an ingredient or associative ingredient name like "chicken" not the ingredient ID number that will be linked to tblRecipe. A recipe can contain many ingredients and an ingredient can belong to many recipes (like salt/pepper).

After entering in a recipe, a user needs to add ingredients and or associative ingredients into the subform. Then, when creating a new recipe record, the ingredient and or associative ingredients previously entered should be available to "select" in for the new recipe record. In addition, if an ingredient is not availble (previously enterd), the users should be able to add an ingredient.

Any more advice is greatly appriciated. Thanks you so much for replying.

Matt

 

[Pat Hartman]

I think your tables still need some work. Recipes and Ingredients have a many-to-many relationship. You can download the sample db that I posted a while ago to see how that type of relationship is implemented.

You can go to the Microsoft template gallery and download their version of a recipe database to give yourself some ideas.

Template gallery

tblRecip
RecipeID (pk)
RecipeName
MealTypeID (fk to tblMealType)
Manufactor
ProductNumber
MainIngredientID (fk to tblIngredient)
NumServings
PrepTime
Notes

tblMealType
MealTypeId (pk)
MealType

tblIngredient
IngredientID (pk)
IngredientName

tblAssociativeIngredient ???
AssociativeIngredientID (pk)
AssociativeIngredientName

tblRecipeIngredient
RecipeIngredientID (pk) (this field is not necessary, you can use a compound pk instead)
RecipeID (fk to tblRecipe)
IngredientId (fk to tblIngredient)
Quantity
UnitOfMeasure

 

[MattBowe]

Thanks

Thanks for the tip.