On Error suddenly stopped working ??

[bugsy]

Hey

This worked fine for some short (but blissfull) time

On Error GoTo MANError

pathbgc = Path & "\Man.xls"
DoCmd.TransferSpreadsheet acImport, , "B_Man", pathbgc, vbYes

MANError:
If Err.Number = 3011 Then ExitSub
'--------------------------------------------------------------------------

Now the damn error message pops and says error 3011, can't find the file..
Why isn't On Error stopping it anymore ?

 

[Fear Naught]

You need a space between Exit and Sub - therefore the last line should read

If Err.Number = 3011 Then Exit Sub

 

[bugsy]

You need a space between Exit and Sub - therefore the last line should read

If Err.Number = 3011 Then Exit Sub

I am sorry, i copied it incorrectly
it's resume next, not exit sub

On Error GoTo MANError

pathbgc = Path & "\Man.xls"
DoCmd.TransferSpreadsheet acImport, , "B_Man", pathbgc, vbYes

MANError:
If Err.Number = 3011 Then Resume Next

that's what not working

 

[Banana]

If your second copy & paste is right this time, seems to me it'd go in an infinite loop because there's no exit point between the routine and the error handling when the error is raised.

Also, I recall there is a distinction between issuing those commands:

On Error Resume Next

On Error Goto to Oops
...
Oops:
Resume Next

But I left my book home so I can't remember what the difference, but I know it acts different... something about not clearing the error when Resume Next is issued...

 

[DJkarl]

As previously stated if the code below is what you're using then the error code will execute every time this routine runs rather than just on error.

On Error GoTo MANError

pathbgc = Path & "\Man.xls"
DoCmd.TransferSpreadsheet acImport, , "B_Man", pathbgc, vbYes

MANError:
If Err.Number = 3011 Then Resume Next

Should be more like this

On Error GoTo MANError

pathbgc = Path & "\Man.xls"
DoCmd.TransferSpreadsheet acImport, , "B_Man", pathbgc, vbYes

Exit Sub
MANError:
If Err.Number = 3011 Then 
      Err.Clear
      Resume Next
Else
     'OtherErrHandling
End if

 

[bugsy]

Hey DJKarl

I can't do 'Exit Sub', since i got other things importing after "MAN"


However it's not even going to MANError !
It's breaking on DoCmd line...

 

[Banana]

Are you sure you didn't set your VBA editor's preference to "Break on all errors"? I know I've done that myself couple times before.

 

[bugsy]

Banana, nope. just checked.

This is really some kind of mystery. I just opened backup version from few hrs ago. There 'On Error' works fine.
After few changes in that version is mystriously stopped version

 

[Banana]

Two things:

1) When you want to trap for a specific error for a specific line and you already have an error handling, it's easier to do thus:

Private Sub foo()

On Error GoTo Oops

'Execute something here

'We want to ignore whatever error this line may throw
On Error Resume Next
'Do something here

'Resets the error handling back to the original error handling
On Error GoTo 0

'Do some more things here

'We want to trap for specific error
On Error GoTo SpeificOops
'Do something here

'Again, reset error handling back to original
On Error GoTo 0

'Clean up and leave

Exit Sub

'Generic Error Handling
Oops:

'Error handling code

Exit Sub

'Specific Error code
SpeificOops:

If Err.Number= X Then
   'Do something
   Resume Next
Else
   'Do something else
End If

End Sub

2nd thing, do you happend to have Auto-Correct option on?

 

[bugsy]

Hey Banana

I finally did it with 10 separate Subs (each with it's own OnErrr), and then one main sub is calling all of them
More or less does what i had in mind..