Linking 3 unrelated tables in a form (1 Viewer)

jdusa

New member
Local time
Today, 11:16
Joined
Apr 25, 2011
Messages
2
I am making a db for my recipes – I have three tables Name, Ingredients, and Directions.

All three tables have a field named Recipe Number. The one in the Name table an auto fill number

The Name form is the main form, and the ingredients and directions are sub-forms. I can get the recipe number to pass for the main form to the each sub-form but when I can not get it to save in the table these separate tables (Ingredients and directions) support.

Any ideas how I can achieve this would be greatly appreciated.

Respectfully,

joe
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:16
Joined
May 21, 2018
Messages
6,371
This recent thread was all about linking three subforms.
 

silentwolf

Member
Local time
Today, 09:16
Joined
Jun 12, 2009
Messages
344
Hi,
just for your information "Name" is a reserved keyword in Access and you should not name your tables "Name".
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:16
Joined
Oct 29, 2018
Messages
19,019
Hi Joe. I know you joined a long time ago. But since this is only your second post, welcome to AWF!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:16
Joined
Feb 19, 2002
Messages
36,453
You don't have enough tables. You need a junction table to connect Recipe with Ingredient since Ingredients can be in many recipes and recipes have many ingredients.

I'll include a m-m sample so you can see how this works

After you make the junction table, continue by opening the relationship window and making a proper relationship and enforcing RI.

The sample has only one subform on the main form. You would add a second to hold the directions.

The main form is connected separately to each subform, they are not hierarchical. these are two separate relationships that happen to use the same FK. Use the Master/Child property of the subform control to connect the subform to the mainform. Access will do this automagically if you have already created relationships in the Relationship window.
 

Attachments

  • ManyToMany20210414.zip
    1.5 MB · Views: 181

HillTJ

Registered User.
Local time
Today, 05:16
Joined
Apr 1, 2019
Messages
536
Pat, Great Demo. I particularly liked your 'dynamic' menu method. I've always hardcoded my menu options & was thinking of some kind of dynamic menu. Helped a lot. I also liked the way you captured 'changes' to objects with the sys table. Stuff I'll try and adapt.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:16
Joined
Feb 19, 2002
Messages
36,453
Glad you found the sample useful. Not many people examine them that closely so good for you:)

PS, the menu is based on the A2K switchboard which was the last one before they switched to embedded macros. I did expand it though to allow for more than 8 items. I have a different example for that if you want it. I've posted it many times and it might be in the samples
 

Mylton

New member
Local time
Today, 09:16
Joined
Aug 3, 2019
Messages
15
Glad you found the sample useful. Not many people examine them that closely so good for you:)

PS, the menu is based on the A2K switchboard which was the last one before they switched to embedded macros. I did expand it though to allow for more than 8 items. I have a different example for that if you want it. I've posted it many times and it might be in the samples
Good afternoon.
just following up.
If you could post or direct where I can find this other example I would be grateful.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:16
Joined
Feb 19, 2002
Messages
36,453
Here's a link

 

Users who are viewing this thread

Top Bottom