Manage append query key violations?

[Angela]

I have a database where the user can add employees to a course list. They
select an employee or group of employees and then click Add, and then these
employees are added to a table with an append query. I anticipate that
occasionally the user will attempt to append the same employee more than
once, which will result in an "append query key violation" which I would like
to manage with code.

It appears that the append query error generates an error message before the
code reaches the error handler, so even tho I've put something in the error
handler to ignore this particular error, the user still gets the append query
key violation error msg. Is there a way around this?

I apologize if this question has been answered before. I searched and found
a gizillion responses to "append query key violation" and tried to look thru
most of them but didn't find an answer to my question.

 

[Piet Linden]

I have a database where the user can add employees to a course list.  They
select an employee or group of employees and then click Add, and then these
employees are added to a table with an append query.  I anticipate that
occasionally the user will attempt to append the same employee more than
once, which will result in an "append query key violation" which I would like
to manage with code.

It appears that the append query error generates an error message before the
code reaches the error handler, so even tho I've put something in the error
handler to ignore this particular error, the user still gets the append query
key violation error msg.  Is there a way around this?

I apologize if this question has been answered before.  I searched and found
a gizillion responses to "append query key violation" and tried to look thru
most of them but didn't find an answer to my question.

Use the Find Unmatched query wizard to return the records that are not
already in the table and only append them....

 

[Angela]

I can't really use this unmatched query wizard since the records being added
to the table are not coming from another table but a combination of tables
and information entered onto a form by the user.

I guess, however, that it's better to avoid the error entirely rather than
try to "manage it" after it's generated so will insert some programming to
check the table for each person before the new record is added.

 

[Tom van Stiphout]

On Wed, 10 Jun 2009 16:05:01 -0700, Angela

Maybe you ran the append query incorrectly. This is how I would do it,
and this code WILL correctly catch the error:

private sub cmdAdd()
on error goto ErrHandler
dim qd as dao.querydef
set qd=currentdb.querydefs("myAppendQuery")
'set any parameters as needed (qd.Parameters("myParamName").Value =
myValue
qd.execute dbFailOnError
qd.close
ExitHandler:
set qd=nothing
exit sub
ErrHandler:
if err.Number=??? then 'TODO: Look up the number for "append query
key violation"
Msgbox "Yo! We already have this employee!", vbCritical
end if
resume ExitHandler
end sub
(of course you change myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[Angela]

Tom, can you use that "dbFailOnError" with RunSQL, or is there something
comparable? I'm trying to do it this way:

strAdd = "INSERT INTO tblEmpTemp yadda yadda yadda....."
DoCmd.RunSQL (strAdd)