Eliminate Duplicate Entries on form

[Eric]

I have recently adopted a database that tracks users and I need to prevent
duplicates from being entered into a form. I have tried using previous
threads, but I can't get them to work. I am aware of the ability to prevent
duplicates through the table, but the error message it gives would be quite
confusing to the users of this database. The name of the table I have is
"Names", the form is called "Names", and the field is "FullName". The
FullName field is invisible to the user and is a combination of the fields
"FirstName" and "LastName" using the after update event on the "FirstName"
field. I have been trying to use the Before Update event on the "FirstName"
field, but I can't get it to work. This is what I got so far:

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("FullName", "Names", "FullName = '" & Forms!Names!
FullName & "'")) Then
Cancel =True
MsgBox "..."
End If
End Sub

Can someone assist?

 

[tina]

well, you might try this instead, as

If DCount(1, "Names", "FullName = '" & Me!FullName & "'")<1 Then

but if it's only the *combination* of the first and last names that must be
unique, it might be better to run the validation code on the *form's*
BeforeUpdate event, which can also be cancelled.

I am aware of the ability to prevent
duplicates through the table, but the error message it gives would be quite
confusing to the users of this database.

you can trap the error code generated by violation of a unique index in the
table, and substitute the ugly system message with one of your own. it's an
alternative to the BeforeUpdate validation solution.

hth

 

[Eric via AccessMonster.com]

Using the forms Before Update worked...Thanks!!!

well, you might try this instead, as

If DCount(1, "Names", "FullName = '" & Me!FullName & "'")<1 Then

but if it's only the *combination* of the first and last names that must be
unique, it might be better to run the validation code on the *form's*
BeforeUpdate event, which can also be cancelled.

I am aware of the ability to prevent
duplicates through the table, but the error message it gives would be quite
confusing to the users of this database.

you can trap the error code generated by violation of a unique index in the
table, and substitute the ugly system message with one of your own. it's an
alternative to the BeforeUpdate validation solution.

hth

I have recently adopted a database that tracks users and I need to prevent
duplicates from being entered into a form. I have tried using previous

[quoted text clipped - 16 lines]

Can someone assist?

 

[tina]

you're welcome

Using the forms Before Update worked...Thanks!!!

well, you might try this instead, as

If DCount(1, "Names", "FullName = '" & Me!FullName & "'")<1 Then

but if it's only the *combination* of the first and last names that must be
unique, it might be better to run the validation code on the *form's*
BeforeUpdate event, which can also be cancelled.

I am aware of the ability to prevent
duplicates through the table, but the error message it gives would be quite
confusing to the users of this database.

you can trap the error code generated by violation of a unique index in the
table, and substitute the ugly system message with one of your own. it's an
alternative to the BeforeUpdate validation solution.

hth

I have recently adopted a database that tracks users and I need to prevent
duplicates from being entered into a form. I have tried using previous

[quoted text clipped - 16 lines]

Can someone assist?