A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

inserting new record, duplication error



 
 
Thread Tools Display Modes
  #1  
Old April 21st, 2008, 11:07 AM posted to microsoft.public.access.forms
hikaru
external usenet poster
 
Posts: 17
Default 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  
Old April 21st, 2008, 02:58 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default 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  
Old April 22nd, 2008, 04:57 AM posted to microsoft.public.access.forms
hikaru
external usenet poster
 
Posts: 17
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.