Full Version: Many To Many Relationship Display In Single Window. Subform/splitform Solution
UtterAccess Forums > Microsoft® Access > Access Forms
CaptainMilly
Hello

Ive searched for a while now, but not found the solution to this. I can only see 'open new form' solutions and not sub-form solutions.

I have a form currently with one subform to display one side of Many-to-Many relationship and want it to display all of it.


1. Module (single record)
1.2 List all events associated with that module (continuous form)

I can make a button on each record in the subform to open a 3rd from to display records related on the other end of the many to many relationship, but ideally I would like the list to appear at the bottom of the continuous form. So I would end up with a 3rd level of the form:

1.3 List all people associated with that event (continuous form)


So, when I click button on each record of the subform at level 1.2 the records in level 1.3 change to the list related to currently selected record on subform at level 1.2.


I want the 1.2 to appear like a list, thus I can't use the detail there to make 1.3 subform. I think I need to split the subform, but not sure how to make the records on the second half change depending on which record is selected in the top subform 1.2


Any ideas?

thank you,

milly
theDBguy
Hi Milly,

The old technique of placing the 2nd subform in the footer section of the 1st subform might work for you. But, if you just need a "list" of attendees, then maybe a simple Listbox is all you need.

Just a thought...
CaptainMilly
Hi

Interesting! Thanks, will try that in the morning.

milly
theDBguy
Hi,

You're welcome. Let us know how it goes or if you have any more questions. Good luck!
tina t
hello Milly, if neither of DBguy's suggestions suit your needs, you can control one subform from another subform, both sitting in a mainform. a commonly used technique is to add an unbound textbox control to the mainform, i'll call it txtEventID. set the control's ControlSource property to the primary key fieldname of the event subform, as

=[SubformContainerControlName].[Form]![EventPKFieldName]

make sure SubformContainerControlName is the name of the "container" control in the mainform that "holds" the subform - it may be the same name as the subform, or a different name. check the Name property of the container control to be sure. and make sure that the primary key field of the event table is included in the event subform's RecordSource.

next set the LinkMasterFields property of the people subform control as txtEventID.
set the LinkChildFields property of the people subform control to the name of the related event ID foreign key field in the people subform's RecordSource.

once you've completed the setup, and confirmed that the correct primary key value is showing in control txtEventID, you can set that control's Visible property to False.

hth
tina
CaptainMilly
Hi

thank you both.

When I tried to put the 3 form to the footer, Access automatically set the 2 form to single record view, so that did not work (maybe I was doing something wrong)

So I went with Tina's suggestion and set both subforms at same level and linked them through the unbound control. Works great! Thank you very much! You've just made my day uarulez2.gif


milly
theDBguy
Hi Milly,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.