How to use VB to make a custom error msg to replace the default PK violation msg? (1 Viewer)

oZone · Nov 16, 2008

In my database I have a PK in the Actors table so when I enter a value that already exists in the PK field I get an error message similar to: "The changes you requested were not successfull because... primary key... etc...".

Is there a way to use VB code to display a different pop-up message instead of this default one? Something like "This actor already exists in the database.".

Is there a way to do this?

CyberLynx · Nov 16, 2008

Next time you get the Error, write down the Error Number.

Enter code into the Form's OnError event to handle the errors you want:

Code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Dim Msg As String
    
    Select Case DataErr
       Case [I][COLOR="Red"]WhateverTheErrorNumberIs[/COLOR][/I]
          Msg = "The data you entered already exists in the database."
       Case [COLOR="Red"][I]PerhapsSomeOtherErrorNumber[/I][/COLOR]
          Msg = "Some other Message to pass to the User"
       Case Else
          Msg = "Some sort of Default Error Message."
    End Select
    
    MsgBox Msg, vbExclamation, "Database Error"
    Responce = acDataErrContinue
    If Err <> 0 Then Err.Clear
End Sub

.

oZone · Nov 16, 2008

It doesnt give me an error number though. It's the default error message that happens when a user tries to enter a duplicate value in any PK field.

Here is the exact error (or more like an alert) message:

"The changes you requested were not successfull 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."

No error number... How would I change this message?

boblarson · Nov 16, 2008

You would use the Before Update event of the form to validate that there is no duplicate before letting the record attempt to be updated. If you find a duplicate then you would issue a Cancel = True and it would cancel the update and return them to the form. You can put your own message just after the Cancel = True and that should solve the problem.

gemma-the-husky · Nov 16, 2008

it doesn't give you an error number because access's intrinsic error handler shows the message only, not the error number

in bobs on error routine just put temporarily

msgbox(dataerr) - that will show you the error - or even

call msgbox("Error: " & dataerr & " Desc: " & dataerr.description)

which will show both ( i think - not tested it)

------------

out of interest this code, will create a complete error number/text table in your database which may be useful

Code:

Function createErrorsTable() 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
    ' 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
        ' 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."

createErrorsTable = True

Exit_AccessAndJetErrorsTable:
    Exit Function

Error_AccessAndJetErrorsTable:
    MsgBox Err & ": " & Err.Description
    createErrorsTable = False
    Resume Exit_AccessAndJetErrorsTable
End Function

oZone · Nov 23, 2008

boblarson said:
You would use the Before Update event of the form to validate that there is no duplicate before letting the record attempt to be updated. If you find a duplicate then you would issue a Cancel = True and it would cancel the update and return them to the form. You can put your own message just after the Cancel = True and that should solve the problem.

Can you tell me how to validate that there is no duplicate?

I'm an access noob and definately a VB noob... need a little help with the code. Can you (or someone) show me an example please?

When a duplicate is chosen, Access displays error 3022 (duplicates error). So, after some searching elsewhere on the web I found some code that worked for someone else to alter the default duplicates error message, but it doesnt work for me. Help?

I got this far, but it doesnt work at all, doesnt display the message box and still shows error 3022.
Unsure where to go from here:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_Duplicates_BeforeUpdate
    Exit Sub

Err_Duplicates_BeforeUpdate:
    If Str(Err.Number) = 3022 Then
        MsgBox "Update Failed: This movie is currently loaned to another borrower."
    Else:
        MsgBox "Error! : " & Str(Err.Number) & " - " & Error$ 
    End If
Exit Sub
End Sub

oZone · Nov 23, 2008

Nevermind, I got it to work

I found the error code, used the code CyberLynx suggested, and put it into the forms error event code:

Code:

    Dim Msg As String
    
    Select Case DataErr
       Case 3022
          Msg = "This star already exists in your database"
       Case Else
          Msg = "Update failed. Please check your data and try again"
    End Select
    
    MsgBox Msg, vbExclamation, "Error: Unable to add this star to the database"
    Response = acDataErrContinue
    If Err <> 0 Then Err.Clear

gemma-the-husky · Nov 23, 2008

ozone

the error code doesnt arise UNTIL you try to insert the record

so before update event wont catch it. Before update allows you manually to inspect the data (with code), and make sure the data appears correct before permitting the update. ie checking ranges or certain values, making sure required fields are completed and so on. You could actually perform a lookup here to see if the item already exists.

the point is that these tests do NOT raise an error.

If you don't test for an error, and just process the update, then the next event is the after update event. The record HAS now been added, and here you may want to tidy up the display, lock/unlock some fields etc, but it is too late now to stop the update.

Now in-between the before update, and after update, the update actually occurs. Access does this for you, so you still get the before update, but if the update fails, you get an error raised, and no after update. In this case, Access reports the error with its own error message, and no error number.

So you can intercept the access error, by using the error event and depending on the error number, replace it with a tailor made error message of your own. This is what you have done. The problem is often finding the error numbers that access is using.

If you use an error event then this will catch ANY form error.

On the other hand, if you do something that knowingly may cause an erorr, you can generally trap this in code, and resolve the particular issue with a error handler

oZone · Nov 23, 2008

I understand about the before/after update events, thanks for the insight Gemma

gemma-the-husky said:
The problem is often finding the error numbers that access is using.

On the other hand, if you do something that knowingly may cause an erorr, you can generally trap this in code, and resolve the particular issue with a error handler

How exactly (code example) do you find, catch, or trap error codes? This is something I'm highly interested in.

I'd love to build code into my databases that will provide error codes along with error messages. I could then build custom, non-cryptic messages for the end user (who may not understand access error messages and what they mean for them).

gemma-the-husky · Nov 23, 2008

see my previous post re error handling - expanded here now

immediately before a statement you want to trap put the command, then exexute the statement

say you want to insert a record

sqlstrg = "insert into mytable (field1, date1) select 'smith',12/11/08"
docmd.runsql sqlstrg

now say this fails for a variety of reasons
a) syntax error eg in the above date should be formatted #12/11/08#
b) no table called mytable
c) insert fails with duplicate error, or required field error, or ref integrity error
(all of these have different error codes - see my earlier insertion for all the error codes)
etc

now all of these errors can be trapped. Before running the docmd line you set the error trap
so you get

Code:

'set the error trap
on error goto labelname

sqlstrg = "insert into mytable (field1, date1) select 'smith',12/11/08"
docmd.runsql sqlstrg

msgbox("Record Added")

exithere:
'cancel the error handler, otherwise it remains active in your programme, and other errors will jump into this biit of code
on error goto 0

'make sure the code doesnt drop back into the error handler
exit sub

labelname:
'any error forces the code to jump here
msgbox("Error: " & err & "   Desc: " & err.description)

'now a resume statement is most important - don't just use a goto as the error handlers are not re-entrant, and you cannot activate another error handler until you close this error handler with the resume statement
'resume at another label
resume exithere

if you have a line that may error, but it doesnt matter
eg try to delete a temporary table, that may not be there, you can just set the error handler to ignore the error carry on

so

Code:

'set the error trap
on error resume next
docmd.runsql "drop table temptable"
'if there is a table it will be dropped. if not it will throw an error, which will be ignored - code execution will just continue

'now disable the error handler
on error goto 0

oZone · Nov 23, 2008

Thanks again for the insight.

I tried using the error number/text table code you inserted above, but I dont know where to put it or how to use it.

I'm still a noob to VB...

Do I put the error number/text table code in the form that is giving me errors? Do I put it inside an event declaration? Or at the end of the code for that form? Or??? I dont know how to use it.

I also tried putting the error trap code into the OnError event of my form, but it didnt do anything either. It didnt pop up an error number, it still gives me error 3022 (duplicates error message).

I'm flying blind because I dont know VB. I dont know what to do now...

gemma-the-husky · Nov 24, 2008

you dont need the error table code

that just creates a stand alone table in your database, with the error information . More just for interest really

---------
the error trap code needs to go somewhere you anticpate an error may happen.

in your case you know you have error 3022

so in the on error event, if you want to replace the access message you have something like

select case accesserror
case 3022: msgbox("Your message")
end select

response = acdataerrcontinue

--------
however, you need to have some understanding of visual basic etc - if all this is new to you, you rellay need to get a genral access primer to get you started.

How to use VB to make a custom error msg to replace the default PK violation msg? (1 Viewer)

oZone

Registered User.

CyberLynx

Stuck On My Opinions

oZone

Registered User.

boblarson

Smeghead

gemma-the-husky

Super Moderator

oZone

Registered User.

oZone

Registered User.

gemma-the-husky

Super Moderator

oZone

Registered User.

gemma-the-husky

Super Moderator

oZone

Registered User.

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread