inserting new record, duplication error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hikaru - 21 Apr 2008 11:07 GMT

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

Reply to this Message

Klatuu - 21 Apr 2008 14:58 GMT

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

Signature

Dave Hargis, Microsoft Access MVP

> hi all,
> I am trying to insert a new record to the table, but if the Contract_No
[quoted text clipped - 18 lines]
>
> End Sub

Reply to this Message

hikaru - 22 Apr 2008 04:57 GMT

Thanks a loooooooot Mr. Hargis

> Check to see if the record exists in the table before trying to insert it.
> You can do that using the DLookup function.
[quoted text clipped - 36 lines]
> >
> > End Sub

Reply to this Message