Problem with errror 3341

[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 3341:
"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!

 

[Becca]

Okay, I figured out the problem. I have a multivalued lookup field for the
contact type in the table contacts. If I delete this field from the form it
works. Problem solved, but does anyone know why? Is there a way to get
around this or do I have to find an alternative to the lookup field?

Thanks,
becca

 

[Becca]

Okay, I figured out the problem. I had a multivalued lookup field in the
table Contacts. When I remove that field from the form everything works
perfectly. But, i don't know why this is causing a problem. Is there some
way to fix this or do I need to find an alternative to using a multivalued
field? Any ideas on this?

Thanks,
Becca

 

[Gina Whipp]

Becca,

Have a look at for some of the reasons we recommend not using them...

http://www.mvps.org/access/lookupfields.htm

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Becca]

Thanks for the info.

 

[Gina Whipp]

You're welcome!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[james hall]

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 3341:
"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!