Append key violation generates no error. (1 Viewer)

[The Stoat]

Hi all,

I'm trying to put some error handling events in my DB mainly to deal with key violations when appending records to tables.
I've put this in a module which is called when i need to run a query.

Option Compare Database
Option Explicit
Public Function RunQry(QryName As String)' Pass in name of saved query

On Error GoTo ErrHandle
DoCmd.SetWarnings False

    DoCmd.OpenQuery (QryName)

DoCmd.SetWarnings True

Exit Function

ErrHandle:

HandleErrors (Val(Err.Number)) 'If an error is generated it will call this function

End Function

However when i do an append query that i know generates a key violation i am not getting an error generated. I would like to tell the user that they have already appended the record(s). If i remove the DoCmd.SetWarnings I get all of the access default append messages which i don't want.

Any clues?

Cheers

TS

 

[Dugantrain]

I don't believe that you're going to get it to work this way as you're SetWarnings False is going to suppress the errors that you're trying to capture. You're going to need to rethink your approach; my suggestion would be to use a query to compare key values among the two tables and, if duplicates are found, to raise a flag telling the User. This query would be easy to write, you could just inner join on the key fields from the two tables and, if the RecordCount is greater than 0, then you have dupes.

 

[The Stoat]

Hi Dugantrain,

That seems like a reasonable idea. It is a shame that this doesn't work as it is far more generic i.e. re-useable, than writing a query. Oh well, thanks for the help it's greatly appreciated.

TS