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

[tucker61]

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, allen@allenbrowne.com, 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
    'Waitoff
    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
Else
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.AddNew
        rst![ErrNumber] = iErrNumber
        rst![ErrDescription] = Left$(strErrDescription, 255)
        rst![ErrDate] = Now
        rst![CallingProc] = strCallingProc
        rst![UserName] = GetLongName
        rst![Parameters] = Job_ID
    rst.Update
    rst.Close
'End If


Exit_LogError:
    Exit Function

Err_LogError:

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

 

[arnelgp]

it is called Unhandled error, Access cannot catch that one.

 

[ebs17]

I find it more convincing not to make errors instead of trying to produce nice error messages.
Principle: First check, then act specifically.
Approach:

Set rs = db.Openrecordset("SELECT * FROM QryX WHERE PK = " & NewValue)
If rs.EOF Then
   ' you can add
Else
   ' warning notice
End If

 

[CJ_London]

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.

 

[Isaac]

Check for dupes prior to trying to enter a dupe

 

[jdraw]

Here's a sample where poster needs method for finding and bypassing a potential duplicate in a unique composite index.
You might be able to adapt to your needs.