Error Trapping in VBA

[Adamba]

Hiya all

In my database i am linking to tables from external databases for criteria set up on a form editied by the user. My code can successfully link the tables but if it cannot find the requested table it crashes. I tried putting in the following error trap which seems to work for the first On Error but not for the second. The code seems to skip the second On Error statement and goes straight to informing the user that the table could not be linked and cancels my code.

Below is a sample of the code i am trying to use, i have defined all the parts before the snippet and display a message box after the snippet but thought these were not needed below.

THANKS IN ADVANCE!

Code Example:
LinkTMR:
TMR = "TMRWk" & Right(Forms![Latest Week]!_
[MaxOfTmr Week No], 4)
On Error GoTo TMRError
DoCmd.TransferDatabase acLink, "Microsoft Access",_
"s:\tmr\tmr.mdb", acTable, TMR, "TMRWk", False
TMR = 0
GoTo LinkBP

TMRError:
TMR = 1

LinkBP:
BP = "Wk" & Right(Forms![Latest Week]!_
[MaxOfbp Week No], 4)
On Error GoTo BPerror
DoCmd.TransferDatabase acLink, "Microsoft Access", _
"s:\bp\bp.mdb", acTable, BP, "BPWk", False
BP = 0
GoTo DoneWk

BPerror:
BP = 1

Done Wk = BP + TMR

Adam

 

[GHolden]

do you have exit subs between each error handler to stop the code just running them all

ie.

EXIT SUB

TMRError:
TMR = 1

EXIT SUB

LinkBP:
BP = "Wk" & Right(Forms![Latest Week]!_
[MaxOfbp Week No], 4)
On Error GoTo BPerror
DoCmd.TransferDatabase acLink, "Microsoft Access", _
"s:\bp\bp.mdb", acTable, BP, "BPWk", False
BP = 0
GoTo DoneWk

EXIT SUB

BPerror:
BP = 1

Done Wk = BP + TMR

 

[Adamba]

GHolden,

i do not have exit sub inbetween the routines, i have just tried it and it cancels after the first routine and does not run BP at all.

What i am after is trapping the errors from both TMR & BP into 1 msg box that i create at the end if they = 1.

i thought by using labels in the code i could link to different errors and procedures that way, but this does not seem to work. Is there something i have missed maybe an error trap stop before starting the new error trap or something?

Adam

 

[GHolden]

After TMRError is executed, if you want to go back and run the

DoCmd.TransferDatabase acLink, "Microsoft Access", _
"s:\bp\bp.mdb", acTable, BP, "BPWk", False
BP = 0

you need

TMRError:
TMR = 1
Resume Next

'this will return processing to the line after the error was generated.

If you don't have exit subs the program will go to the first error handler ie. TMRError and process ever line of code after it until it hits the end sub, including all the following error handlers (even though these haven't been called). So to trap multiple errors include the exit sub but use the resume next to continue processing the following code.

 

[Adamba]

GHolden,

Thanks for the help!

Adam