Index Message

[ions]

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.

 

[genesis]

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.

 

[ions]

Hi Genesis,

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

 

[genesis]

say what kind of customized message?

 

[genesis]

how about this:

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

 

[ions]

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.

 

[John Big Booty]

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.

 

[genesis]

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.

 

[gemma-the-husky]

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

[B]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[/B]

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

just put it in a module and run it

[B]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[/B]

 

[ions]

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?

 

[gemma-the-husky]

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

 

[ions]

Thanks Gemma. Good answers.