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.".
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
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?
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.
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
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
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
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
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).
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
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...
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.
