Interrupt a loop using Toggle Button? (2003)

Topic

New Reply

The VBA program has a For…Next loop that takes a long time to execute, and I wanted to provide a way to interrupt the execution. So I changed the command button that initiates the loop into a toggle button, and used its Click event to initiate the loop. Then I tested for for the state of the Toggle button within the loop:

IF tglDoIt.Value = False Then Exit Sub

Works fine for initiating the loop, but when I click the Toggle button as the code is executing (the loop includes DoEvents), Access issues the following error message:
the macro of function set to the beforeupdate or validationRule property for this field is preventing …

There is nothing in BeforeUpdate nor is there a validationRule. The Toggle button is unbound.

Not sure exactly why the message appears… perhaps not being allowed to re-start the routine before it has properly finished the first time? So I changed the control back to a command button, and used its Caption to indicate whether the loop was running or not. However, I felt it was an awkward workaround, and wondered whether there is a way to use a Toggle button to initiate and interrupt a loop without receiving the Access error message?

Reply | Quote

Replies

I think there must be something else interfering. I tested it in a new form containing only a command button that executes a lengthy For … Next loop and a toggle button. Clicking the toggle button ended the loop without error messages.

By the way, I’d use Exit For instead of Exiit Sub, so that any housekeeping after the Next statement is executed:

If tglDoIt.Value = False Then Exit For

If you can’t find what cauaes the problem, I fear you’ll either have to remove elements from the form to see when the problem disappears, or build a new one from scratch.

Reply | Quote

Thanks, Hans, but I was not clear enough in my initial posting. I am trying to use just one control as “click to start, click to stop” and have the toggle status indicate its current state. I created a new MDB with one form and a single toggle button containing this code:

Option Compare Database
Option Explicit

Private Sub tglDoIt_Click()
Dim i As Long
For i = 1 To 10000
If tglDoIt = False Then Exit For
Application.SysCmd acSysCmdSetStatus, Str(i)
DoEvents
Next
Application.SysCmd (acSysCmdClearStatus)
tglDoIt = False
End Sub

Could not get it to work, and neither could I get unbound textboxes to update their contents while the code was executing. Then I recalled that I have successfully used Slider controls during code execution, but Access does not contain a slider control. Looked and found the Forms 2.0 ToggleButton, and it does exactly what I want. Not sure why the Forms ToggleButton can be updated “dynamically” while the Access ToggleButton cannot be updated during code execution, but that seems to be the way it is…

So the choice seems to be to use the caption property of a command button to indicate the state, or to use a Microsoft Forms 2.0 ToggleButton.

Thanks for your help. It spurred me to look deeper!

Reply | Quote

I think the problem is that clicking the toggle button while the code is running makes On Click event to fire again. This causes a conflict.
MS Forms controls handle events quite differently than Access controls.

Reply | Quote