Entering data with many to many relationship

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

 

[Jim Bunton]

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