Referential Integrity Trouble: Can't Add or Change Record...

[Ally72]

Up until now I've only used Referential Integrity with tables that store such things as patient / staff information fed from subforms. After a course and reading stuff on the forum I've added it to every table, but this is causing me problems and think perhaps I don't need it here, but when I think about it I probably do, ie if a field from the lookup table were to be changed, then it does need to update, although wouldn't this do this automatically? As you may tell I am confused!

Problem: My main table is tblEpisode and I have various lookup tables. On entering data in my form, I get to a certain field then get an error message:

You can't add or change a record because a related record is required in table tblElectrodeMF.

If I then delete the RI between tblEpisode and tblElectrodeMF, it would only keep on going with error messages through all the lookup tables, until I had deleted all the RI from every one.

I did add code to save the record OnOpen but this hasn't made any difference.

Can anyone help please?

 

[fneily]

First, you don't HAVE to have RI, but it's a nice security feature if you have others entering your data in your tables that are related.

Remember, RI means that for every record in a related table(also called the Many side or CHILD), there's has to be a related record in the primary table (also called the One side or PARENT). You can have parents with no children, but all children must have parents. So you can not changed or delete any parent with a child or children. And you can not change a childs parent to a parent that doesn't exist.

Now, if you are going to change a parent, you must change its children also, so in the Edit relationship window where you set RI, make sure you also place a check next to Cascade Updates. Also if you want to delete a parent and its children, place a check next to Cascade Delete.

So if you have a main form, make sure the fields from the related table have a connection back to a parent.

Neil

 

[Ally72]

There are relationships between all the relevant tables.

What happens is, if I come out of the field that I'm trying to enter, make an entry in the Electrode field and go back to the original field I was trying to enter, then the same error message comes up but with reference to another table. So I then go and enter into another record referencing that table, go back to the original field and so on. Basically this happens about 4 more times, where I enter into all the fields that reference a lookup table. Then I can carry on and enter all the other fields. So it will work, but I would have to enter all the fields that reference lookup tables first which isn't going to work.

I'm sure it must be easier than this.

 

[Ally72]

I have changed the defaults of the lookup fields to Null rather than 0 and this has sorted the problem. Thank you for your comments.

Ally