Why does my VBA script not continue after debugging?

Question

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

this may be a silly question, but after you debug your macro are you hitting f5 to continue the script? — jellz77
– jellz77, Commented Jun 28, 2016 at 14:00
@jellz77 No, I was not. After I hit F5 it brings up a dialog box that asks for a macro name, but I don't know a macro to give it. I was running a VBA script... — whatwhatwhat
– whatwhatwhat, Commented Jun 28, 2016 at 14:02
After I debug I make sure that the script is not paused by hitting Ctrl + S (save). It removes the [break] at the top, so I assume that the script resumed. But it doesn't fire anymore. — whatwhatwhat
– whatwhatwhat, Commented Jun 28, 2016 at 14:03
can you post some code so we're able to debug what you've been working on — jellz77
– jellz77, Commented Jun 28, 2016 at 14:04
Depends on context. In a form module, just Sub is Private. — HansUp
– HansUp, Commented Jun 28, 2016 at 17:27

Dave B · Accepted Answer · 2016-06-28 17:22:01Z

1

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.

answered Jun 28, 2016 at 17:22

Dave B

65910 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

whatwhatwhat Over a year ago

That's what I ended up doing, yea.

Hynek Bernard · Accepted Answer · 2016-06-28 14:02:53Z

0

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.

answered Jun 28, 2016 at 14:02

Hynek Bernard

7461 gold badge12 silver badges32 bronze badges

2 Comments

whatwhatwhat Over a year ago

Could this possibly be done within the same script? Like refreshing the timer within the error handling or something?

Hynek Bernard Over a year ago

I am not that much experienced with timers, so I don't know the exact command to re-run the timer. But it should be possible with error handling inside whole sub to dodge timer stopping.

Peter Davis · Accepted Answer · 2020-11-05 16:28:16Z

0

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.

answered Nov 5, 2020 at 16:28

Peter Davis

3182 silver badges8 bronze badges

Collectives™ on Stack Overflow

Why does my VBA script not continue after debugging?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related