Duplicate data message for a data entry form

[JulieMarie]

I created a data input form to enter new contracts that are tied to two tables. The tables update after the data is saved in the form. I do not want users to enter in duplicate contract numbers. I would like to create a message to let users know if the data they entered is duplicate. I do not need to go to the duplicate record.

My code shows everything I enter to be a duplicate, even when I know that the data I am entering is not a duplicate. How can I only get the message to appear if the data is truly a duplicate entry?

[pbaldy]

Your field name doesn't have underscores, it has inadvisable spaces:

Answer = DLookup("[Contract No]", "Contract", "[Contract No] = '" & Me.Contract_No & "'")

[JulieMarie]

I corrected it but it still gives me the message for contract numbers I know do not exist. If you look at the master contract log query it will show all of my contracts. So I entered C1234567 and it says its a duplicate, but I know this is not true. I only want the message to appear if it is truly a duplicate record. I also tried the below code with the same results. I don't know why it is saying every record is a duplicate.

Private Sub Contract_No_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Contract_No.Value
stLinkCriteria = "[Contract_No]=" & "'" & SID & "'"
'Check Contract table for duplicate ContractNo
If DCount("Contract_No", "Contract", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Contract Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
End If
Set rsc = Nothing
End Sub

[pbaldy]

What did you correct? Your code still has underscores. When I used the code I posted, I was able to enter new data.

[JulieMarie]

It works! In case you haven't heard it lately - YOU ARE A GENIUS!!!!
Thank you so much for your help!

[pbaldy]

LOL! I've heard genius, but I've also heard dork, idiot, moron, and a few unrepeatable terms!

Happy to help, and welcome to the site by the way!