Custom Error Message Fails to Work

I seem to be unable to have a custom error message to appear for Error
10011 (database was unable to append all the data to the table). Each
time, the MS Access default error message box appears. The reason for
the error is to prevent an import of a record of which one already
exists based on a like item number.

I tried various methods of structuring an error handler in the sub,
including Select Case, but with no success. I assume that the Form - On
Error procedure should be used instead, but that has not worked either.

Const AddDataError = 10011
If DataErr = AddDataError Then
MsgBox "The item number associated with the record already exists in
the database."
Response = acDataErrContinue
End If

Any assistance would be much appreciated. Thanks.

re: Custom Error Message Fails to Work

Rolan wrote:[color=blue]
> I seem to be unable to have a custom error message to appear for Error
> 10011 (database was unable to append all the data to the table). Each
> time, the MS Access default error message box appears. The reason for
> the error is to prevent an import of a record of which one already
> exists based on a like item number.
>
> I tried various methods of structuring an error handler in the sub,
> including Select Case, but with no success. I assume that the Form - On
> Error procedure should be used instead, but that has not worked either.
>
> Const AddDataError = 10011
> If DataErr = AddDataError Then
> MsgBox "The item number associated with the record already exists in
> the database."
> Response = acDataErrContinue
> End If
>
> Any assistance would be much appreciated. Thanks.
>[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To use user-defined error numbers you have to raise the error yourself:

On Error Resume Next

' do something that may cause an error condition that you want
' your error to show

If err > 0 Then
Err.Raise AddDataError,,"My error msg"
End If

Usually a number is added to a built-in intrinsic constant. Something
like this:

vbObjectErr + 512 [or whatever number you want]

Err.Raise vbObjectErr + AddDataError, , "My Error Msg"

If err = vbObjectErr + AddDataError Then ...

This is to allow future versions of Access to increase the number of
built-in error numbers w/o affecting user-defined error numbering.

See the Access VBA Help articles on Err.Raise for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzi4EYechKqOuFEgEQJtVQCg9koc/Omt0qhxAC5AgBv2rMLpUM8AoO6t
i/N1NdQbvkVHbOm6Qn5mlimh
=pkex
-----END PGP SIGNATURE-----

re: Custom Error Message Fails to Work

Sorry if I did not clarify the post, but this is not related to a
user-defined error number. Error 10011 is a bonafide MS Access (97)
Error number, and regardless of what error trap that has been
constructed, so that a user-designed error message can be shown, the MS
Access default error message is displayed instead (database was unable
to append all the data to the table...) Is this an unknown bug or what?
Below is a related post that I found in the archives that describes the
same phenomenon; however, I have spent considerable time writing error
trapping code, but with no success.

Anyone's insight will be appreciated.


Newsgroups: comp.databases.ms-access
From: soare...@qatar.net.qa (Edward S) - Find messages by this author
Date: 12 Apr 2003 07:16:25 -0700
Local: Sat, Apr 12 2003 10:16 am
Subject: Customs Message instead of the Access Message

I have a button which runs a code to import data from an Excel
spreadsheet to a table
in Access, however when it finds that the data to import already exits,

a message pops like this "Access was unable to append all the data to
the table ............ " . I do not want this message, but want to
replace it with a message like "The file you are trying to import has
already been imported before, please check and try again"


How can I accomplish this. Could someone point me in the right
direction


Regards
Ed



Michael Bragg Apr 12 2003, 11:54 am

Newsgroups: comp.databases.ms-access
From: "Michael Bragg" <pmbr...@megavision.com>
Date: Sat, 12 Apr 2003 10:54:42 -0500
Local: Sat, Apr 12 2003 11:54 am
Subject: Re: Customs Message instead of the Access Message


I think you can use your On Error Goto feature. This will allow you to
go to
any error message that you want to.


HTH
Michael



Rockey Apr 12 2003, 2:25 pm

Newsgroups: comp.databases.ms-access
From: Rockey <member1...@dbforums.com>
Date: Sat, 12 Apr 2003 17:58:19 +0000
Local: Sat, Apr 12 2003 1:58 pm
Subject: Re: Customs Message instead of the Access Message


Correct-


Reproduce the error to grab the error number. Then substitute the
number for ###.


Private Sub DoSomething()


On Error GoTo PROC_ERR


PROC_EXIT:
Exit Sub


PROC_ERR:
Debug.print Err.Number


If Err.Number = ### Then
MsgBox "Access was unable to append all the data to
the table ............ "
Else
MsgBox Err.Description
End If
Resume PROC_EXIT

re: Custom Error Message Fails to Work

Well, my syntax is probably wrong cause I'm doing this off the top of my
head, but I think you need to do something like this ...

Dim dbs As DAO.Database
Set dbs = CurrentDB()
dbs.Execute "qryAppendYourRecords", dbFailOnError

I think I've used this syntax to accomplish what you describe, but whatever
you do, the FailOnError flag needs to be set for it to pass the error to your
error handler. You may need to add a DoCmd.SetWarnings False to ignore
the query generated error.

--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast/



"Rolan" <continue@safe-mail.net> wrote ...[color=blue]
> Sorry if I did not clarify the post, but this is not related to a
> user-defined error number. Error 10011 is a bonafide MS Access (97)
> Error number, and regardless of what error trap that has been
> constructed, so that a user-designed error message can be shown, the MS
> Access default error message is displayed instead (database was unable
> to append all the data to the table...) Is this an unknown bug or what?
> Below is a related post that I found in the archives that describes the
> same phenomenon; however, I have spent considerable time writing error
> trapping code, but with no success.
>
> Anyone's insight will be appreciated.
>[/color]