If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Entering data with many to many relationship
I want to create a form where I can enter data into two tables in a many to
many relationship (joined by a lookup table). I have table agreements and table contacts and they are joined through table AgreementContacts where I have a lookup field for each primary Key. Referrential integrity is inforced. I created a query with the lookup primary ke fields from the AgreementContacts table and all of the fields except the primary keys, from table agreements and table Contacts. The query works perfectly. I can enter: an existing agreement ID and and existing Contact ID an existing contact with a new agreement an existing agreement with an new contact or a new agreement with a new contact and it does't matter what order I do things in. However, when a create a form based on the query it stops working. I set up the form with the Agreement fields on the main form and created a tab for the contact fields (however I tried putting all the fields on one space also and have the same problem.) On the form, when I enter an existing or a new agreement it automatically creates a new contact ID. If I try to choose an existing Contact ID first it gives me the error message: "The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many only' table." I can still enter a new contact with an existing agreement as long as I enter the contact name first. I have tried everything I can think of to solve the problem. I don't understand why the query works but the form does not. Please help! |
#2
|
|||
|
|||
Entering data with many to many relationship
I think you need to use a form with a subform
for the one to many relationship the one is the main form the many is the subform. Essentially you design a form to query each table but on the main form leave a space to accomodate the subform. You just drag the subform into the main form. Click ojn the subform. In the properties dialogue set link child fields and link master fields appropriately main table key - subtable foreign key You will need to consider how top move from record to record in the subform. JimB "Becca" wrote in message ... I want to create a form where I can enter data into two tables in a many to many relationship (joined by a lookup table). I have table agreements and table contacts and they are joined through table AgreementContacts where I have a lookup field for each primary Key. Referrential integrity is inforced. I created a query with the lookup primary ke fields from the AgreementContacts table and all of the fields except the primary keys, from table agreements and table Contacts. The query works perfectly. I can enter: an existing agreement ID and and existing Contact ID an existing contact with a new agreement an existing agreement with an new contact or a new agreement with a new contact and it does't matter what order I do things in. However, when a create a form based on the query it stops working. I set up the form with the Agreement fields on the main form and created a tab for the contact fields (however I tried putting all the fields on one space also and have the same problem.) On the form, when I enter an existing or a new agreement it automatically creates a new contact ID. If I try to choose an existing Contact ID first it gives me the error message: "The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many only' table." I can still enter a new contact with an existing agreement as long as I enter the contact name first. I have tried everything I can think of to solve the problem. I don't understand why the query works but the form does not. Please help! |
Thread Tools | |
Display Modes | |
|
|