Dear Access Expert.

I wanted to know if it is possible to control the error message a user receives when they try to enter a duplicate value into a primary key field or a field with a non Duplicate index?

Or do you always take care of this yourself at the form level using the Before_Update event?

Thanks.

can you tell what is that error.

If what I am thinking is the same with my problem before, you can use docmd.setwarnings false before your codes starts. that will prevent the error message from popping up.

Hi Genesis,

I want the error to pop up but I want to customize the message just like a Validation Rule has Validation Text.

say what kind of customized message?

how about this:

http://www.access-programmers.co.uk/forums/showthread.php?t=86527

Instead of saying

" The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index,
or redefine the index to permit duplicate entries and try again."

I wanted it to say "This value is already in the table" as an example.

You can use;
DoCmd.SetWarnings = False

To turn off the inbuilt error messages, but be warned you will have to code for ALL error trapping and validation.

i think you can use the dlookup function and evaluate for existing records. that way you can customize you msgbox. still use the docmd.setwarnings false after that evaluation procedure.

use the forms error function to intercept the error.

it is strange that access doesnt give you the error numbers - but off hand that error is something like 3022

instead of the err object, you will see dataerr in the header for the event, and you need to use accesserror to investigate this error number

try this sort of thing

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 10505:
'intercepted query fire
Case 8519:
'intercepted delete
Case 3022:
Call MsgBox("Sorry: You have entered a duplicate item. Please check the data and try again. You can press <Esc> to cancel the changes " & _
"you have made.", vbCritical, "Duplicate Key")
Case Else
Call MsgBox("Error: " & DataErr & " Desc: " & AccessError(DataErr))
End Select
Response = acDataErrContinue
End Sub



-----------
heres a function that will generae a table of all the access errors

just put it in a module and run it

Function AccessAndJetErrorsTable() As Boolean
Dim dbs As Database, tdf As TableDef, fld As Field
Dim rst As Recordset, lngCode As Long
Dim strAccessErr As String
Const conAppObjectError = "Application-defined or object-defined error"

On Error GoTo Error_AccessAndJetErrorsTable 'handled
' Create Errors table with ErrorNumber and ErrorDescription fields.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("AccessAndJetErrors")
Set fld = tdf.CreateField("ErrorCode", dbLong)

tdf.Fields.Append fld
Set fld = tdf.CreateField("ErrorString", dbMemo)
tdf.Fields.Append fld

dbs.TableDefs.Append tdf
' Open recordset on Errors table.
Set rst = dbs.OpenRecordset("AccessAndJetErrors")
' Loop through error codes.
For lngCode = 0 To 3500
On Error Resume Next 'handled
' Raise each error.
strAccessErr = AccessError(lngCode)
DoCmd.Hourglass True
' Skip error numbers without associated strings.
If strAccessErr <> "" Then

' Skip codes that generate application or object-defined errors.
If strAccessErr <> conAppObjectError Then
' Add each error code and string to Errors table.
rst.AddNew
rst!ErrorCode = lngCode
' Append string to memo field.
rst!ErrorString.AppendChunk strAccessErr
rst.Update
End If
End If
Next lngCode
' Close recordset.
rst.Close
DoCmd.Hourglass False
RefreshDatabaseWindow
MsgBox "Access and Jet errors table created."

AccessAndJetErrorsTable = True

Exit_AccessAndJetErrorsTable:
Exit Function

Error_AccessAndJetErrorsTable:
msgbox "error " & err & " generating table"
AccessAndJetErrorsTable = False
Resume Exit_AccessAndJetErrorsTable
End Function

Thanks Gemma.

That is what i was looking for.

Gemma do you trap it this way or do you use your own traps in the Before_Update event?

generally beforeupdate, often on the field controls rather than the form itself (you have both).

sometimes i use the error event as well though to give me a belt and braces approach

it depends what you are trying to do - its not so helpful often for a user to fill in a whole form, and THEN be told its a duplicate - and you cant always be sure exactly which bit is causing the problem - just that access doesnt like it.

if its possible that data is duplicated its often worth checking in the control's beforeupdate, as you can tell the user straight away. It probably improves the useability/acceptability of the software

Thanks Gemma. Good answers.