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
11 10625 ADezii 8,834
Recognized Expert Expert
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.
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 8,834
Recognized Expert Expert
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 8,834
Recognized Expert Expert
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.
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 8,834
Recognized Expert Expert
Thanks again, ADezii
I haven't explored it in detail yet but it looks good.
Cheers
Robert
You are quite welcome.
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
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 8,834
Recognized Expert Expert
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Filips Benoit |
last post by:
Hi,
TBL_CONTACT_PERSON
CNTP_ID (auto)
CNTP_LAST_NAME (required = yes)
CNTP_FUNCTION (required = no)
CNTP_..... (all required = no)
FRM_CONTACT_PERSON_ADD_NEW
Property DATA ENTRY = YES
|
by: xxx |
last post by:
hi there,
I have a simple " expense " form which has a number of dropdowns and a
field to enter the money into. The app enters data into a single table.
For some reason it just stopped working. Data is no longer being
entered into the table. Before ( yesterday) we used to get a data entry
success confirmation dialog. When you now hit enter to feed data to the
table nothign happens.
Note : the dropdowns on the expense form, from the...
|
by: voidfill3d |
last post by:
I have a problem with ASP.NET and entering data into a MS SQL database.
I have the following
code and what happens is the data gets into the database, but with one
extra space at the end of
the entry. Is this preventable with something other than a trim in my
stored procedure? I know this is not necessarily in ASP.NET because I
returned the value with quotes around it and it shows up right, but
when I look in the database, it is...
|
by: david |
last post by:
When I compile a window form client for web service, there is error message
as follows:
Program
'D:\usr\winVBcontrol\WinClient4WebServiceFileCS\obj\Debug\WinClient4WebServiceFileCS.exe' does not have an entry point defined
Notice: I create a solution (perhaps for VB). In the sulotion, I created win
form clients for web service with VB in a window form VB project and they
worked fine. Now the current one is developed in a C# project under...
|
by: Radx |
last post by:
Here in my web application, I have a data entry page with serval controls.
Some of the controls have autopostback is set true.
But the problem is when two or more people are entering data at the same
time, in the middle of my data entry , it is brining someelse data on my
screen.
I look everywhere i could not find the solution. Please help me
Radx
| |
by: Miguel |
last post by:
I have an order entry database with two forms. One is for new orders
the other is to update orders. The forms are identical except that one
is strictly order entry. On both forms are three sets of sychronized
combo boxes. When entering test data everything worked perfectly. I
recently migrated 5,000+ records from an Excel file to the database.
The migration was successful and all records were copied over. When I
checked the underlying...
|
by: peter.bremer |
last post by:
Hi all,
I've got a SQL Server database that contains zipped information stored
in (binary) image fields. To complicate things, this zipped data is
combined with plain-text data.
I've verified the zipped data to be readable by SharpZipLib by writing
the field contents to a file and using a binary editor to manually
strip the plaintext data in front of it.
|
by: dba |
last post by:
with html and php using mysql.
How do I catch error's from a database like "duplicate entry" and display
something on the entry screen.
Also how do you clear the data boxes on a screen. When I use 'go back on
page' the data remains.
Thanks
|
by: Tyler |
last post by:
Made a data entry form which is a subform. I made a continuous form
that displays everything entered through the data entry form. The
data entry form displays all of the records. This doesn't make any
sense because it is a data entry form... am I missing something?
Thanks.
|
by: user5555 |
last post by:
After data entry I saved and closed the file ,second time when I open the Form completed data is not visible in Form,it cannot view pre-existing records,they exist only in tables.Data Entry set on "NO".
I can't find the problrm.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |