Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Interrupt a loop using Toggle Button? (2003)

    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?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Interrupt a loop using Toggle Button? (2003)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Interrupt a loop using Toggle Button? (2003)

    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!
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Interrupt a loop using Toggle Button? (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •