How To Ignore Duplicate Key Error Message In Access?

[DAPOOLE]

Is it possible for me to bypass the error message #3604 'duplicate key was ignored'. When this error message is thrown it stops processing the subsequent stored procedures so just want it by-passed and not displayed to screen.

TIA

 

[vbaInet]

For your reading pleasure:

http://www.cpearson.com/excel/ErrorHandling.htm

 

[PaulO]

Is this one query in a multi-query Macro? If so on the first line of the Macro you can use the Setwarnings command and set it to NO

At the end of the Macro again add the Setwarnings command and set it to YES

This change will effectively skip interactive user messages/warnings.

 

[PaulO]

However I'd recommend first that you look at the Table you are quering as there must be good reason for the original error message i.e. you have set a field's properties, for good reason (?), to not allow duplicates so if you do want duplicates you would need to change this property. My original posting (above) will enable you to continue the Macro but NOT complete the affected query in which case the full purpose of the Macro will not be completed.

 

[DAPOOLE]

Is this one query in a multi-query Macro? If so on the first line of the Macro you can use the Setwarnings command and set it to NO

At the end of the Macro again add the Setwarnings command and set it to YES

This change will effectively skip interactive user messages/warnings.

Basically the error is thrown by the mail_proc_beta stored procedure below which is a package of stored procedures. Any subsequent procedures are then obivously not run because of the error.

The error CAN be safely ignored I just need to know how.



Private Sub Command49_Click()
On Error GoTo Err_Command49_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSql As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryStoredProcNoRecReturn")


Select Case Frame24.Value
Case 1
strSql = "exec mail_proc_beta " & Me.Combo10
Case 2
strSql = "exec mail_email_beta " & Me.Combo10
Case 3
strSql = "exec mail_sms_beta " & Me.Combo10
End Select



qdf.SQL = strSql
DoCmd.OpenQuery "qryStoredProcNoRecReturn"

Set db = Nothing
Set qdf = Nothing

Exit_Command49_Click:
Exit Sub

Err_Command49_Click:
MsgBox Err.Description
Resume Exit_Command49_Click

End Sub

 

[PaulO]

However I'd recommend first that you look at the Table you are quering as there must be good reason for the original error message i.e. you have set a field's properties, for good reason (?), to not allow duplicates so if you do want duplicates you would need to change this property. My original posting (above) will enable you to continue the Macro but NOT complete the affected query in which case the full purpose of the Macro will not be completed.

Did you look at the table as you could, potentially, resolve the error there?

 

[DAPOOLE]

Did you look at the table as you could, potentially, resolve the error there?

No, the error is unresolvable as it is thrown as a warning as part of a deduplication process hence the reason why I'm wanting it to be ignored.

 

[vbaInet]

Is the error displayed in the message box that you directed it to?

 

[DAPOOLE]

Is the error displayed in the message box that you directed it to?

Yes it's displayed in a message box.

 

[vbaInet]

That link I provided explained the different error labels, including what I'm about to give you.

Replace:

On Error GoTo Err_Command49_Click

With:

On Error Resume Next

Remove all of this:

Exit_Command49_Click:
    Exit Sub

Err_Command49_Click:
    MsgBox Err.Description
    Resume Exit_Command49_Click

I hope you know the consequences. All that will do is mask the error and move on to the next. You haven't dealt with what's causing the error.

 

[DAPOOLE]

That link I provided explained the different error labels, including what I'm about to give you.

Replace:

On Error GoTo Err_Command49_Click

With:

On Error Resume Next

Remove all of this:

Exit_Command49_Click:
    Exit Sub

Err_Command49_Click:
    MsgBox Err.Description
    Resume Exit_Command49_Click

I hope you know the consequences. All that will do is mask the error and move on to the next. You haven't dealt with what's causing the error.

Okay thanks I am at home now but will try this at work on Monday. As stated it is okay to ignore this error as the stored procedure has been well tested I just don't want Access throwing it.