Weird record level locking with a self join

[Paul H]

I have an Employees table with the following fields:

EmployeeID SupervisorID
Fred Bob
Bob
John Bob
Mary Bill
Bill

I have created a self join in the relationships window, with
referential integrity and cascading updates set. My database settings
are as follows:

Default record locking: "no locks"
Open db using record level locking: checked

If I Change "Bob" to "Bobby" in the EmployeeID column I get the
following error:
"Could not update; currently locked. (Error 3218)"

If I perform the same action with record level locking unchecked, the
change "Bobby" propagates, as expected, to the SupervisorID column:

EmployeeID SupervisorID
Fred Bobby
Bobby
John Bobby
Mary Bill
Bill
If I set Default record locking to "Edited Record" the above behaviour
is the same.

I simply can't get my head round this; I would have expected the
opposite of what is happening. So my understanding is of the above
behaviour is:

If I select record level locking I can not edit the current record.
If I select page level locking I can!! Huh!?! Surely the current
record is part of the page!

I just want to create a self join and cascade updates from EmployeeID
to SupervisorID. But I also want to understand what is happening
above.

Paul

[Tom van Stiphout]

On Tue, 18 Mar 2008 10:25:48 -0700 (PDT), Paul H
<go****@comcraft.freeserve.co.ukwrote:

I'm going to avoid the real issue: Your EmployeeID and SupervisorID
fields should be numeric - Long Integer, and no cascade update is
needed. Names like "Bob" should go in a FirstName field.

-Tom.

>I have an Employees table with the following fields:

EmployeeID SupervisorID
Fred Bob
Bob
John Bob
Mary Bill
Bill

I have created a self join in the relationships window, with
referential integrity and cascading updates set. My database settings
are as follows:

Default record locking: "no locks"
Open db using record level locking: checked

If I Change "Bob" to "Bobby" in the EmployeeID column I get the
following error:
"Could not update; currently locked. (Error 3218)"

If I perform the same action with record level locking unchecked, the
change "Bobby" propagates, as expected, to the SupervisorID column:

EmployeeID SupervisorID
Fred Bobby
Bobby
John Bobby
Mary Bill
Bill
If I set Default record locking to "Edited Record" the above behaviour
is the same.

I simply can't get my head round this; I would have expected the
opposite of what is happening. So my understanding is of the above
behaviour is:

If I select record level locking I can not edit the current record.
If I select page level locking I can!! Huh!?! Surely the current
record is part of the page!

I just want to create a self join and cascade updates from EmployeeID
to SupervisorID. But I also want to understand what is happening
above.

Paul