• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1626
  • Last Modified:

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
0
Radhs74
Asked:
Radhs74
3 Solutions
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
Radhs74Author Commented:
thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now