Error Handling

[robbydogg]

Hi,

I'm having trouble understandign the issue with my error handling not working.

What i need is the following:

Run code queries 1-10
If there's an error in query 2 to 3, then ignore and go onto query 4 and run to the end
If all is ok - run everything
Finish


sounds simple but i'll be jiggered if i can make it work :banghead:

similar to below

Private Sub UpdateHandoversOrderBankData_Click()
DoCmd.SetWarnings False
 
Run Query1
 
' If the next line faults, run the one below, if nto run them all in sequence
 
On Error GoTo My_Error_Handler
 
Run Query2
Run Query3
 
Exit_Point:
Exit Sub
 
Run Query4
Run Query5
Run Query6
Run Query7
Run Query8
Run Query9
Run Query10
 
DoCmd.SetWarnings True
Resume Exit_Point
End Sub

any help would be marvellous

 

[vbaInet]

What should happen if there's an error on 1 or 4? Actually what code lines are you referring to?

 

[robbydogg]

if everything is ok, then it shoudl run through all of them, if there's an error with the second query to run, then ignore and go to the next query.

tfyh

 

[vbaInet]

That doesn't answer any of my questions. What is line 1? The first Run Query?

Also, if the first query fails what should it do? Again, if the 4th query fails, what should it do?

 

[PeterF]

I'm not sure what you're trying to do.
If a bunch of query's need to run and if there's a error in one of them you would like to undo all. You need the BeginTrans, CommitTrans and Rollback methods. Look them up in the help.

If all query's have to run and errors don't matter, put before the first query:

On Error resume next

You can restore your errorhandler after the last query.

 

[delikedi]

Run Query10

My_Error_Handler:
DoCmd.SetWarnings True
Resume Exit_Point
End Sub

 

[vbaInet]

I'm not sure what delikedi is advising?

Unless your application is doing some pretty sensitive work, you don't necessarily need transactions for rolling back. You can use dbFailOnError constant with the Execute method of the database object. That will cause a rollback if an error occurs. E.g.:

set db = Currentdb
db.Execute "INSERT INTO ...", dbFailOnError

 

[robbydogg]

Hi,

I believe i didn't really give full info of what i needed, but i think i have solved it.
I was having issues with the second query failing - it doesn;t matter if it fails but i needed the rest to run anyway.

the vb code now rund something like this:

Private Sub UpdateHandoversOrderBankData_Click()

DoCmd.SetWarnings False

db.Execute "Qry1", dbFailOnError

On Error GoTo ErrorHandler

db.Execute "Qry2 ", dbFailOnError

ExitHandler:

ErrorHandler:

DoCmd.SetWarnings False

db.Execute "Qry3 ", dbFailOnError
db.Execute "Qry4 ", dbFailOnError
db.Execute "Qry5 ", dbFailOnError
db.Execute "Qry6 ", dbFailOnError
db.Execute "Qry7 ", dbFailOnError
db.Execute "Qry8 ", dbFailOnError
db.Execute "Qry9 ", dbFailOnError
db.Execute "Qry10 ", dbFailOnError

DoCmd.SetWarnings True

End Sub


thanks for all of your interactions.

 

[vbaInet]

If there are no depencies then your error handler should look like this:

On Error Goto ErrorHandler
    ... other code here ....

On Error Resume Next
    ... all your db.Execute block of code here ...
On Error Goto ErrorHandler

    ... other code here ....

ExitErrorHandler:
    Exit Sub

ErrorHandler:
    Msgbox "Error message"
    Resume ExitErrorHandler

If you have some way of logging the error, it will be written differently but if you're not bothered about logging the error then the above will suffice.

When I mentioned using the Execute method I forgot to mention that you don't need to turn on/off the warnings. That's one advantage of using this method.

 

[pbaldy]

Why not simply leave off the dbFailOnError argument for query 2? That way if it fails, it fails silently. Plus you can have proper error handling.