CODEWITHSUNDEEP
debuggingms-accessvba
whatwhatwhat
whatwhatwhat

Reputation: 2256

Why does my VBA script not continue after debugging?

Whenever I hit an error with my script, the focus turns to the VBA code and the offending line. I fix it, and hit save. Then I notice that the script is no longer running, even after I make sure that it's not paused.

For example, right now I'm using a Form_Timer() event to do some testing (interval set to 1000ms). To test the script again, I just set it to a minute in the future (e.g. if the current time is 8:54:00 AM I set it to fire at 8:55:00 AM). But this stops working after an error. Does anyone know why this is? I don't want to have to tell my users to close and re-open their copies of the Access DB just to make the script work again.

Code:

Private Sub Form_Timer()


    On Error GoTo ErrorHandler

    current_date_time = Now


    If current_date_time = #6/28/2016 8:52:00 AM# Then 

        MsgBox ("the current_date_time variable holds: " & current_date_time)

        'Declare objects
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim oApp As Outlook.Application
        Dim oMail As Outlook.MailItem
        Dim mail_body As String

        'Set objects
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("qry_BMBFLoc")
        Set rst = qdf.OpenRecordset
        Set oApp = New Outlook.Application
        Set oMail = oApp.CreateItem(olMailItem)

        mail_body = "The following jobs do not have the special BF location set in Job Orders: " & vbCrLf

        If Not (rst.EOF And rst.BOF) Then

            rst.MoveFirst
            Do Until rst.EOF = True

                    mail_body = mail_body & rst!job & "-" & rst!suffix & vbCrLf

            rst.MoveNext
            Loop

            'Email contents
            oMail.Body = mail_body
            oMail.Subject = "Blah"
            oMail.To = "[email protected]" 
            oMail.Send

            'Close stuff
            rst.Close
            dbs.Close
            Set rst = Nothing
            Set oMail = Nothing
            Set oApp = Nothing

        End If

    End If

Exit Sub

ErrorHandler:

        Dim msg As String
        If Err.Number <> 0 Then
               msg = "email Form Timer Error #" & Str(Err.Number) & " error Line: " & Erl & Chr(13) & Err.Description
               MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
        End If
        Exit Sub

End Sub

Upvotes: 1

Views: 1659

Answers (3)

Peter Davis
Peter Davis

Reputation: 318

Yeah this sucks. I am writing a vba script for outlook and so the only way to debug is to close and reopen outlook after every error.

Upvotes: 0

Dave B
Dave B

Reputation: 659

In order to reactivate the code, you could close the form when the error is triggered. The user would then have to reload the form to complete the action.

However, without any intervention the error is likely to occur again.

Edit: Or you could write a Function to automatically close, and re-open the offending form. Calling it in the on error command.

Upvotes: 1

Hynek Bernard
Hynek Bernard

Reputation: 744

When there is an error in access form, the timer will stop working, you don't need to close and reopen the whole database, only the form to start the timer again. Otherwise you can add a button called "refresh" and bind macro to it which will turn the timer on again.

Upvotes: 0

Related Questions