Can I Automatically Update Primary Key In Multiple Tables 1

[JoeF]

I am currently trying to create a database where I have information on recipes in various tables.
recipe info table includes fields detailing name of the recipe, the date, type, location, etc.
recipe directions table is linked to the first table in a one to one relationship and has fields for directions and comments.
recipe ingredients table is linked to the first table in a one to many relationship and has all the ingredients/measurements for each recipe.

Say that my main table (recipe info) has two keys: recipe and chef (one recipe can have more than one chef so I need two variables). I am attempting to link these items on a form so I can enter all the info for a recipe at once. Do I need to type the recipe and chef three times in order to enter data in all the tables?

 

[sdk]

Hi,

You can design a form to display all data, and which will include updating your related tables when you add a record.

You first need to create a form for each table. The form for the main table will incorporate the two others as subforms (forms within forms) and bear this in mind (ie one large, full screen form and two smaller ones). The ingredients table might best be made a datasheet than a form view.

In 'mainform' design view you can drag & drop the subforms into the mainform from the database window. In the properties of each subform, link the forms via your primary key in your mainform. The foreign keys in the subforms (the ones which relate to your mainform-table) do not need to be included in the subforms themslves, but if they are, make sure you are referring to the name of the field in the table, not the name in the form.

When you enter a record in the subforms, the data will be automatically included in the underlying tables (a nice access trick).

If you are a novice, post again and I will step you through the process, but trust me, it ain't hard and it does work.

I am not sure you need the chef key, unless you intend to use different ingred/methods for recipes. If so, you need to link the subforms to two fields.


Cheers

 

[JoeF]

No more explanation necessary. It worked great! I tried to create a form linked into all three tables before and it didn't do the trick. The subforms worked like a charm.