append query error

[MES via AccessMonster.com]

I have an append query that I run from a form. Employee information
(including badge #, last name, first name, etc.) is included in the form for
appending to the table. The primary key of the table is badge #, so if
someone were to put in a duplicate badge #, they get the 'file can't append
all records in the append query' message. I then get the VB run-time error
3059.

I would like to avoid this, and instead of the error messages have a message
box come up and tell the user that the badge # they input is a duplicate and
they must redo and/or choose another number. So it would be putting the
focus back on the data entry form and clearing the duplicate badge # field.

How can this be done?

Any help would be greatly appreciated. Thanks in advance.

 

[Steve Schapel]

MES,

If DCount("*","YourTable","[Badge #]=" & Me.Badge) > 0 Then
MsgBox "Badge # already exists in the table"
Else
< your append code >
End if

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field.