Trap error message

[Bill]

I am trying to present the user with a more friendly error messsage than the
following which occurs when the user adds a record with a with a duplicate
value in an index.

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary ket, or
relationship......."

have the following code in the forms error event:


If DataErr = 3022 Then
MsgBox "There is already a company by the same name or" & Chr(13) &
" a person with the same first and last name in the database", vbOKOnly,
"Error"
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If

The above code does not trap the generic error message. Any help would be
appreciated.


Bill

 

[Michel Walsh]

Hi,


Working fine here. Is your field not allowing duplicated value
has a not-null and duplicated value?Can you try it on a new application?


Hoping it may help,
Vanderghast, Access MVP

 

[Bill]

There are three fields involved (lastname, firstname, and companyname.) I
should mention that the code is in a form that is used only to add a new
name and that the error occurs when I try and close the form. The code I
have works fine when I try and go to a new record.

At what point is the generic message being generated? If I can determine
that and find out the error message number, I should be able to trap it.

 

[Bill]

I have now determined that the event is the before insert event and that
this message does not seem to have an error number. I guess I am going to
have to search the database for a duplicate name before I try and save the
record / close the form.

 

[Michel Walsh]

Hi,


You may have to resort to Cancel=True the Unload event.

Example.

The form is open, dirty, the end user try to close the form.

Your code, in Unload, check to see if the form is Dirty, if so,
Cancel=True with (hopefully) a message warning the end user to save the
record before, OR, with your code trying:

Me.Dirty=False

( or Me.Undo ). There are many alternatives, you have to pick one which is
close to what your client expect.


Hoping it may help,
Vanderghast, Access MVP