Referential Integrity Trouble: Can't Add or Change Record... (1 Viewer)

[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".

 

[Pat Hartman]

tblElectrodeMF is the one-side and tblEpisode is the many-side. This means that the primary key of tblElectrodeMF is stored in tblEpisode. When you connect these two tables in the relationship window, tblElectrodeMF primary key that is stored in tblEpisode becomes a "foreign key". In this relationship, RI should be enforced and if tblElectrodeMF does not use an autonumber primary key, Cascade Update should be selected but Cascade Delete should NOT. You don't want rows from tblEpisode to be deleted if a row is deleted from tblElectrodeMF, you want RI to prevent a rod in tblElectrodeMF from being deleted as long as any row in tblEpisode references it.

 

[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.

 

[Pat Hartman]

The problem is three-fold, first part is that the subforms are in the tab order between fields of the the row being added by the mainform. When the focus leaves the main form to move to the subform, Access needs to save the record on the main form. However, at this point in time, many fields are still unfilled and that is what is causing the error messages. The second part of the problem is that the foreign key fields are defined in the table with a default value of zero. Their default should be null or a valid value from the table. When the focus moves to the subform, some of the foreign keys have not been entered, therefore Access applies their default which is zero which is invalid. The third part of the problem is similar to the first in that when focus leaves the first tab, Access needs to save the record but again there are fields on the second form that are part of the record that is being saved and their values are defaulting to the default value which is invalid.

A solution is to change the default of the foreign keys to null and move ALL fields from the main form record to the first tab. Then move the subforms to the second tab. This will ensure that Access can save all the main form fields when focus moves to the second tab.

 

[Ally]

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