Recipe Design

[izen]

Hi there!

I have a problem about Recipe database. My database has 3 main tables
<Recipe table >
RecipeID (PK)
RecipeName
FoodCategoryID
PortionYield
PercentFoodCost
Instructions
Source
Picture

<Ingredient table >
IngredientID (PK)
IngredientName
IngredientCategoryID
Quantity
Unit
Cost

<RecipeIngredient Table>
RecipeIngredientID (PK)
RecipeID
IngredientID (Users choose the ingredients from the combo box which receives the ingredients from ingredient table)+++
Quantity
Unit

My problem is some of recipes can be ingredient in other recipe as well. For example, Napoleon sauce can be an ingredient in other recipes such as pizza topping, some pasta sauces, some stews.

In recipe form I have subform of RecipeIngredient table that users can add Ingredients that belongs in each Recipe

For example <Recipe table>
ReciepID 16
Reciep Name Napoleon Sauce
FoodCategory Sauces
PortionYield 20 Lt
PercentFoodCost 25%
Instructions bla bla bla
Source
Picture

subform <recipeIngredient table>
RecipeIngredientID 1
RecipeID 16
IngredientID 20 (tomoto)
Quantity 10
Unit kg
RecipeIngredientID 2
RecipeID 16
IngredientID 50 (basil)
Quantity 2
Unit each
RecipeIngredientID 3
RecipeID 16
IngredientID 100 (honey)
Quantity 500
Unit g
RecipeIngredientID 4
RecipeID 16
IngredientID 29 (tomato paste)
Quantity 800
Unit g

======================================
RecipeID 17
RecipeName Red wine beef stew
FoodCategory Main
PortionYield 10 lt
PercentFoodCost 25%
Instructions .....
Source .....
Picture ......
================================
subform RecipeIngredient table
RecipeIngredientID 5
RecipeID 17
IngredientID 30 >>>> (Chuck Beef)
Quantity 5
Unit kg

RecipeIngredientID 6
RecipeID 17
IngredientID Napoleon sauce***
Quantity 2
Unit lt


RecipeIngredientID 7
RecipeID 17
IngredientID 29 >>>> (tomato paste)
Quantity 400
Unit g

RecipeIngredientID 8
RecipeID 17
IngredientID 24 >>>> (Red wine)
Quantity 500
Unit ml

RecipeIngredientID 9
RecipeID 17
IngredientID 36 >>>> (beef stock)
Quantity 2
Unit lt

============================================
++++If ingredients in combo box comes from ingredient table We cannot choose recipe that can be ingredient this case is Napolean sauce wont show up in combo box


****the problem is Napoloen sause should have RecipeId or IngredientID as a reference


and could u pls tell me that kind of problem that I am dealing with how to deal with pls?


Pls check the attrached files

thank you for your time

 

[John Big Booty]

Welcome to the forum.

I'm not quite sure what the question was. But why not add an additional field/s to your ingredients table that lets you flag that ingredient as a recipe in it's own right, and links that ingredient back to it's recipe. Then in your form you test each ingredient to see if it is an ingredient or an ingredient/recipe. If it's an ingredient/recipe you could then have a button to show the recipe in a pop up form or some such.

 

[izen]

thank you for your suggestion

Unfortunately I dont still understand
could u pls explain abit more details pls?

cheers,

 

[John Big Booty]

If we take Napoli sauce as an example. You would have that stored as a recipe with say (for argument sake) RecipeID of 25. Now in your ingredients table you could add a field called RecipeID (which would have a default value of 0 {Zero}). So you are entering a recipe for Pizza and one of the ingredients in one quantity of Napoli Sauce. So you enter Napoli sauce into your ingredients table, now on the form there would be a button that allows you to select a recipe as an ingredient. You hit this button and it pops up a form with a combo box with all your recipes, from that box you select Napoli sauce and click a button that fires an update query that copies the Napoli Sauce RecipeID (25) and updates the RecipeID field in your ingredients Table to 25, and also closes the pop up form.

Now in your ingredients you would need to have the RecipeID field (from your ingredients table) as a non visible field. Using conditional formatting you could have any ingredient who's RecipeID is not 0(zero) high light somehow (bolded, or whatever you choose). You could then have an on double click event for the ingredients field, that would (depending on the Ingredients RecipeID either open the appreciated recipe, or the ingredients form.

I hope that make some sort of sense

 

[izen]

thank you again John!!

do u mind that I am breaking down in small step?

"So you enter Napoli sauce into your ingredients table" (Do I have to open ingredient form and actualluy add RecipeID 25, IngredientName Napoli sauce, the Qauntily, the CostUnit manually?)

IngredientID (PK) = can be anything
RecipeID 25
IngredientName Nap sauce
IngredientCategoryID...
Quantity 20
Unit lt
Cost $40

 

[John Big Booty]

All you need to store in you ingredients table is Napoli Sauce and the link to the recipe (via the RecipeID) the rest you can pick up via a query as you require it. How you get that information there is really up to how you wish the DB to operate. I would do it as suggested above.

 

[izen]

thank you john I am working on it
However, do u have a sample database that is similar with this circumstance for me to study ?
if not thats fine

thank you very much

 

[John Big Booty]

I'll try and mock something up during the day.

 

[John Big Booty]

Perhaps the attached will give you some ideas.

 

[izen]

Sorry that I responed your post very late cause my internet didnt work last few day

Thank you so much John !!!
I will study your sample database

Happy easter and a heap of thanks again

 

[izen]

Hi John
I cannot click Isingredient checkbox on Recipe form
It doesnt repond


why we have to lock and unenable checkbox ?
cheers,

Zen

 

[John Big Booty]

The reason I have put some safety features on the Is Ingredient check box; is that when that check box is clicked it runs an append query that adds that recipe name and recipe ID to the ingredients table. The first safety I have put on is the message box to confirm that you really want to add that recipe to the ingredients table. Once you click yes the recipe is added and the check box is locked, so that it can not be checked or unchecked again.

This is only my thoughts so you don't have to stick with them. You may have your own ideas on how this can be controlled. The main thing is to ensure that a recipe can only be added once and once only to the ingredients list.

 

[izen]

Hi john

I have a small problem after I added the recipe to the ingredient table. the focus goes back to the first recipe.

for example I have 5 recipes in the recipe table
After I added 3th recipe in ingredient table (click IsIgredient checklist) after it was added the focus goes back to the first recipe. I need still focus on 3 th recipe

and second problem is I like your double click idea on ingredient list so I would like to borrow your idea to add in my form

Private Sub IngredientID_DblClick(Cancel As Integer)

Dim stLinkCriteria As String
Dim stLinkCriteria2 As String

stLinkCriteria = "[RecipeID]=" & Me![IngredientID].Column(3)
stLinkCriteria2 = "[IngredientID]=" & Me![IngredientID]

If Me![IngredientID].Column(3) <> 0 Then
DoCmd.OpenForm "fIngredient", , , stLinkCriteria, , acDialog
Exit Sub
ElseIf Not IsNull([IngredientID]) Then
DoCmd.OpenForm "fIngredient", , , stLinkCriteria2, , acDialog
Exit Sub
End If


End Sub
Everything looks fine to me except when i click the blank ingredient list It doesnt pop the ingredient form up.
what is wrong in the code?

the last question on the Recipes that can be ingredients when i would like to update the recipe name or some properties on recipe form. the same recipe that was added to ingredient recipe doesnt change according to the changed infomation on recipe form. How can we make a relationship between recipe/ingredient ?

cheers,