Can I replace Duplicate Key Error message ? (1 Viewer)


Registered User.
Local time
Today, 14:43
Jan 13, 2008
I have recently changed the structure of my table to not allow duplicates, using a combined primary key.
So in one respect i am happy now to stop the duplication, but how can i change the error message so it is more user friendly.

Access front End - with Error Logging - but it does not log this error.

SQL server back end.

Function LogError(ByVal iErrNumber As Integer, ByVal strErrDescription As String, strCallingProc As String, Job_ID As String)
On Error GoTo Err_LogError
    ' Purpose:   Generic error handler.
    '            Logs errors to table "tLogError".
    ' Arguments: iErrNumber        - value of Err
    '            strErrDescription - value of Error$
    '            strCallingProc    - name of sub|function that generated the error.
    ' Author:    Allen Browne,, June 1997.

    Dim NL As String * 2                ' New Line
    Dim sMsg As String                  ' String for display in MsgBox
    Dim db As Database                  ' Current database
    Dim rst As Recordset                ' The tLogError table
    Dim Strql As String
    sMsg = "Error " & iErrNumber & ": " & strErrDescription
If iErrNumber = 6 Then
MsgBox "Please contact Gareth Tucker giving brief description of what you were doing when you received the error!"
End If

'If iErrNumber <> 3167 Or iErrNumber <> 1004 Then
'    MsgBox sMsg, 32, strCallingProc
'End If

If iErrNumber = 2114 Then
MsgBox sMsg, 32, strCallingProc
End If

    ''''''debug.print Err.Number

    Set db = CurrentDb()
    'Set Query = CurrentDb.QueryDefs("QryQCLogError")
       Set Query = CurrentDb.QueryDefs("QryQCLogError")
    Set rst = Query.OpenRecordset(dbOpenDynaset, dbSeeChanges)

        rst![ErrNumber] = iErrNumber
        rst![ErrDescription] = Left$(strErrDescription, 255)
        rst![ErrDate] = Now
        rst![CallingProc] = strCallingProc
        rst![UserName] = GetLongName
        rst![Parameters] = Job_ID
'End If

    Exit Function


If iErrNumber = 6 Then
MsgBox "Please contact Admin giving brief description of what you were doing when you received the error!"
End If
    sMsg = "An unexpected sitPRODion arose in your program." & NL
    sMsg = sMsg & "Please write down the following details:" & NL & NL
    sMsg = sMsg & "Calling Proc: " & strCallingProc & NL
    sMsg = sMsg & "Error Number " & iErrNumber & NL & strErrDescription & NL & NL
    sMsg = sMsg & "Unable to record because Error " & Err & NL & Error$
    MsgBox sMsg, 16, "LogError()"
    Resume Exit_LogError
End Function



..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:43
May 7, 2009
it is called Unhandled error, Access cannot catch that one.


Well-known member
Local time
Today, 23:43
Feb 7, 2020
I find it more convincing not to make errors instead of trying to produce nice error messages.
Principle: First check, then act specifically.
Set rs = db.Openrecordset("SELECT * FROM QryX WHERE PK = " & NewValue)
If rs.EOF Then
   ' you can add
   ' warning notice
End If
Last edited:


Super Moderator
Staff member
Local time
Today, 22:43
Feb 19, 2013
I assume the error being generated is when adding to a data table and not adding to your error log table.

you can trap the potential error before inserting using sql or the dcount function in the form before update event (or when me.dirty is used in say a save button) to see if the composite key already exists - if it does, provide your own message/values to populate the error log

You can also try the form onError event if users are not saving a record by clicking a button but simply going to another record or closing the form

Why open a recordset and add? why not just use an insert query? - then you can use dbFailOnError as a parameter to your currentdb.execute
Not tested with a Sql Serve BE but might give you a more informative error value.
Not relevant to the error, just a comment on the code

Other issues
- you do not appear to be using Option Explicit since 'Query' is not declared, so all compilation errors will not appear until runtime
- 'Query' is a reserved word and should not be used for an object name

Just as a side note, unless you actually require a composite primary key, better to create a separate composite key.

Also - the code you are using is 27 years old, if you look around you may well find something more current.


Lifelong Learner
Local time
Today, 14:43
Mar 14, 2017
Check for dupes prior to trying to enter a dupe

Users who are viewing this thread

Top Bottom