Hi,

I am facing a problem while preventing duplicate value in field "CID". As per below code, it response good when any duplicate value enters in CID field, but it also responses the same msg while entering new CID value.

Please help me to find the problem. thanks in advance.
I'm using Access2003.
================================================== ========
Private Sub CID_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
SID = Nz(Me.Cid.Value, "")

stLinkCriteria = "Cid = " & "'" & SID & "'"

If DCount("Cid", "tblCustomerSale", stLinkCriteria) > 0 Then
MsgBox "Duplicate Customer ID.", , "Error"
Cancel = True
End If
End Sub
================================================== =======

Are you absolutely sure that you're entering a new CID number? Have you checked the Table to varify that the CID you're going to enter is, in fact, new?

I ask because I created a db with your Field Name, copied and pasted your code in the Code Module of the Form, and, for me, also using 2003, it works as it should, only popping the Messagebox when I attempt to enter an existing CID.

Linq ;0)>

Thanks for fast reply dear, but I' facing the same problem. I'm getting msgbox in both conditions, whether I enter new CID value or existing Value.

Many Thanks,
Anuj

Ditto. I tried too and, as expected, I got the same result as Missinglinq.

What about changing the dcount >1

As you are checking if more than one

Finally, I got the solution. It is working in both ways. It is a below:
=================
Private Sub CID_BeforeUpdate(Cancel As Integer)
Dim stLinkCriteria As String

If Me.NewRecord Then
stLinkCriteria = "CID = """ & Me.CID & """"
Else
stLinkCriteria = "CID = """ & Me.CID & """"
End If

If DCount("*", "tblCustomerSale", stLinkCriteria) > 0 Then
MsgBox "Duplicate Customer ID.", vbExclamation, "Error"
Cancel = True
End If
End Sub
===================

Thanks a lot for your fast replies.

This makes little sense. The way the criteria is assembled is the same whether Me.NewRecord is True or False:
is the same as:
and the test is useless.