Linking main form with subform

[resolva]

Hi all,

I have a problem with a current database I have setup. Basically, I have made three forms, one the list project form, one the add new project form and another subform which shows the recipes.

I want it so the user can add a new recipe in the subform for a particular project. So they go to say the data of project 5 and add in a few recipes. I have tried doing it myself but I can't seem to link them correctly.

Would really appreciate it if someone could have a look at the database file I have attached.

Thank you in advance

 

[jzwp22]

Before I get to your form issue, I'm a unclear about your table structure and what your database is designed to do.

The way you have it set up, a recipe can only have 1 ingredient (even though your field name is ingredients), 1 activity etc.

I would recommend a structure like this:

tblIngredients
-pkIngredientID primary key, autonumber
-txtIngredientName

tblActivities
-pkActivityID primary key, autonumber
-txtActivityName

tblRecipes
-pkRecipeID primary key, autonumber
-txtRecipeName

tblRecipeIngredients
-pkRecIngrID primary key, autonumber
-fkRecipeID foreign key to tblRecipes
-fkIngredientID foreign key to tblIngredient
-spQty (amount of ingredient)
-fkActivityID foreign key to tblActivities (assuming that only 1 activity is necessary for each ingredient; if multiple activities are needed per ingredient, then another table is necessary)
-longSeq (sequence # to tell the order of the ingredient/activity)

 

[resolva]

Hey, thank you very much for the quick reply.

I have setup the database relationship again as you have suggested (attached below)

Could you verify if i have linked them correctly please?

Also the next step in the process... which of the tables do I need to make forms for?

Kind regards,

Aaron Luckie

 

[jzwp22]

Looks good so far. In your original DB version, you had something about projects. How do the projects relate to the recipes?

 

[resolva]

Basically...

They would set up a project... then in that project they would would add recipes to it. The ingredients are used to add to the recipes.

So they would make a new project (main form im guessing) then would add a new recipe to it (can add many of them aswell)(Subform im guessing) While making the recipe they can add ingredients from the ingredients table?

How would I go about doing this?

Would I firstly make a project page then link this with the recipe form?

 

[jzwp22]

You have to get your table structure set up before you worry about the forms.

So you'll need a project table:

tblProjects
-pkProjID primary key, autonumber
-txtProjName
other project fields


Let me ask some questions about the relationship between projects and recipes. You said that a project can include many recipes. Can a recipe be used in multiple projects?

 

[resolva]

I have re-attached the access file with the complete tables in it.

Let me ask some questions about the relationship between projects and recipes. You said that a project can include many recipes. Can a recipe be used in multiple projects?

In terms of the recipe...

A recipe can be included in many projects... For example you would have Project number A11, and then within that project you might have 50 recipes.

A certain recipe may need to be used in another project again yes but this is not essential.

Would be great if you could look at the relationships for me, thats the part that always brings me problems.

Thank you again for the help

 

[jzwp22]

The following describes a many-to-many relationship between projects and recipes

A recipe can be included in many projects... For example you would have Project number A11, and then within that project you might have 50 recipes.

A certain recipe may need to be used in another project again yes but this is not essential.

Even though it is not essential, it is possible and you have to take that into account. To handle a many-to-many relationship you need a junction table

tblProjRecipes
-pkProjRecID primary key, autonumber
-fkProjID foreign key to project table
-fkRecipeID foreign key to recipe table

I have attached the revised DB.

Out of curiosity, can you explain the fields BatchNo, modifications and monimers in your project table? Can a project have many batches? Do modifications refer to a batch?

 

[resolva]

Thank you for getting back to me. I have finally made all the connections with the relationships.

How would I go about next? I need to be able to pull The ingredient name from the tblingredients to the add to recipes if you say what I am saying. So when you make up a recipe you would pick ingredients from the ingredients table.

Which form would you create first if it was you?

Thank you again

 

[jzwp22]

You can create a form/subform setup. I would probably create a form based on the tblRecipeIngredients first and populate the ingredients field using a combo box based on tblIngredients. I would then create another form based on tblRecipes and then drag the 1st form into this new form to create a form/subform setup.