Lets assume that I have three tables: Media, Author, and Media/author_xref:
There is a many to many relationship between Media and Author.
Media/author_xref is the cross reference table.
Media
-------
ID
Title - text
Author
-------
ID
Name - text
Media/author_xref
---------
ID
Author - foreign key to Author.ID
Media - foreign key to Media.ID
I know that I could create a form based on the Media table with a multivalued lookup field to the Author table, but my client has a problem with having to scan through too many items in combo boxes. She wants only single valued combo boxes because she can type the first few characters of an item and have the combo box jump to items starting with those characters.
Instead, I am wondering about having the Media/author_xref table as a subform in a form based on the Media table. In this subform I would just have the ID and Author fields of the table visible, and the Media field would be filled in automatically for each record. The user would select an author from a combo box (looking up Name from the Author table) for each record. In this way a Media can have multiple Authors, and an Author can create multiple Media.
Here is a diagram:
Media form:
ID: xxxxxxxxxx
Title: ***text field***
Media/author_xref subform:
ID: xxxxxxxxxxxxx
Author: ***combo box displaying all Author.Name***
Any insight regarding how to accomplish this would be appreciated.
Thanks kindly,
Craig