Preventing duplicate data entry

How to prevent duplicate data entry in different records.

Q:  Nigel Heremaia wants to prevent users from entering the same data in the FirstName and LastName fields in different records.  He is using this code, but it isn’t working:

Private Sub Form_BeforeUpdate(Cancel As Integer)


Dim FirstName

Dim LastName

Dim FullName


FirstName = DLookup(“[FirstName]“, “tblClients”, “[FirstName] ='” _

   & Me!FirstName & “‘”)

LastName = DLookup(“[LastName]“, “tblClients”, “[LastName] ='” _

   & Me!LastName & “‘”)

FullName = FirstName & LastName

    If FullName <> 0 Then

        MsgBox “Client has already been entered in the database.”

        Cancel = True

    End If

End Sub


A:  The problem here is that you are comparing FullName to 0, and the expression FullName <> 0 will always evaluate to True, because whatever value is in the FullName variable, it is a String value, not a numeric value.  To clarify this, declare your variables as String, and wrap the FirstName and LastName variables with Nz() – this converts Nulls in text fields to zero-length strings.  Then you can check whether Full Name <> “” (instead of 0).  That should work the way you intend.