Updating from related tables

sstasiak

Registered User.
Local time
Yesterday, 20:02
Joined
Feb 8, 2007
Messages
97
I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.

When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.

I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.

Would setting the relationship solve that problem?
 
So what would you prefer to have in place of the deleted physician's name?
 
If the physician was deleted from tblPhysician, then the physician field in tblOncReg for all records that were assigned the deleted physician should be blank. I would think that is just standard practice to enforce data integrity throughout the database.

Again, I thought that setting the relationship would solve that problem, but I get the error I stated above when I try to set it.
 
But if you set referential integrity, this requires a related record in tblPhysician, the exact opposite of what you are suggesting.

If it was my application, I think I would still want to see the physician's name even if they were no longer current. I'd probably include a flag in tblPhysician to show current/non-current.

Alternatively, you could simply blank the physician name in tblPhysician, and leave the record and its PhysicianID present.

I assume it's the PyhsicianID you hold in tblOncReg, not the name.
 
After thinking about it, the name should remain even if the physician was removed from tblPhysician. Thanks for the help though.
 

Users who are viewing this thread

Back
Top Bottom