Trapping Access messages

[Tony Williams]

When a user deletes a record from a table which has a link to another table
two messages appear.One that asks the user to confirm they want to delete
the record and another that involves the deleting of records in the linked
tables. is there any way to trap these messages to stop them appearing and
replace them with my own because the wording of the second message isn't
user friendly?
TIA
Tony Williams

 

[Wayne Morgan]

If you are working directly in the tables, I don't know of a way on an
individual basis. If you are using forms, this can be done. You could do the
delete in code and bypass the warnings.

There is a way to turn some warnings off for everything, but be careful when
doing this. The options are located in Tools|Options, Edit/Find tab under
Confirm. The advantage of turning off the warnings in code is that you can
also do some verification in code to make sure that an item should be done
and so the warnings may not be as necessary or you may have already supplied
your own warnings and so there is no need to duplicate it with Access's
warnings.

 

[Tony Williams]

Thanks Wayne I found the Tools/Options method but can you point me in the
direction of turning off the messages by code. Are there some guidance notes
somewhere. I am a newbie at VBA!
Tony

 

[Wayne Morgan]

There are a couple of methods. The older one is

DoCmd.SetWarnings False

Just remember to set it back to True again when you're done with what you're
doing. Another method is to use a different syntax

CurrentDb.Execute "qryMyActionQuery", dbFailOnError

This will execute an action query, such as an update query, append query, or
delete query, and will only give you a message if there is an error. You
could trap the error for the linked table and then delete those records in
code or pop-up a more user friendly message letting the user know that you
can't delete that record right now because there are records in the other
table that are using it. You could even pop-up a form at this point
displaying those records.

To get both of the message you are mentioning, I assume you have Cascade
Deletes turned on the Referential Integrity. If that is the case, running a
delete query with the CurrentDb.Execute... syntax will delete the desired
record and all its associated records without any warning at all. If you
want one, it'll be up to you to pop-up a Msgbox verifying the users intent
before actually running the delete query. Using a delete query for the
delete, you control which record(s) is deleted by the WHERE clause in the
query. If you want to only delete one record, you would use the unique id
field of the record in the WHERE clause.

 

[Tony Williams]

Thanks Wayne I'm going to try the second alternative
Tony