Form design to maintain many-to-many relationship

[mscertified]

I have two tables and a linking table and I need to provide a way for the
users to maintain these relationships.

tblKW: KWID and KWDesc
tblDT: DTID and DTDesc
tblKWDTXref KWID and DTID

I tried a form with a combo box for tblKW and a subform for related tblDT
records but that does not allow the addition of new KWID entries in the Xref
table. What is the best way to do this?

 

[Jeff Boyce]

A standard approach to providing users a way to deal with m:m relationships
is to use a main form/subform design.

You can pick which ever of the two main tables makes more sense as a
"parent" for the main form. Then create a subform based on a query, based
on the linking (relation/junction/resolver) table joined with the second
main table (for description). When you follow the Access wizard for
embedding this second form in the first (as a subform), you'll automatically
get the record ID from the main form's record inserted into the subform
record when you create a new "linking" record (in that subform).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

Thanks for the response.
Its difficult to use that approach the way my tables and relationships are
set up.
One of the 'many' tables is a self-referencing table (a tree structure) that
gets 'flattened' to form the actual table I use. I always access this
flattened table via a query that sorts it. When the table gets flattened, it
is deleted and recreated from the tree, so it can't be part of a
relationship. The table in my relationships is the self-referencing table.
Can I use your approach if I don't have a relationship defined between the
record source of the main form and the subform? I wouold want my main form to
be bound to the query for my flattened table.

 

[Jeff Boyce]

I may not fully understand your scenario, but binding forms (and reports) to
queries rather than to tables is often a preferred approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Hi mscertified

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.