Set Default on Delete problem

[cooljholmes]

Hello all...

First let me say that these forums are invaluable and it is nice to see people helping each other out.

This is my first message and the problem I have is that I have is with setting a table to do Set Default for all foreign keys when I delete a table.

For example I have two tables that are linked, Donors and Donations. Each Donor has a unique ID (Donor_ID). The Donor_ID is stored in the Donations table as a foreign key to link these tables. When I delete a Donor what I would like it to do is to set all related records in the Donations table to a default value i.e. it would change the Donor_ID in the Donation Table to a "DON1". How do I go about doing this in MS Access? I have seen how it is done in SQL but how do I incorporate this in Access. Please let me know and thank you in advance

[KnooKie]

Just prior to deleting the donor can you run an Update query to update the donorID (of the one you wish to delete) in the Donations table to DON1.

You will need to have a Donor in the donor table with a donorID of DON1 in order for the 1 to many relationship to work and also the update.

Hope that makes sense ?

[bertonciniluca]

If you delete a Donor form a datasheet (Browsing) view of Donor table, you cannot update the Donations table. (Access doesn't have Store Procedure).
So best way to do that is to intercept the delete event inside a form showing Donor records; in the event procedure you can run an update query (to change the Donor_ID to "DON1") then you can delete the Donor Record.
Let me know if this solve your problem.

CIAO