Check to see if the record exists in the table before trying to insert it.
You can do that using the DLookup function.
Private Sub cmdNext_Click()
Dim contNo As String

contNo = UCase(Me.Contract_No)

If Not IsNull(DLookup("[Contract_No]", "Tbl_Contracts","[Contract_No] =
""" & contNo & """")) Then
MsgBox "Contract " & contNo & " Already exists", vbExclamation
Me.Undo
Else
DoCmd.RunSQL ("INSERT INTO Tbl_Contracts (Contract_No, EmpID) VALUES
('" & contNo & "'," & Me.EmpID & ")")
End If

End Sub
--
Dave Hargis, Microsoft Access MVP


"hikaru" wrote:

hi all,
I am trying to insert a new record to the table, but if the Contract_No
(primary Key) of the record i'm trying to insert was similar to another one
that is already in the table, I recieve an error msg.. how can I show a
msgbox "There was a contract with the same No., please choose another one!!"
when the same contract_no already exist,, and insert the new record if there
were no similar contract_no in table.. I hope I explained it well.. I need
your help, please.

thanks in advance.

here is my code:

Private Sub cmdNext_Click()
Dim contNo As String

contNo = UCase(Me.Contract_No)

DoCmd.RunSQL ("INSERT INTO Tbl_Contracts (Contract_No, EmpID) VALUES ('" &
contNo & "'," & Me.EmpID & ")")


End Sub