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

[ADezii]

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

I just created a Template for someone who had almost the exact same situation that you are currently in, and who struggled with implementing the logic. This simple Database consists of 2 Tables which would normally comprise a MANY to MANY Relationship (PCs to Software), joined by an Intermediate Table which created 2 - 1 to MANY Relationships. He also wanted to know how he could add various Software Packages under this design which has an uncanny comparison to what you are trying to accomplish. Rather than trying to explain in detail the steps necessary to Add Records under this existing structure, simple download the Database and should you have any questions, feel free to ask.

[RobertJohn]

I just created a Template for someone who had almost the exact same situation that you are currently in, and who struggled with implementing the logic. This simple Database consists of 2 Tables which would normally comprise a MANY to MANY Relationship (PCs to Software), joined by an Intermediate Table which created 2 - 1 to MANY Relationships. He also wanted to know how he could add various Software Packages under this design which has an uncanny comparison to what you are trying to accomplish. Rather than trying to explain in detail the steps necessary to Add Records under this existing structure, simple download the Database and should you have any questions, feel free to ask.

Thanks, ADezii

This is very helpful for me as a learning exercise. I am presently trying to work out why what I was trying to do, which looked very similar to your solution, doesn't work. I presume that with the PC-Software template it is necessary to populate the Software table before trying to use the PC form, ie the Software table is then used as a Lookup table. What I was wanting was to be able to enter my Authors onto the subform of the Books form. In other words, I wanted to open the main form, enter the Book details, and then move to the Authors subform where I could either select from the list of authors (from the Authors table) or add a new Author directly (presumably using a combo box).

Perhaps the easiest way will be for me to copy your model, ie using the drop-down list to select an author and, if it a new author, build in a button which takes me to another entry form just for the Authors table where I can add the new author and then return to the original form/subform and use the dropdown list which should now include the new author.

Hope this makes sense. If I can't get it to work I'll come back to you for advice.

Thanks again.

Robert

[ADezii]

Thanks, ADezii

This is very helpful for me as a learning exercise. I am presently trying to work out why what I was trying to do, which looked very similar to your solution, doesn't work. I presume that with the PC-Software template it is necessary to populate the Software table before trying to use the PC form, ie the Software table is then used as a Lookup table. What I was wanting was to be able to enter my Authors onto the subform of the Books form. In other words, I wanted to open the main form, enter the Book details, and then move to the Authors subform where I could either select from the list of authors (from the Authors table) or add a new Author directly (presumably using a combo box).

Perhaps the easiest way will be for me to copy your model, ie using the drop-down list to select an author and, if it a new author, build in a button which takes me to another entry form just for the Authors table where I can add the new author and then return to the original form/subform and use the dropdown list which should now include the new author.

Hope this makes sense. If I can't get it to work I'll come back to you for advice.

Thanks again.

Robert

Hello Robert. What I'll due is to modify my Template to suit your specific needs, then make it available to you an as Attachment. I cannot give you a specific time when this will be done, but if there is no great rush on this Project, I'll be more than happy to accommodate you.

[ADezii]

Thanks, ADezii

This is very helpful for me as a learning exercise. I am presently trying to work out why what I was trying to do, which looked very similar to your solution, doesn't work. I presume that with the PC-Software template it is necessary to populate the Software table before trying to use the PC form, ie the Software table is then used as a Lookup table. What I was wanting was to be able to enter my Authors onto the subform of the Books form. In other words, I wanted to open the main form, enter the Book details, and then move to the Authors subform where I could either select from the list of authors (from the Authors table) or add a new Author directly (presumably using a combo box).

Perhaps the easiest way will be for me to copy your model, ie using the drop-down list to select an author and, if it a new author, build in a button which takes me to another entry form just for the Authors table where I can add the new author and then return to the original form/subform and use the dropdown list which should now include the new author.

Hope this makes sense. If I can't get it to work I'll come back to you for advice.

Thanks again.

Robert

Hello Robert, first of all I had a little time, so ignore Post #4. Download the Attachment and I think you'll like what you see. In either event, let me know how you make out.

[RobertJohn]

Hello Robert, first of all I had a little time, so ignore Post #4. Download the Attachment and I think you'll like what you see. In either event, let me know how you make out.

Thanks again, ADezii

I haven't explored it in detail yet but it looks good.

Cheers

Robert

[ADezii]

Thanks again, ADezii

I haven't explored it in detail yet but it looks good.

Cheers

Robert

You are quite welcome.

[RobertJohn]

You are quite welcome.

Hi ADezii

I have now tried your database and it works fine for me. However, as part of my learning process I have tried to replicate it by building it from scratch, and have made some progress. I can get the form with its subform to work, ie I can enter a new book title then enter one or more authors, but only by entering first and last names each time. I cannot work out how to get the Lookup format at this point.

I have noticed that in your subfBooksAuthor s the properties for the Author_ID text box include a Row Source (qryBookAuthorL ink) whereas on mine there is no mention of Row Source. Is this the problem and, if so, how can I fix it?

I appreciate any help you can give me on this.

Thanks in advance.

Robert

[chemera2002]

Hello Robert, first of all I had a little time, so ignore Post #4. Download the Attachment and I think you'll like what you see. In either event, let me know how you make out.

I have the very same issue... where can I download the Template?

Thanks,

Newbie

[ADezii]

Hi ADezii

I have now tried your database and it works fine for me. However, as part of my learning process I have tried to replicate it by building it from scratch, and have made some progress. I can get the form with its subform to work, ie I can enter a new book title then enter one or more authors, but only by entering first and last names each time. I cannot work out how to get the Lookup format at this point.

I have noticed that in your subfBooksAuthor s the properties for the Author_ID text box include a Row Source (qryBookAuthorL ink) whereas on mine there is no mention of Row Source. Is this the problem and, if so, how can I fix it?

I appreciate any help you can give me on this.

Thanks in advance.

Robert

Hello Robert:
The Template I used is long since gone, so please Attach the original Template to a Message and I'll have a look at it.

[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;