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.
