On Error GoTo ... not consistent (Access 2000 on XP)

[jschilt]

I'm in a spot of bother... just spent 6 hours trying to figure this one out - getting very frustrated...

I have seen many posts on this site and some others... all touching on this subject but not quite giving me the answer (but boy have I learned a lot !)

Problem is, in VB in Access 2000, On Error GoTo .. is not consistent...

On Error # 1 works... (ie, table "In Development" does not exist and code jumps to In_Production)
On Error # 2 does not work... I get a MS VB Runtime error 3078
been trying dozens of different approaches...

What am I trying to do ? If a file does not exist, I want to jump and run some specific code...

btw, Tools>Options>General>Error Trapping is set to "Break on Unhandled Errors"

Any suggestions ? Anyone else come across this problem ?

Many thanks in advance...

<start code>
...
On Error GoTo In_Production   '** # 1 **
Set InDevelopment = MyDb.OpenRecordset("In Development")
y = InDevelopment.RecordCount
 
In_Development:
MsgBox "You are in V5 Development - no changes will occur."
Exit Function
 
In_Production:
Countrecs = MySet.RecordCount
Msg = "Reconnecting Databases"
ReturnValue = SysCmd(SYSCMD_INITMETER, Msg, Countrecs)
x = 1
 
On Error GoTo Next_Stage   '** # 2 **
Set InDevelopment = MyDb.OpenRecordset("In Development")
p = p + 1
 
Next_Stage:
... additional code...
 
<end code>

John

[MMcCarthy]

I an having a problem following your logic. However, one thing I've noticed is that you tell the code in error one to go to In_Development and in Error 2 to go to Next_Stage but if error 1 is not found then the recordset

Set InDevelopment = MyDb.OpenRecordset("In Development")

is opened twice.

I think your problem is in your logic.

Remember if an error is not found then the code will procede and the steps you are marking in the code are only bookmarks. They do not stop the rest of the code that following being processed.

For example:

 
Stage1:
   Code1a
   On Error GoTo Stage3 ' This will skip Stage2 but will complete all code sequentially from then on including Stage4.
   Code1b
   On Error GoTo Stage4 ' This will skip Stage2 and Stage3 but will complete all code sequentially from Stage4 on.
 
Stage2:
   Code2
   On Error GoTo Stage1 ' This will return to Stage1 and repeat all the code sequentially from that point.  Therefore if the error in this stage is never resolved or you don't hit the error handler in Stage1 you would end up in an endless loop.
 
Stage3:
   Code3
 
Stage4:
   Code4
 
 

Mary

[NeoPa]

You could try entering

On Error GoTo 0

in the code where it is no longer appropriate to trap the error in the way defined.
You haven't stated, but I'm assuming the error message comes up and the code is stopped on the

Set InDevelopment = MyDb.OpenRecordset("In Development")

line.

[NeoPa]

This only goes wrong when BOTH errors are triggered right?
That's because you can't trigger error code while you're still in an error routine.
You need to issue a Resume command (or exit the scope) before the error code can be triggered again.

For your type of situation where you want to throw an error but continue processing consider using

On Error Resume Next

to get past lines that are likely to error.

[jschilt]

Thanks NeoPa - your thoughts gave me some ideas and I have now been able to successfully run my code...

your words "... you can't trigger error code while you're still in an error routine..." gave me some ideas... I'm now using :

On Error Resume Next
... VB statement (may be in error)
If Err.Number = 1234 then
... execute some more code
... End If

Using this method, the error is handled in my code, not by VB/Access.

Thanks again.

John

[NeoPa]

My pleasure - and thanks for the reply. It does help.