DAO Error Catching not working

[tt1611]

Good day folks

My access program connected to SQL Server has a table tbl1 has a composite key (col1 and col2). Naturally these two will not take duplicates. I have code that is set to trap duplicate entry errors as below.

Private Sub cmdsave_Click()
On Error GoTo err_duplicate
 
'....some code
 
err_duplicate:
'If Err.Number = 3146 Then
'MsgBox "Error Number" & " " & Err.Number & vbNewLine & " " & "This record contains errors (possible duplicate name/serial)", vbExclamation + vbOKOnly, "Error"
'Else
Dim errN As DAO.Error
If Errors.Count > 1 Then
For Each errN In DAO.Errors
If errN.Number = 3043 Or errN.Number = 3146 Or errN.Number = 3151 Or errN.Number = 11004 Then
MsgBox "Error Number" & errN.Number & vbNewLine & "This name or serial is already being used. Please create a new PC record", vbExclamation + vbOKOnly, "Error"
End If
Next errN
'End If
End If
End Sub

I commented out the runtime error number 3146 because that works fine. However this particular error is being thrown by SQL Server and I was hoping that my DAO Error trap should catch that. However it doesnt and I am unable to fully debug this as the save option is not available in design mode.
The idea is that if someone tries to enter a duplicate name in either col1 or col2 SQL server throws an ODBC error 3146 or 3627. Either way this subsequently causes a runtime error in access. I am able to trap the runtime error but not the DAO Error which my code appears to be attempting to trap.

Do you guys have any idea as to what I may be doing wrong?


Update - I also attached a screenshot of the exact error including error codes that we thrown by Access as a runtime error and SQL Server as an ODBC call error for further clarification when the save button is pressed.

Thanks

 

[Uncle Gizmo]

Do you guys have any idea as to what I may be doing wrong?

Yes, you’re not supposed to use error numbers in this way, (we all do!). The correct method would be to first inspect the table to see if the values you are going to post to it exist, if they do then inform the user that they cannot use those particular values, or take what other action is necessary.

I imagine what you need to do first is to write an SQL statement that checks to see if the values you want to add are there or not, if they are then take the necessary action.

 

[tt1611]

Thanks for your response

Not following what you mean by..

Yes, you’re not supposed to use error numbers in this way, (we all do!).

I know I do have the option of running the data validation in the code on an after update event but I also wanted the option of trapping it as an error when the user tried to save the record.

What are your validations against this method of coding.

 

[vbaInet]

I believe that's a Form Error, so you should be able to trap it in the Form's Error event.

 

[tt1611]

It is a form error and like I had mentioned earlier, I am able to trap the application side (see attachment) as runtime error 3146. I am having a problem specifically trapping the DAO Error. Access only throws a runtime 3146 which could mean anything. I need to error to trap the particular duplicate problem which is in fact error 2627 (again see attachment) for which I am using the DAO.Error.

 

[vbaInet]

But you are trapping it in the wrong event. Which was why I mentioned you need to trap it in the form's ON ERROR event. Try it there and see the outcome.

 

[tt1611]

VBA
Thanks for getting back..and thanks for your patience. Just so we are clear I am showing you what I have for a) the save event where I have commented out all my error trapping and b) on error event where I am trying to specifically trap the DAO Error only.

The On Error event to my knowledge only traps runtime errors (correct me if I'm wrong). When I click the save button following entry of a dup, nothing happens (no error messages or nothing. If following my changes I try to close the form being a bound form, then the On Error event fires picking up the runtime error 3146 - (saw this in the watch window)..so once again, seeing that error 3146 could be anything, I dont want to trap this error thereby causing another 3146 generated for another reason outside of duplicate to display the wrong error. I hope this is somewhat clear men.

Please see code below

Private Sub cmdsave_Click()
'On Error GoTo err_duplicate
If Me.txtserial = "" Or Me.cmbtype = "" Or Me.cmbPPC = "" Then
MsgBox "Device Serial/Type/Name must be completed", vbOKOnly + vbExclamation, "Error"
Else
    If MsgBox("Are you sure you wish to save these changes", vbYesNo + vbExclamation, "PIMA") = vbYes Then
     DoCmd.RunCommand acCmdSaveRecord
     btnsave = True
     Form_NetDevice_Sub.Requery
     MsgBox "Device details saved successfully", vbOKOnly + vbInformation, "PIMA"
   
    DoCmd.Close acForm, "netDevicesMain"
       
        
    Else
    Cancel = True
    End If
End If
'err_duplicate:
'If Err.Number = 3146 Then
'MsgBox "Error Number" & " " & Err.Number & vbNewLine & " " & "This record contains errors (possible duplicate name/serial)", vbExclamation + vbOKOnly, "Error"
'Else
'Dim errN As DAO.Error
'If Errors.Count > 0 Then
'For Each errN In DAO.Errors
'If errN.Number = 2627 Then
'MsgBox "Error Number" & errN.Number & vbNewLine & "This name or serial is already being used. Please create a new PC record", vbExclamation + vbOKOnly, "Error"
'End If
'Next errN
'End If
'End If
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim errN As DAO.Error
If Errors.Count > 0 Then
For Each errN In DAO.Errors
If errN.Number = 2627 Then
MsgBox "Error Number" & errN.Number & vbNewLine & "This name or serial is already being used. Please create a new PC record", vbExclamation + vbOKOnly, "Error"
End If
Next errN
End If
End Sub

Clicking the save button, nothing happens on the form...because it is not understanding (or trapping the DAO).
on form close, then the on error event fires

 

[vbaInet]

Yes, it's for Runtime errors but since your form is bound it should also trap it too. With this event you use the DataErr parameter. It's the first argument of the form's On Error event. I will give you an example:

Private Sub Form_Error([COLOR=blue]DataErr[/COLOR] As Integer, Response As Integer)
    MsgBox "Form Error: " & [COLOR=blue]DataErr
[/COLOR]End Sub

Just see if it the message box comes up with the error number.

In any case, let's go back to the code in your first post, try this (and don't forget to comment out the form's error event when you try this):

Private Sub cmdsave_Click()
On Error GoTo err_duplicate
     
    '....some code
exit_err_duplicate:
    Exit Sub
    
err_duplicate:
    Dim errN As DAO.Error
    
    For Each errN In DAO.Errors
        Select Case errN.Number
            Case 3043, 3146, 3151, 11004
                MsgBox "Error Number" & errN.Number & vbNewLine & _
                       "This name or serial is already being used. Please create a new PC record", _
                       vbExclamation + vbOKOnly, "Error"
                Resume exit_err_duplicate
            Case Else
                ' do nothing
        End Select
    Next errN
    MsgBox "Error Number: " & Err.Number, vbExclamation + vbOKOnly, "Error"
    
    Resume exit_err_duplicate
End Sub