A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Entering data with many to many relationship



 
 
Thread Tools Display Modes
  #1  
Old January 20th, 2010, 05:27 PM posted to microsoft.public.access.forms
becca
external usenet poster
 
Posts: 44
Default 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  
Old January 21st, 2010, 07:37 PM posted to microsoft.public.access.forms
Jim Bunton
external usenet poster
 
Posts: 72
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.