Page 4-49 of Grover Park George on Access

[jim may]

I'm a bit confused [on page 4-49] where Cascade Update Related Fields is discussed. Sentence 2 says:

"You can change a value in the primary key because cascading updates causes all corresponding records in the related table to be updated to the same value".

HOLD ON !! Before I move on,,, I've just committed to memory that the Primary key cannot be changed, like the autonumber say I'm using as a primary key.. Please Help me through this ** in dummy language ** please,,
TIA,

 

[Andrew Fergus]

Hi Jim

Yes there is a situation where you can change the value in a primary key.

Forget autonumbers for the moment - they are only one type of primary key and you can't change those (well there may be a way around it but let's ignore that for the moment).

For other types of primary keys (formatted as numbers, text etc.) you can change the value. Where the primary key is linked to another field in a another table under the relationships screen, then in the link properties you have to tick the box that says "cascade updates", before you can change the value in the primary key. The "cascade update" feature works like this : if you change the value in a primary key, then provided the relationships have been correctly set up (i.e. enforce integrity and cascade update) then Access will automatically change the values in the other linked tables.

However, if you haven't set up the relationships correctly, then you will get yourself into all sorts of trouble when changing the value of a primary key.

Getting back to the autonumber, yes you are right, the value can not be changed and I wouldn't dream of trying to do so. If the autonumber doesn't meet your needs you might want to consider another type of primary key. Lastly, you can't rely on the autonumber to give you consecutive values - this has been discussed elsewhere in this forum.

HTH, Andrew.