Many-To-Many Forms problems - classic

[PaulWilson]

Folks,
I'm new to the forum but I have a problem that no one seems to know how to solve. It seems like a basic thing to me, but everyone I talk to seems to avoid it. I have a database of books and authors where one author can write many books and each book can have many authors (classic many-to-many). The problem is how do I build ONE form where the user can add-update-delete books and authors?

I provide a sample database as an Access 2007 file. The form called frmReadme explains the problem.

How do I build such a form?

PaulWilson

 

[plog]

3 tables: books, authors, booksauthors.

books has an autonumber field called book_id which is unique to every book
books also has other data (title, pages, language, etc)
authors has an autonumber field called author_id which is unique to every author
authors also has other data (first name, last name, gender, birthday, etc.)
booksauthors has just two fields--book_id and author_id

2 Forms:
books based on books table which allows you to add/edit all the fields in the books table.
sub_books based on booksauthors, this is a continous form with just one drop down based on a query of booksauthors and authors--this drop down will have 2 columns--one not visible which is the author_id field from the booksauthor table and a second column which is visible and displays the name of the author from the authors table.

Insert the sub_books form as a subform to the books form linking the two by books_id.

That form now lists one book's data which can be edited as well as all the authors that wrote it.

 

[PaulWilson]

Thanks Plog,
Your suggestion worked. The key was that query based on the junction table linked to the authors table. I uploaded a new database having the type of form you suggested. I also noted that the authors table did populate with new authors and the many-to-many relationships worked.

Problem: I need the author name entry control on the subform to be an editable combo-box based on the existing authors table. Currently, if an author writes more than one book, his/her name has to be entered manually and there will be more than one entry for this author in the authors table. For example, the author David Brooks has written several books. I just added another of his to the sample database. Now he has two entries in the authors table.

Do you know how I might accomplish the lookup in the subform without using a table-level lookup. I imagine it might involve some VBA and a query based on the authors table?


PaulWilson

 

[plog]

2 mistakes:

1. The subform's source should be that junction table--the way you have it you are directly adding data to your author's table. What you want to do is add links between books and authors already in your author table.

2. The only input on the subform should be a dropdown of authors. This allows you to control the addition of authors to the authors table.

I created a NewSubform field and have uploaded it to show you how it should look and operate.

 

[MikeSpaans]

Hello Paul,

I was wondering if you ever solved your 'adding author names in the subform' issue. Plog's solution did not really address that issue.
I'm facing the same puzzle at the moment with a database for articles. Some have multiple authors. When populating the database it should be possible to add new authors and select already present authors. Mine is even more complicated since I'm working with first and last names.

I am curious how you solved your issue. Maybe you could post you end result for some inspiration? Would certainly appreciate it.

May thanks,

Mike

 

[plog]

The correct way to add new authors is by having a form just for that. That way they get added and then your drop down populates with that new author.

I would recommend a button on you sub-form that opens up a form to add new authors. Once finished you close it and the subform refreshes and that new author is available to be select.

 

[MikeSpaans]

Thanks Plog, I was thinking about doing that and will now certainly give it a go.

Just found a MS Access database template [due to forum rules I can't post the link here) which works in that same way only with out a button to add new authors but with by double click.

 

[PaulWilson]

I proposed a books and authors database as an example of a many-to-many design. The issue was how to populate the required junction table with records from the established authors list without using table-level lookups.

I also needed to add, update and delete authors from the current list.
The form had to be smart enough to tell the user that the author wasn't on the list and bring up the authors form so it could be added.

I finally figured it out.

What makes it easy is my version of Access 2007 has a property on comboboxes called "list items edit form" which allows what plog suggests.
You set the limit to list value to "yes" and create a form where the user can enter new authors. The system takes care of the requery on the combobox after you close the form.

Attached is the sample database showing the concept.

 

[PaulWilson]

MikeSpaans,
I added it today. It's what plog suggested with a few additions. I hope it can serve as a template for you and others.