B
Becca
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!
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!