Getting ODBC Call failed error and unique constraint violated

Can anyone please help as I am getting the error in the attached file ... and below is the code for reference. I have changed the columnnames and tablenames so please ignore that..
can anyone let me know what the issue is ...thanks for your time

ODBC "call failed"
[Microsoft][ODBC Sql Server Driver] [SQL Server] Violation of Unique Constraint "xxxx". Cannot insert duplicate key in object (tbldetails) (#2627) [Microsoft][ODBC Sql Server Driver] [SQL Server] The statement has been terminated(#3621)


Private Sub Add_Click()
On Error GoTo Err_msg:
    strSysUserdt = Trim(UName())
    Dim strSQLGroup As String
    Dim rstConn As ADODB.Recordset
   
    'Select Statement to get the my group
    strSQLGroup = _
    " SELECT tblS.UName, tblS.SUserName, tblS.Cust, tblU.hGrp " & _
    " FROM tblS INNER JOIN tblU ON tblS.UserName = tblU.UName " & _
    " WHERE ((tblS.SUserName)= '" & strSysUserdt & "')"
    'Open Connection for my group
    Set rstConn = New ADODB.Recordset
    rstConn.Open strSQLGroup, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText    
       
    Select Case Me.lhStatus.Value
    Case "is not closed"
     If (rstConn!fcGroup = "my") Then
        Me.sfrmDetails.Enabled = True
        Forms!frmSouthC!sbfrmDetails.Form!ldP.SetFocus
        Else
        MsgBox "Your are not authorized to add any records", vbOKOnly, "No Privs!"
        DoCmd.CancelEvent
    End If
    Case "Closed"
    MsgBox "You can not add  records, GSB is closed", vbOKOnly, "GSB Closed"
    DoCmd.CancelEvent
    Case Else
    MsgBox "Invalid Status", vbInformation, "Error"        
    End Select
    rstConn.close
Err_msg:
  End Sub
Radhs74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BodestoneCommented:
It does not look like that is the code where the error is being generated since there are no insert statements.
Chances are there is a field on the form that is linked to the primary key of tbldetails that is not properly set as autoinsert,or another foetm item associated oth a DB field that has a unique constraint.

You can ciew constraints in SSMS or EM (2005 or 2k) by expanding the above table and looking at the constraints.

Anything that is a primary key or a uniqye constraint should either be an auto increment field at the fron end (if It is an identity column) or have a check on that button to do a query to see if that value exists in the database with a corrsponfing error to say so.
0
Radhs74Author Commented:
but I want to trap that error message and display a custom message in order for the user to understand that they have choosen or entered a duplicate value .. i want to override the system error message and keep my message there ..but when I wrote this part of the code to trap and display my own message ..it does show my message and then again the system error message .,.i am not sure how i can override this ... below is the code I have written in the change event of the qty field which is in the subform as i was getting the error there

Private Sub Qty_Change()

'Exit_ldQty:
    'On Error Resume Next
    'Exit Sub
   
Err_msg:
   'MsgBox Err.Number & ":" & Err.Description
   If Err.Number = 0 Or Err.Number = 3146 Or Err.Number = 20 Then
   MsgBox " You have violated a unique constraint .. please select another value"
   End If
   Resume Next  'Exit_ldQty
End Sub
0
Radhs74Author Commented:
can anyone help me with this as I have to complete this assignment asap
Would greatly appreciate your help

thanks
rds
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BodestoneCommented:
Sorry, I had lost track of this one. You might try turning off the checking before the query is run with:

Application.ErrorCheckingOptions.BackgroundChecking = False
0
Arthur_WoodCommented:
this code

Private Sub Qty_Change()

'Exit_ldQty:
    'On Error Resume  Next
    'Exit Sub
   
Err_msg:
   'MsgBox Err.Number &  ":" & Err.Description
   If Err.Number = 0 Or Err.Number = 3146  Or Err.Number = 20 Then
   MsgBox " You have violated a unique  constraint .. please select another value"
   End If
   Resume  Next  'Exit_ldQty
End Sub



does NOT actually TRAP en error.  In every case it will simply display your message (whether there was an error or not)

To trap the error, and only show your message in the case of that error:

Private Sub Qty_Change()

'Exit_ldQty:
    'On Error Resume  Next
    'Exit Sub
   
Err_msg:
   'MsgBox Err.Number &  ":" & Err.Description
   If Err.Number = 0 Or Err.Number = 3146  Or Err.Number = 20 Then
   MsgBox " You have violated a unique  constraint .. please select another value"
   End If
   Resume  Next  'Exit_ldQty
End Sub

AW
0
JR2003Commented:
How is that code generating a unique constrint violation? You can't get that error from a select. So it must be somewhere else where you are doing an insert or update.
0
BodestoneCommented:
That's that I said initially. It is probably happening when form data has been entered and the record changes thus auto submitting.

Having mnore of a think about this you want to put some code into the
BEFORE)UPDATE() event on the form that will perform a select statement for the table that the form is linked to checking if a record exists with the value entered into the relevant control on the form.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Radhs74Author Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.