By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Data Entry Form with a Many-to-Many Relationship

P: 21
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
Feb 9 '08 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,607
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.
Feb 9 '08 #2

P: 21
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
Feb 11 '08 #3

ADezii
Expert 5K+
P: 8,607
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.
Feb 11 '08 #4

ADezii
Expert 5K+
P: 8,607
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.
Feb 11 '08 #5

P: 21
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
Feb 12 '08 #6

ADezii
Expert 5K+
P: 8,607
Thanks again, ADezii

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

Cheers

Robert
You are quite welcome.
Feb 12 '08 #7

P: 21
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 subfBooksAuthors the properties for the Author_ID text box include a Row Source (qryBookAuthorLink) 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
Mar 18 '08 #8

P: 1
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
Jul 30 '08 #9

ADezii
Expert 5K+
P: 8,607
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 subfBooksAuthors the properties for the Author_ID text box include a Row Source (qryBookAuthorLink) 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.
Jul 30 '08 #10

P: 1
I don't see any attachment can you guys help me out with the same problem.
Mar 30 '10 #11

ADezii
Expert 5K+
P: 8,607
The SQL for the Row Source for [Author_ID] (qryBookAuthorLink) 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Authors.Author_ID, Authors.First_Name, Authors.Last_Name, [First_Name] & " " & [Last_Name] AS [Full Name]
  2. FROM Authors
  3. ORDER BY Authors.Last_Name;
Attached Files
File Type: zip Books and Authors.zip (41.4 KB, 897 views)
Mar 30 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.