How can I pause and resume a loop with a button press?

E

efandango

I have a simple code routine that works its way through some setvalue lines.
I want to be able to pause the loop at any time that a buton is pressed, and
resume the code when it is pressed again. Can this be done?

If I have to use two buttons, then that will be ok, though I would really
like to get away with using just the one button for this function.
 
A

Arvin Meyer MVP

The only way that you can pause code execution is within the procedure you
are running. An external button won't work. You can use Ctrl+Break to pause
code, but not restart it.

Usually code runs so fast that you couldn't stop it anyway, without setting
an internal breakpoint.
 
S

Stefan Hoffmann

hi,
I have a simple code routine that works its way through some setvalue lines.
I want to be able to pause the loop at any time that a buton is pressed, and
resume the code when it is pressed again. Can this be done?
Click to expand...
You can use a logic like this:

Option Compare Database
Option Explicit

Private LoopCount As Long
Private LoopContinue As Boolean

Private Sub cmdPause_Click()

LoopContinue = False

End Sub

Private Sub cmdResume_Click()

LoopContinue = True
DoLoop

End Sub

Private Sub cmdStart_Click()

LoopCount = 0
LoopContinue = True
DoLoop

End Sub

Private Sub DoLoop()

Do While LoopCount < 10000 And LoopContinue
LoopCount = LoopCount + 1
txtLoop.Value = LoopCount
DoEvents
Loop

End Sub


mfG
--> stefan <--
 
E

efandango

Stefan,

thanks for replying to my post. to be honest, I'm a little baffled on how to
implement your suggestion. Am i right in thinking that 3 buttons will be used
for the following 3 routines?

Private Sub cmdPause_Click()
Private Sub cmdResume_Click()
Private Sub cmdStart_Click()


if so, where do I put my own loop code among these?

regards

Eric
 
S

Stefan Hoffmann

hi Eric,
thanks for replying to my post. to be honest, I'm a little baffled on how to
implement your suggestion. Am i right in thinking that 3 buttons will be used
for the following 3 routines?

Private Sub cmdPause_Click()
Private Sub cmdResume_Click()
Private Sub cmdStart_Click() Correct.

if so, where do I put my own loop code among these?
Click to expand...
You don't.

The loop is initialized in cmdStart_Click(). The loop code itself is
implemented in DoLoop() which is called from cmdResume_Click() and
cmdStart_Click().


mfG
--> stefan <--
 
E

efandango

Stefan,

I wasn't entirely sure what do do, but this is what I have so far: If I
don't put my code in, then nothing happens when I click the buttons. If I put
my code in (as below), then my loop works correctly when I click the cmdStart
button, but when I press the pause button once, then nothing happens, when I
press it again then my loop pauses, but if I press the cmdResume button, then
MS Access reports 'not responding'...

This is the complete code I have so far:

********************
Private Sub cmdPause_Click() (this is on a command button called cmdPause)
LoopContinue = False

End Sub

Private Sub cmdResume_Click() (this is on a command button called cmdResume)


LoopContinue = True
DoLoop

End Sub

Private Sub cmdStart_Click() (this is on a command button called cmdStart)

LoopCount = 0
LoopContinue = True
DoLoop

** My Code inserted where I think it should go

On Error GoTo Proc_Err

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb

Set rs = db.OpenRecordset("Select [Run_waypoint] from
tbl_Run_Reveal_Selector where [Run_No]=" & Forms!frm_Runs!Run_No & " order by
[Run_waypoint];", dbOpenForwardOnly)

Do Until rs.EOF


'setfocus
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus

'pastes record from table into target form
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] = Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] = Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext


Call sSleep(Me.cbo_Run_Reveal_Timer)

Loop
rs.Close
Proc_Err:
' MsgBox "Complete"
End Sub

*** My code ends

Private Sub DoLoop()

Do While LoopCount < 10000 And LoopContinue
LoopCount = LoopCount + 1
txtLoop.Value = LoopCount
DoEvents
Loop

End Sub
********************
 
S

Stefan Hoffmann

efandango said:
I wasn't entirely sure what do do, but this is what I have so far: If I
don't put my code in, then nothing happens when I click the buttons. If I put
my code in (as below), then my loop works correctly when I click the cmdStart
button, but when I press the pause button once, then nothing happens, when I
press it again then my loop pauses, but if I press the cmdResume button, then
MS Access reports 'not responding'...
Click to expand...
You have to put your code into the DoLoop method:




mfG
--> stefan <--
 
S

Stefan Hoffmann

hi Eric,
I wasn't entirely sure what do do, but this is what I have so far: If I
don't put my code in, then nothing happens when I click the buttons. If I put
my code in (as below), then my loop works correctly when I click the cmdStart
button, but when I press the pause button once, then nothing happens, when I
press it again then my loop pauses, but if I press the cmdResume button, then
MS Access reports 'not responding'...
Click to expand...
You have to put your code into the DoLoop() method:

Private db As DAO.Database
Private rs As DAO.Recordset

Private Sub cmdStart_Click()

Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT [Run_waypoint] " & _
"FROM tbl_Run_Reveal_Selector " & _
"WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " " & _
"ORDER BY [Run_waypoint];", _
dbOpenForwardOnly)
LoopContinue = True
DoLoop

End Sub

Private Sub DoLoop()

Do While Not rs.EOF And LoopContinue

'setfocus
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus

'pastes record from table into target form
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] = _
Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] = _
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

Call sSleep(Me.cbo_Run_Reveal_Timer)
DoEvents
Loop

End Sub

btw, you can't interrupt the Sleep calls.

mfG
--> stefan <--
 
E

efandango

Stefan,

Now, when I press the 'start' button, I get the error message '424 Object
Required' on this line: Do While Not rs.EOF And LoopContinue


This is now my latest code.


Private Sub cmdStart_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT [Run_waypoint] " & _
"FROM tbl_Run_Reveal_Selector " & _
"WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " " & _
"ORDER BY [Run_waypoint];", _
dbOpenForwardOnly)
LoopContinue = True
DoLoop
End Sub

Private Sub DoLoop()

Do While Not rs.EOF And LoopContinue

'setfocus
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus

'pastes record from table into target form
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

Call sSleep(Me.cbo_Run_Reveal_Timer)
DoEvents
Loop

End Sub
 
S

Stefan Hoffmann

hi Eric,
Now, when I press the 'start' button, I get the error message '424 Object
Required' on this line: Do While Not rs.EOF And LoopContinue

This is now my latest code. [..]
Click to expand...
Read my previous post again. Where did you declare rs?

mfG
--> stefan <--
 
E

efandango

Sorry Stefan, I'm not very experienced at coding rules, and only really
understand this type of thing after it is pointed out to me, so I have
corrected/added the code (from my original button that simply loops)

so now this is my latest code, but when I run it, nothing happens, no
errors, and no loop.


my code:



Private Sub cmdStart_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT [Run_waypoint] " & _
"FROM tbl_Run_Reveal_Selector " & _
"WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " " & _
"ORDER BY [Run_waypoint];", _
dbOpenForwardOnly)
LoopContinue = True
DoLoop
End Sub

Private Sub DoLoop()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer
Set db = CurrentDb

Set rs = db.OpenRecordset("Select [Run_waypoint] from
tbl_Run_Reveal_Selector where [Run_No]=" & Forms!frm_Runs!Run_No & " order by
[Run_waypoint];", dbOpenForwardOnly)

Do While Not rs.EOF And LoopContinue


'setfocus
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus

'pastes record from table into target form
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

Call sSleep(Me.cbo_Run_Reveal_Timer)
DoEvents
Loop

End Sub

Stefan Hoffmann said:
hi Eric,
Now, when I press the 'start' button, I get the error message '424 Object
Required' on this line: Do While Not rs.EOF And LoopContinue

This is now my latest code. [..]
Click to expand...
Read my previous post again. Where did you declare rs?

mfG
--> stefan <--
Click to expand...
 
S

Stefan Hoffmann

hi Eric,
Sorry Stefan, I'm not very experienced at coding rules, and only really
understand this type of thing after it is pointed out to me, so I have
corrected/added the code (from my original button that simply loops)
Click to expand...
You have to declare all needed variables for the loop, which is the code
between Do and Loop, as Private on module level. Not in the DoLoop() nor
cmdStart_Click() method. So at the head of your module place:

Private db As DAO.Database
Private rs As DAO.Recordset

The initialization of the variables are handled in the cmdStart_Click()
method.

Only the "content" code of your loop and the loop statement itself are
placed in the DoLoop() method. And nothing else.


mfG
--> stefan <--
 
E

efandango

Stefan,

I think I have done what you suggested, but when I run the Start button, It
only pastes/shows the first record; but it doesn't loop, and I don't
understand why this is so. I also pasted these two lines right at the very
top of the form module, which look like this:

Option Compare Database
Private db As DAO.Database
Private rs As DAO.Recordset



this is my latest code:



Private Sub cmdPause_Click()
LoopContinue = False
End Sub


Private Sub cmdResume_Click()
LoopContinue = True
DoLoop
End Sub


Private Sub cmdStart_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sRoute As String
Dim iWPCount As Integer

Set db = CurrentDb


Set rs = db.OpenRecordset( _
"SELECT [Run_waypoint] " & _
"FROM tbl_Run_Reveal_Selector " & _
"WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " " & _
"ORDER BY [Run_waypoint];", _
dbOpenForwardOnly)

LoopCount = 0
LoopContinue = True
DoLoop
End Sub



Private Sub DoLoop()
Do While LoopCount < 10000 And LoopContinue
LoopCount = LoopCount + 1
txtLoop.Value = LoopCount
DoEvents
Loop

'setfocus
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus

'pastes record from table into target form
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

Call sSleep(Me.cbo_Run_Reveal_Timer)
DoEvents
'Loop
End Sub
 
S

Stefan Hoffmann

hi Eric,
I think I have done what you suggested, but when I run the Start button, It
only pastes/shows the first record; but it doesn't loop, and I don't
understand why this is so. I also pasted these two lines right at the very
top of the form module, which look like this:
Click to expand...
You code must not only reside in the DoLoop() method, it must be also
between the Do-Loop keywords.
Option Compare Database
Private db As DAO.Database
Private rs As DAO.Recordset
Click to expand...
Add an Option Explicit. Activate under Tools/Options "Variable
declaration necessary" (or so).


mfG
--> stefan <--
 
E

efandango

Stefan

Do you mean paste ***MY CODE HERE*** in these methods like the examples
below; I have pasted the code block in the other two methods, but it only
loops twice. It pastes the first record, then loops once to paste the second
record, and then stops.

Also, when I paste these:

Option Compare Database
Private db As DAO.Database
Private rs As DAO.Recordset

just below Private Sub cmdStart_Click() and run the conpiler it reports that
they are invalid inside procedure.


My latest code


Private Sub cmdPause_Click()
LoopContinue = False
End Sub



Private Sub cmdResume_Click()

LoopContinue = True

DoLoop

***MY CODE HERE***

End Sub



Private Sub cmdStart_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Run_waypoint] " & "FROM
tbl_Run_Reveal_Selector " & "WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " "
& "ORDER BY [Run_waypoint];", dbOpenForwardOnly)
LoopCount = 0
LoopContinue = True

***MY CODE HERE***

DoLoop
End Sub



Private Sub DoLoop()

Option Compare Database
Private db As DAO.Database
Private rs As DAO.Recordset

Do While LoopCount < 10000 And LoopContinue
LoopCount = LoopCount + 1
txtLoop.Value = LoopCount
DoEvents
Loop

'My Code ************************************

Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus


Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

Call sSleep(Me.cbo_Run_Reveal_Timer)
DoEvents

'*******************************************

End Sub




For the sake of simplicity, can you just use a tag to show me where to also
paste my code in the other methods using this template below?

Private Sub cmdPause_Click()
LoopContinue = False
End Sub

Private Sub cmdResume_Click()
LoopContinue = True
DoLoop
End Sub

Private Sub cmdStart_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Run_waypoint] " & "FROM
tbl_Run_Reveal_Selector " & "WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " "
& "ORDER BY [Run_waypoint];", dbOpenForwardOnly)
LoopCount = 0
LoopContinue = True
DoLoop
End Sub

regards

Eric
 
S

Stefan Hoffmann

hi Eric,
Do you mean paste ***MY CODE HERE*** in these methods like the examples
below; I have pasted the code block in the other two methods, but it only
loops twice. It pastes the first record, then loops once to paste the second
record, and then stops.
Click to expand...
Oh, how hard could it be? I've posted it all before:

Option Compare Database
Option Explicit

Private db As DAO.Database
Private rs As DAO.Recordset
Private LoopContinue As Boolean

Private Sub cmdPause_Click()

LoopContinue = False

End Sub

Private Sub cmdResume_Click()

LoopContinue = True
DoLoop

End Sub

Private Sub cmdStart_Click()

Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT [Run_waypoint] " & _
"FROM tbl_Run_Reveal_Selector " & _
"WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " " & _
"ORDER BY [Run_waypoint];", _
dbOpenForwardOnly)
LoopContinue = True
DoLoop

End Sub

Private Sub DoLoop()

Do While Not rs.EOF And LoopContinue

'setfocus
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus

'pastes record from table into target form
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] = _
Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] = _
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

Call sSleep(Me.cbo_Run_Reveal_Timer)
DoEvents
Loop

End Sub


mfG
--> stefan <--
 
E

efandango

Stefan,

firstly apologies if I have frustrated you, it is something I have tried to
avoid doing. I know there is nohing more maddening than someone not
understanding what seems like the blindingly obvious. But that is assuming
the someone always understands the message. In my case, I didn't always
understand exactly what you were saying, when for example you said "You code
must not only reside in the DoLoop() method, it must be also between the
Do-Loop keywords.", I took this to mean that I had to duplicate my code into
the other Methods where DoLoop existed. Coding is not always intuitive for
inexperienced people such as me.

However, I just wanted to say a huge thanks for your help, patience,
Perseverance, because it finally worked, and now I have a fantastic form that
does exactly what I always hoped it would do.


truly grateful,

Eric.


PS: I have deposited 1,000 'happy vibe' karma credits for you with the
Central Bank of Karmania for your immediate withdrawal.

Stefan Hoffmann said:
hi Eric,
Do you mean paste ***MY CODE HERE*** in these methods like the examples
below; I have pasted the code block in the other two methods, but it only
loops twice. It pastes the first record, then loops once to paste the second
record, and then stops.
Click to expand...
Oh, how hard could it be? I've posted it all before:

Option Compare Database
Option Explicit

Private db As DAO.Database
Private rs As DAO.Recordset
Private LoopContinue As Boolean

Private Sub cmdPause_Click()

LoopContinue = False

End Sub

Private Sub cmdResume_Click()

LoopContinue = True
DoLoop

End Sub

Private Sub cmdStart_Click()

Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT [Run_waypoint] " & _
"FROM tbl_Run_Reveal_Selector " & _
"WHERE [Run_No] = " & Forms!frm_Runs!Run_No & " " & _
"ORDER BY [Run_waypoint];", _
dbOpenForwardOnly)
LoopContinue = True
DoLoop

End Sub

Private Sub DoLoop()

Do While Not rs.EOF And LoopContinue

'setfocus
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus

'pastes record from table into target form
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] = _
Me.Run_Direction
Call sSleep(500)

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] = _
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNewRec

Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

Call sSleep(Me.cbo_Run_Reveal_Timer)
DoEvents
Loop

End Sub


mfG
--> stefan <--
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form to LoadingForm to run Queries for Report? 1
Stop / Pause a Macro using Pause button 4
How to pause a loop to insert information and resume in Visual Basic 2008 0
Run Different Reports with Same Button 4
How do i pause a slide in Powerpoint? 3
Pause a Loop statement so computer can cool off. 2
Pause and restart PPt presentation 8
Opening a form as datasheet and dialog will not pause code 1

Top