How to Delete "Properly"

[Ollie_3670]

I have a Manage Staff form. This form allows you to view staff and edit them.

However, If I want to delete a member of staff who is linked to another table, (e.g. 1 staff member can assess MANY Student applications) it will obviously blurt out a rather helpful, but annoying message.

What is the standard work around for this? Also, is there anyway I can edit the error message?

Cheers!

 

[McSwifty]

Hi Ollie,

I had a similar problem recently with a db I have created for work to keep track of mistakes made in a warehouse operation.

Instead of deleteing the staff, I have check box for 'resigned'. The queries then look up all records where 'resigned' = 0.

This way the records are all there and and linked records stay in it for stats tracking purposes.
If you do want to remove the staff member and all related fields, you can try to have referential integrity in you relationship turned on (suggested anyway) and use the delete cascaded records option as well.
I don't use this option, as I am afraid to delete stuff by accidentally having somone click on a button or they delete a record and wipe out huge amounts of information.

You can work around another way though.

Have two delete queries - remember to have a specific field targeted, which is why I use the 'resigned' check box as this will only be true for the ones you want to remove.

One to delete the related records in the other table
The other to delete the actual record.
Remember - the queries need to be run in the order above to ensure when you delete the staff member the related records are already gone, to avoid the error message coming up.

Use
docmd.setwarnings false/true
tos top the warnings about removing records, once you are comfortable the queires will do as expected.

Hope this helps and I haven't go the wrong idea.

Kev

 

[Galaxiom]

Rather than actually remove a record it is generally better to flag the record as "deleted". Especially if it is from a table that is never going to become huge.

The queries are modified to ignore these records unless a switch is used to show them.

Deleted records can then be easily recovered in the case of an error.

 

[gemma-the-husky]

but the "unhelpful" message you are getting is probably far from unhelpful

if you have enforced relational integrity, access stops you deleting things that would cause data "orphans"

so lets say you have a teacher, and he is linked to a number of classes.

if you try to delete the teacher, you get a "helpful" (ie not unhelpful) message that you cant delete the teacher because he is already used.

Now you can either

a) use the form error event to intercept and change the error message to something more informative
b) use cascading deletes to delete the linked records as well
c) use the above idea of a "resigned" flag - but you may then still need to resolve the linked records

or even
d) rethink your database
e) test the deletion BEFORE you press delete to see if it will cause any problems

and probably several other things as well/instead

 

[Ollie_3670]

Thanks guys, adding a "deleted" or "retired" field is a great way around the problem, I really don't foresee the database breaching 100records so size isn't an issue! Thumbs up!


---

but the "unhelpful" message you are getting is probably far from unhelpful

...blurt out a rather helpful ...

I did understand this, my tone probably masked my words a little, however nicely described in the event of someone else facing the same problem without the background understanding! Two thumbs up, double result!