What's your favorite Form Design for Many to Many User Interface.

[Thales750]

Mine has usually been two List Boxes (or sub-forms) with controls similar to Microsoft decision Wizards.

 

[The_Doc_Man]

The only time I had this kind of situation where I was using the GUI rather than VBA, I had a combo that allowed me to tell the form "Select this user." Then when the selection had been made, a different combo would select whatever was the other possibility (in this case, a group). So once I made that selection, the user selected for the form was one side of the junction table and the group from the combo was the other side.

 

[Thales750]

The only time I had this kind of situation where I was using the GUI rather than VBA, I had a combo that allowed me to tell the form "Select this user." Then when the selection had been made, a different combo would select whatever was the other possibility (in this case, a group). So once I made that selection, the user selected for the form was one side of the junction table and the group from the combo was the other side.

Yep, that is similar. The advantage of list boxes is rapidly selecting multiples from either lists and connecting them.

I use multi-select list boxes when multiple many to many connection are the norm. Like selecting contractors for projects and when they might have different leads for each project.

That's interesting about not using them, my databases have dozens of many to many connections in them. But, all of my databases are Project Management, Process Management, or ERP. I couldn't imagine them without multiple "many to many" connections. Although building systems based on many to many is an order of magnitude more development time. Often, I will use very elaborate Relationships Diagrams on the Relationship Build Tool. Not to enforce Integrity, but to keep track of myriad of Universal Linking Tables. In the end you get a much better model of reality than many to one, in many cases.

 

[Thales750]

One of the things to be careful of is how to control recursive connections. If you allow any of the tables to show up on both sides you can create infinite recursive connections.

 

[Pat Hartman]

Many-to-Many relationships usually need to be represented from each side. So, you need a main form for the left side with a subform bound to the junction table and showing the right side. Then you need a main form for the right side with a subform bound to the junction table and showing the left side. In either case, the subform has ONE combo and picks either the right or the left PK depending on what the subform is supposed to show. You can use popup forms rather than subforms to show the other side but I rarely do it that way.