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

[Ally]

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 do a search on this on the forum and didn't come up with anything to help with this particular problem, although on reading Cannot add or change record I did add code to save the record OnOpen.

Can anyone help please?

 

[Rich]

As a guess I'd say the relationships are the wrong way around

 

[Ally]

Not sure what you mean. I thought they could only go one way - "one to many".

 

[Ally]

I tried just using cascade update but that made no difference.

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.

 

[Rich]

Post a copy here with some dummy data, it'll have to be in 97

 

[Ally]

Thank you - is in '97, zipped.

Go to frmPatient and Add New Episode from that form. You can enter the first two dates, then the error occurs.

Have had to remove quite a bit to be able to attach it but all the main stuff's there.

 

[Ally]

Thank you Pat. Have made the defaults of the values Null and it's working fine!