Append Query - Trap Key violation error

[Jacqui3]

Hi,

I have an append query run by a macro. To prevent all the MS Access messages appearing I have Set Warnings off
BUT
then the user does not realise the information was not appended to the table as duplicate records where encountered.

What I would like to do is trap the Key violation error and send a message to the screen indicating that the records did not append.

How do I do this?

 

[tomschafer]

Instead of turning your warnings off - which turns off action notifications, as well as, errors. Try one of these -

SetOption "Confirm Action Queries", False

To turn action confirmations back on use

SetOption "Confirm Action Queries", True


OR

currentdb.Execute "Your SQL String"

Which by default does not display action confirmations but, will display index/key violations and all other errors.

Good luck!

 

[Jacqui3]

Hi,

Thanks for the help it worked beautifully.

Another question - is is possible to remove the Yes button from the error message? I do not want the user to append the records.

OR could I write my own error message?

Jacqui

 

[tomschafer]

Try using some error handling, Run This Example....

Sub ErrorExample()

On Error GoTo ErrorTrap

'Put Your Code In Here
Err.Raise 1

ErrorTrap:
MsgBox "There Was An Error!", vbOKOnly, "Records Not Appended"
Exit Sub

End Sub