Clearing a field

[Guest]

I have one form with part numbers and the tools used to install the part. A
second form has the tools and their leasing information. I used a look up
table and the tool number is stored in the part number table. I know this is
wrong, but I built the database a long time ago and I don't have the time
right now to reconstruct it. My problem is that if I delete a tool from the
database, the tool number stays in the part database. I don't want to delete
the entire record, just clear the field where the now deleted tool number
was. How can I do this? Thanks a lot!

 

[Allen Browne]

What is the relationship between your tables?

I think you have:
- a Tool table, with a ToolNumber primary key
- a Part table, with a ToolNumber foreign key.
So, one Tool can be used in many Parts?

Now you want to be able to delete a Tool, and its ToolNumber should change
to Null wherever it is used in the Part table.

If that's the idea, it is actually possible to create a cascade-to-Null
relation between the Tool and Part table. This kind of relation is available
only in Access 2000 and later, and it can only be created programmatically.
But once you have set up the relationship, there is nothing more to do.
Access will automatically update Part.ToolNumber to null when you delete the
tool.

If you are interested in creating this kind of relation, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html
The article gives you the code you need to create the relation, and includes
a sample database that demonstrates the code.

 

[Guest]

Thanks Allen, that worked perfectly!

What is the relationship between your tables?

I think you have:
- a Tool table, with a ToolNumber primary key
- a Part table, with a ToolNumber foreign key.
So, one Tool can be used in many Parts?

Now you want to be able to delete a Tool, and its ToolNumber should change
to Null wherever it is used in the Part table.

If that's the idea, it is actually possible to create a cascade-to-Null
relation between the Tool and Part table. This kind of relation is available
only in Access 2000 and later, and it can only be created programmatically.
But once you have set up the relationship, there is nothing more to do.
Access will automatically update Part.ToolNumber to null when you delete the
tool.

If you are interested in creating this kind of relation, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html
The article gives you the code you need to create the relation, and includes
a sample database that demonstrates the code.