Solved

Pause Button

Posted on 2010-11-23
11
298 Views
Last Modified: 2012-05-10
I have code which loops through a customer database table and emails each customer via Outlook 07.
This code is fired from a form with a command button.
Is there anyway I can manually pause this code (either by clicking a command button or perhaps a keystroke) and then resume the code at the point it stopped?
~Worcse
0
Comment
Question by:Worcse
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 34199715
Hi, Worcse.

You can set a breakpoint in the code.  that will pause execution at that point until you tell it to resume.  To set a breakpoint, open the code editor. click on the line you want to break on, and press F9.  The line will turn red.
0
 
LVL 7

Expert Comment

by:cmrobertson
ID: 34199790
you can also set a hard breakpoint using "Stop" it will pause execution and could be programmed to your button
0
 

Author Comment

by:Worcse
ID: 34199822
BlueDevilFan -
Thank you for the response.  What I am looking for is a way (if possible) to pause the code after it has fired.  
If my table contains 3000 emails and I fire the code... it starts the looping and email process.  Lets say that after a random number of loops I want to pause the code....  is this possible?
0
 
LVL 7

Expert Comment

by:cmrobertson
ID: 34199906
yes set a counter and use "stop" when appropriate
0
 

Author Comment

by:Worcse
ID: 34200678
cmrobertson -
Thank you... could you give me a little more in-depth information behind you answer?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 34200693
yes, but what would you want to do during the pause?

I would probably do something like add another button to the form (call it cmd_Pause_Resume).  I would set its Caption to "Pause" and the Tag property to -1.

When you click the button to start this email process, I woud enable cmd_Pause_Resume, and the Click event of this button would look like:

Private sub cmd_Pause_Resume_Click

    Me.cmd_Pause_Resume.Tag = Not Me.cmd_Pause_Resume.Tag
    Me.cmd_Pause_Resume.Caption = IIf(Me.cmd_Pause_Resume.Tag = 0, "&Resume", "&Pause")
    if me.cmd_Pause_Resume.Tag = - 1 then Call SendEmail(False)    
End Sub

In the code module that actually sends your email, I would add a declaration to indicate whether you are starting this process from scratch or resuming.  It might look like below.

Public Sub SendEmail(Optional Reset as Boolean = True)

With this declaration statement, you can tell the function whether to reset the static variable described below.

If Reset then lngAbsRec = 0

I would also insert a line in the declaration section of this same code module which sends your email.  This line would declare a static variable (lngAbsRec) which will be used to determine what record to start on if you resume the email process.

Static lngAbsRec as Long

At the bottom of your loop, I would add a line of code that checks the Tag property of cmd_Pause_Resume.  If the Tag property changes to 0, the I would exit the loop and close the recordset.  In total, that code module would look something like:

Public Sub SendEmail(Optional Reset as Boolean = True)

    Static lngAbsRec as Long
    Dim strSQL as string
 
    strSQL = "SELECT [email] from [your table]"
    Set rs = currentdb.openrecordset (strsql,, dbfailonerror)

    'If you are resuming the process, skip to the next record in the recordset
    if Reset = False AND lngAbsRec > 0 Then
        docmd.GoToRecord ,,acGoTo, lngAbsRec + 1
    End if

    'Loop through the records in the recordset
    Do while not rs.eof
        'send your email here

        lngAbsRec = rs.absolutePosition
        if me.cmd_Pause_Resume.Tag = 0 then Exit Do
        rs.movenext
    Loop

    rs.close
    set rs = nothing

End Sub

Hope this helps.
Dale
0
 
LVL 7

Expert Comment

by:cmrobertson
ID: 34207416
If you use the command "Stop" it is just like setting a break point in code, the major difference being it is saved when you close and reopen your project, so yoiu could say:
ictr = 0
Do for whatever your condition is
    ictr = ictr + 1
    if ictr = 100 then
        stop
    endif
loop
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 34210116
See sample attached.

All you would need to do is modify the SendMail subroutine to open your recordset that contains email addresses.
PauseLoop.accdb
0
 

Author Comment

by:Worcse
ID: 34307621
So sorry for the delay ~ Family... can't live with em - cant' give em away

fyed:  your suggestion looks like a possible solution... unfortunately, my parameters have changed so I don't know if I will need to open a new question or if I can continue with this one.  I guess the EE gods will let me know.

What I need to accomplish is for my code to loop through and email the first 200 emails in my table
I then need a 15 minute pause
Then I need to email the next 200 emails in the table
And then pause for 15 minutes....  until EOF

How would I accomplish this...?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 34310411
If you want your code to handle that automatically, without manual intervention, then you would need to add a pause into your code loop.  I've modified the sample database above so that it implements a one minute pause after every 50 records.

Take a look at the code in the SendMail subroutine.  After every 50 records, it sets the TimerInterval to 1 minute (TimerInterval = 60,000), and calls the cmd_Pause_Resume_Click subroutine.  This basically performs the same action as if you clicked the Pause button.  But because of the TimerInterval, after one minute, the Timer Event gets fired, which calls that subroutine again, the same thing as you clicking the Resume button.

All you would need to do is change the frequency from 50 to 100, and the TimerInterval from 60,000 to 900,000.


PauseLoop.accdb
0
 

Author Closing Comment

by:Worcse
ID: 34406201
fyed ~
Sorry for my delay in getting back to you.
Your suggestion/answer was spot on...!!!
U Da Man
Thank your again for your help.
worcse~
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Supress Detail 4 17
Unbound TextBox: Change color when clicked 5 13
How hide #Error in a form field 2 10
DCount using "OR" 4 17
MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now