Error with "cascade update related field". Unable to change field.

[Vindicator]

Hi There,
I have created a database to record research studies data. Everything is organized as I want it, but I cannot modify a field (Study Name) which is in pretty much in all my tables. I have all my tables linked and the option “Cascade Update Related Fields”, yet when I change the “Study Name” it says it cannot perform the cascading change.
Here is how the database looks like. All tables include “Study Name” as a primary key. As soon as I have Table 4 connected it gives an error if I change the study name in Main Table:

MainTable 1à∞ Table2 1à∞ Table3 ∞à1 Table4

I suspect this is because it’s a ∞à1 relationship, but I don’t see why. Everything seems logical.

Cheers

 

[HiTechCoach]

IMHO, it sounds like you have some design issues with properly normalizing your tables

I find it best to NEVER change the value of a primary key. I have never had the need to do this with a properly normalized databases. I would urge you to use an Auto number. The study name probably should only be in one table. Your study name will be an indexed field with No Dups. To the end user, it will appear to be the primary key, but it really is not.

 

[Vindicator]

Hey Cheers dude. So i reorganized my database using an autonumber as the main connecting field while keeping Study Name, and now all is well.

 

[HiTechCoach]

Good news! Glad to hear you got it working!

 

[ions]

I have been reading a book by John L Viescas titled "Building MS Access Applications" and he has an entire section devoted to explaining why autonumber (artificial primary key) should be avoided in most situations.

What is your opinion?

 

[boblarson]

I have been reading a book by John L Viescas titled "Building MS Access Applications" and he has an entire section devoted to explaining why autonumber (artificial primary key) should be avoided in most situations.

What is your opinion?

I use it exclusively as a surrogate primary key. I do not like natural keys as they tend to have something change eventually. So, I use an autonumber for all of my tables and keys. I have not had problems except for maybe one time in one database. But other than that, all has functioned well.