Page 1 of 1

[Solved] Main subform linking

PostPosted: Sat Oct 19, 2019 12:11 am
by gkick

Contact and Activity table - one to many relationship,
Now I need to design a form either main sub or a couple of tablecontrols where I first select an activity from the activity table which then will display all those contacts who like skiing, etc.
My minds gone completely blank on how to use multiple master slave links.
The attached is just a dummy, in the real db the activity is an integer from a referencetable.
Thanks for your thoughts.

Re: main subform linking

PostPosted: Sat Oct 19, 2019 11:35 am
by Villeroy
In real life this would a many-to-many relation. Each person can be assigned to many activities and vice versa. Your one-to-many relation assigns each activity to one person. Therefore you can not select an activity and get all the related persons since there can be only one person per activity.

Re: Main subform linking

PostPosted: Sat Oct 19, 2019 12:51 pm
by F3K Total
Find attached your file, modified, according to the idea of Villeroy, as a sample.

Re: Main subform linking

PostPosted: Sat Oct 19, 2019 3:23 pm
by gkick
Wow, fantastic and thank you F3K Total and Villeroy !!! Exactly what I have in mind.

Aah, and thanks for refreshing my memory Villeroy. Of course I need a junction table which means a bit of a redesign of my mainform (see attachment).
The goal is to display in a different form then three table controls to tell the story of

Activity Category Activity Contact
select one pick one display all contacts who like Golf

To do that I need to change the Description to a Category table (Junction) tblContactsActivities as per F3K Total
In the mainform put the Category field of the category table in front of the activity
Some how filter(sync) the Activity listbox by the selection of the Category listbox, would you have an example of that as I also could use that for country and region
Thanks a lot guys for your time and help

Re: Main subform linking

PostPosted: Sun Oct 20, 2019 12:44 pm
by F3K Total
upload a sample containing all tables and their relations...

Re: Main subform linking

PostPosted: Mon Oct 21, 2019 4:48 am
by gkick
Hi, here s a quick and dirty mockup of my intentions put together at candle light (having some power outages here.)
The contact add edit form should allow multiple activities by different categories using listboxes, the selection of activity however limited to the category chosen.
The one and only query seems to to the right thing, the type acvtivity maintenance form is used to poulate the activities table
The original activity matching form needs a third control for the category ie
first select category, then select activity resulting in all contacts engaged in it
Hope this makes sense

Re: Main subform linking

PostPosted: Tue Oct 22, 2019 6:52 pm
by F3K Total
Try attached version using no listboxes. It's done without a macro. If you want to have a cascading listbox, there are tutorials in this forum, e.g. here:

Re: Main subform linking

PostPosted: Wed Oct 23, 2019 3:42 am
by gkick
Thank you very much, appreciate it.
Greetings from Chile