Data Entry Form with a Many-to-Many Relationship

[RobertJohn]

Hi all

I am using Access 2007 to start a small home library application, and so far it has two tables.
1. Books, with fields Book_ID (Primary Key) and Title, and
2. Authors, with fields Author_ID (Primary Key), First_Name and Last_Name.

Both tables will be expanded later, after I have solved my current problem

Because one book can have multiple authors, and because one author can have several books, I want to use a many-to-many relationship. To do this I created a third table (a junction table??) called Books_Authors, with fields Book_ID and Author_ID. I then created a One-to-Many relationship between Books and Books_Authors on the Book_ID fields, and similarly with the Authors table. As I understand it that should now give me a Many-to-Many relationship between my two main tables, Books and Authors.

Now comes the problem. I want to create a form for data entry, based on the Books table, but which allows me to enter one or more Authors for each book title. I tried using a form with a subform, and this seemed to work up to a point. It filled the two main tables appropriately, ie the Books table contained a list of book titles, and the Authors table contained a list of authors. But the junction table was empty! (and presumably this means that the Many-to-Many relationship is just not working.)

I received an error message (Field cannot be updated) when I tried to enter data into the fields in the Authors subform

I would be grateful for any assistance with this.

Thanks in advance

Robert

[hulasm]

I don't see any attachment can you guys help me out with the same problem.

[ADezii]

The SQL for the Row Source for [Author_ID] (qryBookAuthorL ink) is posted below. This Combo Box consists of 4 Columns with only the Last Column (Full Name) being displayed. What makes this work is the fact that Column 1 ([Author_ID]) is the Bound Column in order to populate the Junction Table along with [Book_ID] which is the Linking Field (Child) in the Sub-Form.

P.S. - I recreated the Attachment for the benefit of other Members who may be interested.

SELECT Authors.Author_ID, Authors.First_Name, Authors.Last_Name, [First_Name] & " " & [Last_Name] AS [Full Name]
FROM Authors
ORDER BY Authors.Last_Name;