If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
inserting new record, duplication error
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 |
#2
|
|||
|
|||
inserting new record, duplication error
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 |
#3
|
|||
|
|||
inserting new record, duplication error
Thanks a loooooooot Mr. Hargis
"Klatuu" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|