How to stop running sub routine?

[deko]

I have a sub routine that searches the Outlook PST for a message sent
to/from a particular email address. Obviously, this can take a long time
when the PST contains thousands of messages. I'd like to put a button on my
Access 20003 form that will stop the sub routine when clicked. I know I can
press Ctl + Break and stop the code, but is there a more graceful way to do
this programmaticall y? Should I use SendKeys?

Also, it would be nice to have the datasheet (that shows the results of the
email search) populate with the found message headers as they are found -
I'm still thinking about how to do this.... any thoughts/suggestions are
very welcome!!

thanks!

[Trevor Best]

Re: How to stop running sub routine?

On Sun, 11 Jan 2004 07:34:20 GMT in comp.databases. ms-access, "deko"
<dje422@hotmail .com> wrote:
[color=blue]
>I have a sub routine that searches the Outlook PST for a message sent
>to/from a particular email address. Obviously, this can take a long time
>when the PST contains thousands of messages. I'd like to put a button on my
>Access 20003 form that will stop the sub routine when clicked. I know I can
>press Ctl + Break and stop the code, but is there a more graceful way to do
>this programmaticall y? Should I use SendKeys?
>
>Also, it would be nice to have the datasheet (that shows the results of the
>email search) populate with the found message headers as they are found -
>I'm still thinking about how to do this.... any thoughts/suggestions are
>very welcome!![/color]

20003 eh? Wow, can't wait for that one <g>

I assume your code has some kind of loop? e.g.

Do while <someconditio n>
.. doing messages
Loop

If this is in a form then create a module level variable (in the
delcations section, e.g.

Dim mfSearching as Boolean and have a "stop" button on that form

or if in a public module then a public or global variable, then use a
popup form with a "stop" button on it. The Stop button's code will
look like...

Sub cmdStop_Click()
mfSearching = False
End Sub

Now back to your main loop, it should do something along the lines
of...

mfSearching = True
Do while (<somecondition >) And mfSearching
.. doing messages
Doevents
Loop

You need the DoEvents to allow the stop button to be pressed, when
pressed the module/global variable mfSearching is set to false and the
main loop will end natrually.

I've often done similar things and scaled down the scope of the
variables needed by re-using the start button, e.g.

Sub cmdStart_Click( )
Static fRunning As Boolean ' note must be static
if fRunning Then
fRunning=False
cmdStart.Captio n="Stopping"
DoEvents
exit sub
end if
fRunning = True
cmdStart.Captio n="Stop"
Do while <someconditio n> And fRunning
... do something
DoEvents
Loop
cmdStart.Captio n="Start"
End Sub

Note if using a For loop, then insert some code inside the loop like:

if not mfRunning Then
Exit For
End If

The same can be used for do loops as well (using "exit do") in case
there's a lot of things to do each iteration.

--
A)bort, R)etry, I)nfluence with large hammer.

[deko]

Re: How to stop running sub routine?

> Note if using a For loop, then insert some code inside the loop like:[color=blue]
>
> if not mfRunning Then
> Exit For
> End If[/color]

It's a "For Each ... Next" loop.

so... the code to add is:

Dim mfRunning as Boolean

then inside the For Each loop:

if not mfRunning Then
Exit For
End If

and on the cmdStart_Click button, something like this:

Sub cmdStart_Click( )
Static fRunning As Boolean
if fRunning Then
fRunning=False
cmdStart.Captio n="Stopping"
DoEvents
exit sub
end if
fRunning = True
cmdStart.Captio n="Stop"
For Each ...
... do something
DoEvents
Next
cmdStart.Captio n="Start"
End Sub

I'll have to play around with this a bit... thanks for pointing me in the
right direction...

[Roger]

Re: How to stop running sub routine?

at the top of your form's code, add
dim blnStop as boolean

before starting your search
blnStop = false

the 'click' event of your STOP button
blnStop = true

in your search loop
if (blnstop) then
exit sub
end if

.....

create a table called tblMessagesFoun d with the fields that you want
to see in your datasheet

before running the search
delete * from tblMessagesFoun d

while search, add new messages to the table
dim rs as recordset

set rs = currentdb.openr ecordset("tblMe ssagesFound")
your loop
if something found
rs.addNew
rs!message = ......
rs.update
end if
end of your loop

rs.close
set rs = nothing


create a datasheet form with tblMessagesFoun d as the source
"deko" <dje422@hotmail .com> wrote in message news:<007Mb.939 1$ca4.8862@news svr25.news.prod igy.com>...[color=blue]
> I have a sub routine that searches the Outlook PST for a message sent
> to/from a particular email address. Obviously, this can take a long time
> when the PST contains thousands of messages. I'd like to put a button on my
> Access 20003 form that will stop the sub routine when clicked. I know I can
> press Ctl + Break and stop the code, but is there a more graceful way to do
> this programmaticall y? Should I use SendKeys?
>
> Also, it would be nice to have the datasheet (that shows the results of the
> email search) populate with the found message headers as they are found -
> I'm still thinking about how to do this.... any thoughts/suggestions are
> very welcome!!
>
> thanks![/color]

[Hans]

Re: How to stop running sub routine?

No,

use "DoEvents" (see helpfile for details)

Hans Heezemans

https://www.iservi.com

"deko" <dje422@hotmail .com> schreef in bericht
news:007Mb.9391 $ca4.8862@newss vr25.news.prodi gy.com...[color=blue]
> I have a sub routine that searches the Outlook PST for a message sent
> to/from a particular email address. Obviously, this can take a long time
> when the PST contains thousands of messages. I'd like to put a button on[/color]
my[color=blue]
> Access 20003 form that will stop the sub routine when clicked. I know I[/color]
can[color=blue]
> press Ctl + Break and stop the code, but is there a more graceful way to[/color]
do[color=blue]
> this programmaticall y? Should I use SendKeys?
>
> Also, it would be nice to have the datasheet (that shows the results of[/color]
the[color=blue]
> email search) populate with the found message headers as they are found -
> I'm still thinking about how to do this.... any thoughts/suggestions are
> very welcome!!
>
> thanks!
>
>[/color]

[deko]

Re: How to stop running sub routine?

Here's what I've tried... (not working, but getting close, I think...)

This code is in a standard module. The form with the buttons is
frmEmailMessage s.

Public Sub SearchPST()
Dim blnStop As Boolean
If Forms!frmEmailM essages!cmdStar tSearch.Caption = "Stop Search" Then
blnStop = True
Else
blnStop = False
End If
[code omitted]
Do Until rst.EOF
Forms!frmEmailM essages!cmdStar tSearch.Caption = "Stop Search"
For Each olmi In olFolder.Items
DoEvents ' note: DoEvents must be here - can't be in the "Do
.... Loop" or other code will not work.
If (blnStop) Then GoTo Stop_Here
[code omitted]
Next
Loop
Stop_Here:
Forms!frmEmailM essages!cmdStar tSearch.Caption = "Start Search"
rst.Close
Set olmi = Nothing
Set olFolder = Nothing
End Sub

I was hoping I could use the "If ... Else" statement to toggle blnStop - but
clicking on cmdStartSearch (with caption "Stop Search") does not stop the
routine. For some reason the second time I click the button, the event is
not processed. I'm wondering if I need to put the code in frmEmailMessage s
rather than in a separate module ???

[deko]

Re: How to stop running sub routine?


Here's what I've tried... (not working, but getting close, I think...)

*** I've also tried using a sepatare cmdStop button (which I think is what I
want to do), but that did not work either....
I must still be missing something....

This code is in a standard module. The form with the buttons is
frmEmailMessage s.

Public Sub SearchPST()
Dim blnStop As Boolean 'also tried Static blnStop here but no luck...
If Forms!frmEmailM essages!cmdStar tSearch.Caption = "Stop Search" Then
blnStop = True
Else
blnStop = False
End If
[code omitted]
Do Until rst.EOF
Forms!frmEmailM essages!cmdStar tSearch.Caption = "Stop Search"
For Each olmi In olFolder.Items
DoEvents ' note: DoEvents must be here - can't be in the "Do
.... Loop" or other code will not work.
If (blnStop) Then GoTo Stop_Here
[code omitted]
Next
Loop
Stop_Here:
Forms!frmEmailM essages!cmdStar tSearch.Caption = "Start Search"
rst.Close
Set olmi = Nothing
Set olFolder = Nothing
End Sub

I was hoping I could use the "If ... Else" statement to toggle blnStop - but
clicking on cmdStartSearch (with caption "Stop Search") does not stop the
routine. For some reason the second time I click the button, the event is
not processed. I'm wondering if I need to put the code in frmEmailMessage s
rather than in a separate module ???

[Trevor Best]

Re: How to stop running sub routine?

On Sun, 11 Jan 2004 22:30:25 GMT in comp.databases. ms-access, "deko"
<dje422@hotmail .com> wrote:
[color=blue]
>Here's what I've tried... (not working, but getting close, I think...)
>
>This code is in a standard module. The form with the buttons is
>frmEmailMessag es.
>
>Public Sub SearchPST()
> Dim blnStop As Boolean
> If Forms!frmEmailM essages!cmdStar tSearch.Caption = "Stop Search" Then
> blnStop = True
> Else
> blnStop = False
> End If
> [code omitted]
> Do Until rst.EOF
> Forms!frmEmailM essages!cmdStar tSearch.Caption = "Stop Search"
> For Each olmi In olFolder.Items
> DoEvents ' note: DoEvents must be here - can't be in the "Do
>... Loop" or other code will not work.
> If (blnStop) Then GoTo Stop_Here
> [code omitted]
> Next
> Loop
>Stop_Here:
> Forms!frmEmailM essages!cmdStar tSearch.Caption = "Start Search"
> rst.Close
> Set olmi = Nothing
> Set olFolder = Nothing
>End Sub
>
>I was hoping I could use the "If ... Else" statement to toggle blnStop - but
>clicking on cmdStartSearch (with caption "Stop Search") does not stop the
>routine. For some reason the second time I click the button, the event is
>not processed. I'm wondering if I need to put the code in frmEmailMessage s
>rather than in a separate module ???[/color]

Your code is re-entrant, basically when you press the button a second
time to stop it, it runs through the procedure again but exits out of
the loop straight away, the original call to the routine carries on
regardless. You should make blnStop a static variable and exit the sub
when you set it to true, the original call will then exit the loop.

You can also avoid the use of the dreaded "goto"...
blnStop = False ' <Note[1]
Do Until rst.EOF or blnStop
For Each ...
If blnStop Then
Exit For
end if
Next
rst.Movenext
Loop

[1] You need to set this to false prior to your loop as well as
exiting the sub before that when setting it to true otherwise once you
have pressed your stop button, the code will never run again.

--
A)bort, R)etry, I)nfluence with large hammer.

[deko]

Re: How to stop running sub routine?

okay, here's the latest - but the problem still seems to be setting blnStop
to True - the cmdStop button just doesn't seem to do it... do I have to put
the code from modOutlook into frmEmailMessage s?
thanks again for the help!

[modOutlook] - a standard module

Public Sub SearchPST()
Static blnStop As Boolean
blnStop = False
[code omitted]
Do Until rst.EOF or blnStop
For Each olmi In olFolder.Items
DoEvents
If (blnStop) Then Exit For
[code omitted]
Next
Loop
Stop_Here:
rst.Close
Set olmi = Nothing
Set olFolder = Nothing
End Sub

[frmEmailMessage s] - a popup form

Option Compare Database
Option Explicit
Public blnStop As Boolean

Private Sub cmdStop_Click() 'note [1]
blnStop = True
End Sub

note [1] this is not the same button that launches searchPST, it's in a
separate form module

[deko]

Re: How to stop running sub routine?

> Your code is re-entrant, basically when you press the button a second[color=blue]
> time to stop it, it runs through the procedure again but exits out of
> the loop straight away, the original call to the routine carries on
> regardless. You should make blnStop a static variable and exit the sub
> when you set it to true, the original call will then exit the loop.[/color]

This seems to be working... but I think I will have to use GoTo because
there is some code that fires (that I don't want to fire) when the sub is
called from cmdStop. Thanks again for the help!


[frmEmailMessage s] - the form with the controls

Private Sub cmdStart()
modOutlook.Sear chPST (False)
End Sub

Private Sub cmdStop_Click()
modOutlook.Sear chPST (True)
End Sub

[modOutlook] - a standard module with the following code

Public Sub SearchPST (blnStop as Boolean)
[code omitted]
Do Until rst.EOF or blnStop
DoEvents <<== this is needed here
For Each olmi In olFolder.Items
DoEvents <<== as well as here
If (blnStop) Then Exit For
[code omitted]
Next
Loop
Stop_Here:
rst.Close
Set olmi = Nothing
Set olFolder = Nothing
End Sub

[deko]

Re: How to stop running sub routine?

Actually this is NOT working. But at least now I know that I'm toggling
blnStop in the sub...


code on [frmEmailMessage s] - the form with the controls

Private Sub cmdStart()
modOutlook.Sear chPST (False)
End Sub

Private Sub cmdStop_Click()
modOutlook.Sear chPST (True)
End Sub




code on [modOutlook] - a standard module with the following code

Public Sub SearchPST (blnStop as Boolean)

Debug.Print blnStop <<== this will return "True" after clicking cmdStop,
but the Do and For loops continue - not sure why...
[code omitted]
Do Until rst.EOF 'I don't think I need "Or blnStop" here
[code omitted]
For Each olmi In olFolder.Items
DoEvents
If (blnStop) Then Exit For 'this shd suffice as a stopping point
[code omitted]
Next
Loop

End Sub

[deko]

Re: How to stop running sub routine? - WORKING CODE

Nth times a charm...

This works, but I'm open to suggestions on how to improve.

code on [frmEmailMessage s] - the form with the controls
Private Sub cmdStart_Click( )
Dim n as string
n = False
modOutlook.Sear chPST (n)
End Sub
Private Sub cmdStop_Click()
Dim n as string
n = True
modOutlook.Sear chPST (n)
End Sub


code on [modOutlook] - a standard module
Public Sub SearchPST (n)
Static blnStop as Boolean
blnStop = n
[code omitted]
Do Until rst.EOF Or (blnStop)
DoEvents
[code omitted]
For Each olmi In olFolder.Items
DoEvents
If (blnStop) Then Exit For
[code omitted]
Next
Loop
End Sub

[Trevor Best]

Re: How to stop running sub routine?

On Mon, 12 Jan 2004 01:32:12 GMT in comp.databases. ms-access, "deko"
<dje422@hotmail .com> wrote:
[color=blue]
>
>Private Sub cmdStop_Click() 'note [1]
> blnStop = True
>End Sub
>[/color]

This doesn't do anything to the static variable in SearchPST(), you
appear to have created two variables here.

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

Re: How to stop running sub routine?

On Mon, 12 Jan 2004 04:42:05 GMT in comp.databases. ms-access, "deko"
<dje422@hotmail .com> wrote:
[color=blue]
>Actually this is NOT working. But at least now I know that I'm toggling
>blnStop in the sub...
>
>
>code on [frmEmailMessage s] - the form with the controls
>
>Private Sub cmdStart()
> modOutlook.Sear chPST (False)
>End Sub
>
>Private Sub cmdStop_Click()
> modOutlook.Sear chPST (True)
>End Sub
>
>
>
>
>code on [modOutlook] - a standard module with the following code
>
>Public Sub SearchPST (blnStop as Boolean)
>
> Debug.Print blnStop <<== this will return "True" after clicking cmdStop,
>but the Do and For loops continue - not sure why...
> [code omitted]
> Do Until rst.EOF 'I don't think I need "Or blnStop" here
> [code omitted]
> For Each olmi In olFolder.Items
> DoEvents
> If (blnStop) Then Exit For 'this shd suffice as a stopping point
> [code omitted]
> Next
> Loop
>
>End Sub
>
>[/color]

Now you've removed "or blnStop" from the do loop, when you set it to
true, it will still loop around the recordset.

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

Re: How to stop running sub routine? - WORKING CODE

On Mon, 12 Jan 2004 07:02:30 GMT in comp.databases. ms-access, "deko"
<dje422@hotmail .com> wrote:
[color=blue]
>Nth times a charm...
>
>This works, but I'm open to suggestions on how to improve.
>
>code on [frmEmailMessage s] - the form with the controls
>Private Sub cmdStart_Click( )
> Dim n as string
> n = False
> modOutlook.Sear chPST (n)
>End Sub
>Private Sub cmdStop_Click()
> Dim n as string
> n = True
> modOutlook.Sear chPST (n)
>End Sub
>
>
>code on [modOutlook] - a standard module
>Public Sub SearchPST (n)
> Static blnStop as Boolean
> blnStop = n
> [code omitted]
> Do Until rst.EOF Or (blnStop)
> DoEvents
> [code omitted]
> For Each olmi In olFolder.Items
> DoEvents
> If (blnStop) Then Exit For
> [code omitted]
> Next
> Loop
>End Sub
>[/color]

Why the string variables to hold boolean values?

--
A)bort, R)etry, I)nfluence with large hammer.