It looks like it is trying to count the number of records in which ID is not
null and ContactName is the same as ContactName in the current record. The
Replace function is for names containing an apostrophe, which would cause
the string to truncate and the expression to fail.
You could avoid having to test ID for null by using it in place of the
asterisk:
=IIf(DCount("[ID]","[Contacts Extended]",
"[Contact Name] = '" & Replace(Nz([Contact Name]),"'","''") & "'") > 0,
"Possible Duplicate","")
DCount will ignore null values in the ID field when the expression is
constructed in this way.
Rather than place this code in an unbound text box it may be better to use a
message box in the After Update event of the control bound to [Contact
Name]:
Private Sub txtContactName_AfterUpdate()
Dim blnDup as Boolean
' True/False test of whether the possible duplicate exists
blnDup = DCount("[ID]","[Contacts Extended]", _
"[Contact Name] = '" & Replace(Nz([Contact Name]),"'","''") & "'") >
0
If blnDup = True Then
MsgBox "Possible Duplicate"
End If
End Sub
The advantage is that you do not need to run the domain function except when
updating the name field. Domain functions such as DCount can slow things
down.
You could write it this way:
Private Sub txtContactName_AfterUpdate()
If DCount("[ID]","[Contacts Extended]", _
"[Contact Name] = '" & Replace(Nz([Contact Name]),"'","''") & "'") >
0 Then
MsgBox "Possible Duplicate"
End If
End Sub
This does not use the variable blnDup. Using the variable may make the code
easier to read, but it is not required.
It looks as if there is one field for the full name. Best practice is for
first and last names to be in separate fields.
If there are no quotes in the names ("Big Al" and such) you can replace the
apostrophe with a pair of quote marks, and avoid having to use Replace
(spaces added for clarity):
blnDup = DCount("[ID]","[Contacts Extended]", _
"[Contact Name] = " " " & [Contact Name] & " " " ") > 0
Note that the underscore may be used as a line continuation character in VBA
code, but in a text box Control Source expression or a query expression you
cannot have line breaks. IN those cases the expression needs to be on one
line.
