onerror trap doesn't trap error! 
Author Message
 onerror trap doesn't trap error!

I have a simple parameter delete query that asks for the record number to
delete.  This table is related to another table.

As it happens, the relationship was set for ref integrity, but cascade
deletes was NOT checked.  Thus, running the query from the query design grid
failed with a message about "can't delete ..due to key violations..."

If I change the relationship to cascade deletes, the query will work.

Now, the interesting (to me) part.  I have a button on a form for "delete
item."  Here is a portion of the code:

    On Error GoTo PromoteErr:
    Dim ws As Workspace, db As Database, rst As Recordset
    Dim C_PLS_ID, New_LS_ID, New_PG_ID As Long, mx As Integer, ctr As Byte
    Dim xUser As String, xLastEdited As Date

    Set db = CurrentDb
    Set ws = DBEngine.Workspaces(0)

ws.begintrans
....
lots of other stuff here
....
    With CurrentDb.QueryDefs("Qry_Delete_Rec")
          .Parameters![Current_PLS_ID_Number] = C_PLS_ID
          .Execute
    End With

ws.committrans

ErrorDone:
    Exit Sub
PromoteErr:
    mx = MsgBox("An error has occurred!"   "Please try again.  Click 'OK' to
continue.", vbOKOnly + vbExclamation, "Status of operation")
ws.Rollback
    db.Close
    Set ws = Nothing
    Set db = Nothing
    DoCmd.Hourglass False
    Resume ErrorDone

Well, when the same delete query runs here, NO error trapping occurs!

Why not?  It's the same query on the same table with the same (bad)
relationship?

I realize that if I correct the relationship all is "OK" but I don't
understand why the query, which fails here too (since the record is NOT
deleted), does not generate a trappable error and the user is left to
believe the delete was processed.

Thanks!

Bill



Sun, 18 Nov 2001 03:00:00 GMT  
 onerror trap doesn't trap error!

.Execute does not Trigger an Error
You Can Check the .RecordsAffected Property Though and Raise an Error
 With CurrentDb.QueryDefs("Qry_Delete_Rec")
    .Parameters![Current_PLS_ID_Number] = C_PLS_ID
    .Execute
    If .RecordsAfftected = 0 Then _
      Err.Raise 300000,"Child.Exist","At least one Child Record exist" '
Or Whatever
 End With
--
Regards
Pieter Wijnen


http://www.thuleeng.com

Tough guys don't take backup - tough guys cry a lot..

Good site to look for Access answers is:
http://home.att.net/~dashish

  wijnen.vcf
< 1K Download


Mon, 19 Nov 2001 03:00:00 GMT  
 onerror trap doesn't trap error!
Hi Bill,

DB.Execute does not raise errors.
Take a look at Access Help for: RecordsAffected Property
If no records are deleted you can inform the user

--
Calum Reay
Paradox Contracting Ltd
http://www.pcltd.co.uk/
http://www.pcltd.co.uk/access


Quote:

> I have a simple parameter delete query that asks for the record number to
> delete.  This table is related to another table.

> As it happens, the relationship was set for ref integrity, but cascade
> deletes was NOT checked.  Thus, running the query from the query design
grid
> failed with a message about "can't delete ..due to key violations..."

> If I change the relationship to cascade deletes, the query will work.

> Now, the interesting (to me) part.  I have a button on a form for "delete
> item."  Here is a portion of the code:

>     On Error GoTo PromoteErr:
>     Dim ws As Workspace, db As Database, rst As Recordset
>     Dim C_PLS_ID, New_LS_ID, New_PG_ID As Long, mx As Integer, ctr As Byte
>     Dim xUser As String, xLastEdited As Date

>     Set db = CurrentDb
>     Set ws = DBEngine.Workspaces(0)

> ws.begintrans
> ....
> lots of other stuff here
> ....
>     With CurrentDb.QueryDefs("Qry_Delete_Rec")
>           .Parameters![Current_PLS_ID_Number] = C_PLS_ID
>           .Execute
>     End With

> ws.committrans

> ErrorDone:
>     Exit Sub
> PromoteErr:
>     mx = MsgBox("An error has occurred!"   "Please try again.  Click 'OK'
to
> continue.", vbOKOnly + vbExclamation, "Status of operation")
> ws.Rollback
>     db.Close
>     Set ws = Nothing
>     Set db = Nothing
>     DoCmd.Hourglass False
>     Resume ErrorDone

> Well, when the same delete query runs here, NO error trapping occurs!

> Why not?  It's the same query on the same table with the same (bad)
> relationship?

> I realize that if I correct the relationship all is "OK" but I don't
> understand why the query, which fails here too (since the record is NOT
> deleted), does not generate a trappable error and the user is left to
> believe the delete was processed.

> Thanks!

> Bill



Mon, 19 Nov 2001 03:00:00 GMT  
 onerror trap doesn't trap error!

Quote:

>I have a simple parameter delete query that asks for the record number to
>delete.  This table is related to another table.

When using .Execute, you must pass it the dbFailOnError constant in order
for it to generate runtime errors.  For example:

with qry
    .parameters!CustomerID = 1101
    .execute dbFailOnError
end with

or

opendatabase(path).execute "DELETE * FROM tblCustomers WHERE CustomerID =
1101", dbFailOnError

Try that.



Mon, 19 Nov 2001 03:00:00 GMT  
 onerror trap doesn't trap error!
OK, I'll try the dbfailonerror, since that section of code has several
append, update, delete queries that I've bundled within a transaction block,
and if ANY fail I need to rollback.

Thanks!

Bill



Mon, 19 Nov 2001 03:00:00 GMT  
 onerror trap doesn't trap error!

Quote:

>OK, I'll try the dbfailonerror, since that section of code has several
>append, update, delete queries that I've bundled within a transaction
block,
>and if ANY fail I need to rollback.

ULTRA IMPORANT!  Anytime you have a routine with an error trap and a
.BeginTrans, remember to put a .RollBack in the error handler.  I didn't do
this once.  The BeginTrans went off, an error occurred and was trapped, but
no .RollBack.  The user carried on, doing this and that.  None of their
changes ever made it to the database, because it was all within a
transaction!


Tue, 20 Nov 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. On Error doesn't trap error!

2. Resume Next doesn't trap error

3. KeyPress() event doesn't trap all characters

4. Trapping Action Query 'Errors'

5. Trapping 'Save to Floppy' ERROR codes

6. Simple 'AppActivate' Error-trapping Question

7. Can't find post on error trapping

8. Can't trap an error in Form_Load

9. Can't trap runtime errors

10. HELP - Radio error trapping don't work on IE3

11. Can't trap Inet Control Error

12. Can't trap an error raised with raiserror

 

 
Powered by phpBB® Forum Software