Unanswered: Problem Enforcing Referential Integrity
I am using Access 2000 and have recently related three tables to one parent table using a common client ID # which is the primary key on the parent table. I have checked both boxes to cascade updated related fields and delete related records. I enterted a new record (client ID #) in the parent table, but when I looked at the other tables, the new ID was not added. This is what I want for the outcome. I tried changing the order in which I related the tables (parent >and I tried the 1 to 1 and the third option under join properties. What am I doing wrong? Or what else did I miss that I need to do so that one a new record is entered in the parent table, the new record is added to the related tables as well?
Cascading Updates does not add a new record to child tables when a record is added to the parent table.
Rather, it UPDATES the child records when the parent record is changed.
With referential integrity, you can not add a new record to the child table until after the record is in the parent table - that's what it checks for you.
For example, say your parent table had a person's name and your child table had phone numbers. Adding a person the parent table does not automatically create a record in the child table - what phone number would it use? You add the new person and then add the person's phone number.
Sure there is, but not using referential integrity alone.
For example, using the person-phone example above, if the phone table is setup as a sub-form of a form based on the person table, when a new phone number is added, Access will apply the correct key value from the parent form.
You can do it a variety of ways manually using code as well.