UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Closed TopicStart new topic
> Deleting records or suppress default message    
 
   
Alan_G
post Dec 7 2006, 08:25 PM
Post#1


Remembered as Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,533
Joined: 12-January 03
From: Devon UK


A question that seems to be asked many times is how do I stop the 'You are about to delete...etc' message box that is displayed by default (depending on your settings in Tools-->Options-->Edit/Find tab-->Confirm settings) when you are running a delete action query. I hope the following may offer a different approach - or a solution.
An alternative to actually deleting data
The problem with actually deleting records from your lovingly crafted database is that once you've clicked on your 'Yes, Please delete my records' button, your records are gone forever. There's no going back, no saying 'I didn't really mean it', no point in praying and you probably wont earn many brownie points. It's even worse if you have related records and Enforce Referential Integrity with Cascade Delete Related Records ticked in the Relationship window as you potentially could be deleting 1000's of records in one fell swoop!!!. Unless you have a recent backup there is no way of getting the data back. Fortunately, there is another way. It takes a little bit more work on the part of you the developer, but heh - that's what we're here for.
Instead of really deleting the records, just add an extra Yes/No (Boolean) field to your tables. Call it something like NotCurrent and set the Default Value to False. Now when you want to delete a record, instead of actually deleting it you merely UPDATE your new field to True which is flagging the record as having been 'deleted'. It's now a simple task to filter your recordsets using queries to only include records that have your NotCurrent field equal to False - hence current records that haven't been previously 'pseudo' deleted. You also have the added benefit of viewing - or reinstating if you wish by updating your NotCurrent field to False - previously 'deleted' records by returning a recordset with your NotCurrent field equal to True.
You can bet your bottom dollar that at some point in the future, somebody is going to want to compare/query/analyse/report on historical data which is not a problem as long as the data is actually still available - and you can say with a smile on your face - 'No problem'
Really deleting records without the confirmation
If, on the other hand you really do want to delete your records without having the 'You are about to' etc message box, then the general syntax is
If Msgbox ("You are about to delete data, do you want to continue?", vbYesNo + vbCritical,"Confirm Delete")
= vbNo Then
Exit Sub
Else
CurrentDb.Execute "DELETE MyID FROM MyTable WHERE MyID = " & Me.MyIDControl, dbFailOnError
End If
Use it advisably though as there's no going back, particularly when referential integrity/cascade deletes applies!!!
Hope there's food for thought.......
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 02:25 PM