Dealing with Many to Many Relationships on Forms

[AdiDoyle]

Hi all,

I'm working on a database for collecting different types of texts. They can be in different languages, sometimes one text can have multiple languages. Similarly, a text can have multiple authors, and an author can have written many different texts. It's the same story with editors. This has meant that I've created a number of "assignment tables", the purpose of which is to link authors, languages, editors, etc to a given text.

Here is a picture of some of the relationships:




What I need to know is, when I create a form to input new records in the text table, is there an easy way to link fields these other tables (author, editor, language, etc.) to the text I'm inputting.

If I weren't dealing with many-many relationships where I have an assign table in between, I'd just have a drop down menu linked to the other relevant table so I could easily choose which record to link, but in this situation I don't know to deal with it.



I'd appreciate any advice or suggestions.

Thanks in advance.

[ssanfu]

Typically, you would use a main form/sub form arraignment.

Main form record source is a query on "Text_Table". (I use queries for form record sources - never tables - my preferences)

You have three junction tables "Assign_Author", "Assign_Language" and "Assign_Theme".
1) I would create 3 queries: "qryAssignAuthor", "qryAssignLanguage" and "qry_AssignTheme".

2) I would create 3 sub forms: "sfAssignAuthor", "sfAssignLanguage" and "sfAssignTheme". (sf = sub form).
Record sources would be the appropriate query.
Default view would be "Continuous Forms".

3) On the main form would/could be a tab control with 3 tabs named: "Assign Author", "Assign Language" and "Assign Theme". (Or you can put all 3 sub forms on the main form.......)

4) Set the Link Master field and the Link Child field to link the main form to the sub form for each of the dub forms. Because you have set the linking fields, whenever you create a new record in the sub form, the PK field value automatically gets copied to the FK field.

In each of the sub forms, do not display the "Text_ID" FK field.

For the form "sfAssignAuthor", the Author_ID control will be a combo box with the row source property a query based on the table "Author".
The query would be something like

SELECT Author_ID, Author_Surname & ", " & Author_forename AS FullName FROM Author ORDER BY Author_Surname, Author_forename

Set these properties of the combo box:
Column Count = 2
Bound Column = 1
Column Widths = 0

The other 2 sub forms set up accordingly.



BTW, NEVER display the PK field if it is an autonumber type.
See
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers
(What they are NOT #6)

[kd2017]

Anyone going to comment on the perfectly horizontal and vertical relationship lines? I like your style adidoyle