relationships

[Guest]

My database consists of 3 tables. All of them have Primary keys. They are
linked but will not relate with referential integrity no matter what I do. I
always get a message about not being able to enter new date into the "child"
tables when I try to relate them. I have tried everything that I have read in
several books and followed suggestions on this site for unchecking Name
AutoCorrect and letting Access create the relationships itself. Nothing has
worked. If I have to start over It must be soon. Please ket me know. My
living depends on it.
Thank You, Mari

 

[Guest]

Post your table sturcture - TableName and Field - datatype.

 

[John Vinson]

My database consists of 3 tables. All of them have Primary keys. They are
linked but will not relate with referential integrity no matter what I do. I
always get a message about not being able to enter new date into the "child"
tables when I try to relate them. I have tried everything that I have read in
several books and followed suggestions on this site for unchecking Name
AutoCorrect and letting Access create the relationships itself. Nothing has
worked. If I have to start over It must be soon. Please ket me know. My
living depends on it.
Thank You, Mari

You're joining on the wrong fields, it appears.

Note that we cannot see your table structures, nor do we have any idea
how you're trying to enter data. You can see your database; we cannot.

What are the Primary Keys in each table?
What fields are you using to link the tables?
What EXACT error message do you get when you attempt to join the
tables in the Relationships window?
How are you entering data - directly into the Table (not a good idea)
or into a Form with one or more Subforms (much better)?

John W. Vinson[MVP]

 

[Guest]

Post your table sturcture - TableName and Field - datatype.

Karl and John,
Thank you for replying.
The main table is the Membership Table: Member ID (Auto Number) This table
includes two Number fields (long Integer) for the other two tables. They are
Children's Table and Participation Table. The Children's Table includes
Member ID as a Number field (long Integer). The Participation Table includes
Member Id and Child's Key as Number fields (long Integer). There are other
fields of course: Date/Time, Yes/No & Memo, but most are Text. 32 fields in
Membership, 10 in Children's Table and 22 in Participation.
Relationships:
Membership Table=Member ID (1) to Member ID (many) in Children's Table.
Children's Table=Child's Key (1) to Child's Key (many) in Participation Table.
Message:
You cannot add or change a record because a related record is required in
table 'Membership Table'.
Please note:
The Children's table and Participation tables are subforms in the Membership
form and I work with that as my main screen. Let me know if you need more
information.
Thank you in advance.
Mari

 

[Guest]

The Membership Table does not need Number fields (long Integer) for the other
two tables.

I assume the Master-Child links for the Main to Children subform and the
Children subform to Participation subform were established based on the same
fields as the relationships.

Does the Main have a member showing when you are trying to add the new Child?

 

[Guest]

The Membership Table does not need Number fields (long Integer) for the other
two tables.

I assume the Master-Child links for the Main to Children subform and the
Children subform to Participation subform were established based on the same
fields as the relationships.

Does the Main have a member showing when you are trying to add the new Child?

Karl,
OK on the first sentence. I can remove them.

On the second sentence I'm assuming that you mean that I would have had to
establish the identical links between the forms that I have with the tables?
If so, at this point of the struggle I really don't remember if they all
match or not. Should I throw out the two subforms (they were brought in as
already made forms) and remake the subforms from the tables? or remake the
forms?

On the third sentence, the answer is yes.
thanks, Mari

 

[Guest]

Open the form in Design view and click on the upper-left most corner of the
subform. Right click and select properties.
You will see Link Child Fields and Link Master Fields.

 

[Guest]

Open the form in Design view and click on the upper-left most corner of the
subform. Right click and select properties.
You will see Link Child Fields and Link Master Fields.

Thank you Karl, I'll get back to you tomorrow.
Mari

 

[Guest]

Thank you Karl, I'll get back to you tomorrow.
Mari

Karl,
Partitipation Form 2:
Source Object= Participation Form 2
Link Child Fields= Participation ID
Link Master Fields= Member ID
? and now

Children's Form:
Record Source= Children's Table
Default View= Continuous Forms
This form will not give me Link Child Fields/Link Master Fields at all.
There are no links showing in Relationships "show all".
Unlike the Participation Form 2 that I created myself, I had the Wizard make
the Children's Form.
Do I need to recreate the Children's Form myself to get this relationship to
work?
Thank you, Mari