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.

--
Message posted via http://www.accessmonster.com

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.

--
Steve Schapel, Microsoft Access MVP

MES via AccessMonster.com wrote:
> 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.
>