VBA How to exit function on error? Not working. Access 2007

Question

In Access 2007 My Error Trapping is set on Break on Unhandled Errors

I want the code to stop at the line where an error occurs and exit functions instead of resuming to the next line of code. However it doesn't seem to be working for me. I purposely created an error at line 6 to see if it would exit the function after this line but it only prompts the error handler message and continues to resume to the next line after error occured. Here is my code:

GoToBackend():

'Go to current linked backend database

Private Function GoToBackend()

On Error GoTo BackendErrorHandler

    'To update BEPath requires two sets of proc.
    'Delete Exisiting
     RunQuery "DeleteBEEPath"   'Here is where I created error by miss spelling it
    'Insert Into
     RunQuery "InsertBEPath"

    'Prompt alert
    MsgBox "Front end tables succesfully linked. Access now needs to run the backend database to complete the linking process. Please ensure macros/vba are enabled if prompted.", 48
    Hyperlink.GoHyperlink (Hyperlink.PrepHyperlink(GetBackendPath))

ExitFunction:
    Exit Function   'Why won't this exit the function? 

BackendErrorHandler:
    Dim Msg As String
    Msg = Err.Number & ": " & Err.Description
    MsgBox Msg
    Resume ExitFunction
End Function

RunQuery():

'Run a given query name
Private Function RunQuery(qName As String)
On Error GoTo RunQueryErrorHandler
   DoCmd.SetWarnings False
   DoCmd.OpenQuery qName
   DoCmd.SetWarnings True

ExitFunction:
    Exit Function
RunQueryErrorHandler:
    Dim Msg As String
    Msg = Err.Number & ": " & Err.Description
    MsgBox Msg
    Resume ExitFunction
End Function

Newd Newd · Accepted Answer · 2015-05-22T16:09:59

The error itself happens in your RunQuery function so the error is handled there. In there you say to display the message for the error with Msg = Err.Number & ": " & Err.Description so therefore it pops up and the error is considered "Handled" and the original function continues to run.

Your line here:

RunQuery "DeleteBEEPath"

Doesn't care what your string is, in its eyes you have properly provided it a string to pass onto the function. Once it enters the function that is where the actual error occurs. I haven't tested this but I believe if you turn off the error handling in your second function then the error handling in GoToBackend should handle it in the way you want it to. So your second function would be something like this:

Private Function RunQuery(qName As String)
   DoCmd.SetWarnings False
   DoCmd.OpenQuery qName
   DoCmd.SetWarnings True
End Function

Once again I haven't really been able to test this but this should get you on the right path or perhaps if someone with some more experience is around they could provide a better answer than I.

I would also recommend putting DoCmd.SetWarnings (True) in your error handling, that way if the error occurs after you set the to false they will get turned back on.

Edit: I figured I would throw in my newly conceptualized function. (Untested)

Public Function RunSQLNoWarnings(strSQLQuery As String) As Boolean
On Error GoTo Err_Handler


    DoCmd.SetWarnings (False)
    DoCmd.RunSQL (strSQLQuery)
    DoCmd.SetWarnings (True)

    RunSQLNoWarnings = True

Exit_Handler:
    Exit Function

Err_Handler:
    DoCmd.SetWarnings (True)
    Call LogError(Err.Number, Err.Description, strMODULE_NAME & ".RunSQLNoWarnings on SQL Query: " & strSQLQuery)
    RunSQLNoWarnings = False
    Resume Exit_Handler
End Function

VBA How to exit function on error? Not working. Access 2007

1 Answers