Link table to itself, programmatically

[Zoran612]

Hi,

The problem is I would like to use Cascade-to-Null relationship which can be done using nice code by Allen Browne http://allenbrowne.com/ser-64.html, but within one table. For instance in a table "Empoyees" which has a field "RepotsTo" that refers to another employee in a same table. I don't want to delete empoyees by deleting a supervisor!

Thanks.

[Stewart Ross]

Could you perhaps amplify what it is you wish to know? Self-joining tables is straightforward , but I am entirely unclear what you are asking.

To remove the 'reports to' relationship of a particular row in an employee table the 'reports to' field is simply reset to null. The excellent code you link in post 1 above applies to multiple-table relationships rather than self-joins.

-Stewart

[Zoran612]

OK, I will be more clear.

I have a table which is joined to itself, something like table "Employees" in which there is a field ("ReportsTo" ) that is related to the primary field of the same table ("EmployeeID "). When I try to delete a record I get a message that either there is a related record in the table or that the record is locked (depending of whether the Cascade Deleted Records is selected in Relationships window). Cascade-to-null is a fine solution that I use a lot, but I don't know how to apply it in this situation. (How to refer in VBA to that phantom table "Employees_ 1" that is automaticaly created when joining tables in Relationships Window?)

[FishVal]

OK, I will be more clear.

I have a table which is joined to itself, something like table "Employees" in which there is a field ("ReportsTo" ) that is related to the primary field of the same table ("EmployeeID "). When I try to delete a record I get a message that either there is a related record in the table or that the record is locked (depending of whether the Cascade Deleted Records is selected in Relationships window). Cascade-to-null is a fine solution that I use a lot, but I don't know how to apply it in this situation. (How to refer in VBA to that phantom table "Employees_ 1" that is automaticaly created when joining tables in Relationships Window?)

Hello, Zoran.

Use the same table name. Numbered alias is a Relationships pane feature, nothing more.
DRI set this way will not allow to delete PK from table view / form either.
However delete query will work as expected.

Regards,
Fish

[Zoran612]

Thanks,
this helped. I'm fresh to MS Access and all this VBA stuff...(this is an excuse for such a simple question)

[NeoPa]

No excuse required :)

Self referencing tables are not a simple concept to grasp. Logical possibly, but not simple.