Instantiating a pause button

kez1304 · #1 (**permalink**) 07-08-11, 04:57

Evening all, once more...

I have another quick query that I'm struggling to find any answers to using our good friend Google.

I have a loop, that looks a little like:

Code:

Do While Not rs.EOF
        code here...

Where rs = some recordset.

Now, what ideally I'd like, is for at some point during the 'code here...' bit, for a MsgBox to appear, the user clicks 'Ok', then the script pauses until a resume button has been pressed.

All the examples I can find revolve around using a timer, which isn't any good, as the amount of time required will be unknown, thus the resume button.

It's basically going to pause the script so that the user can check over all the details it's retrieved, which on occassion could be 100+ records, other times 2, so assigning a 3 minute wait time isn't really the way to go.

Any ideas, suggestions, experiences? Please share!

Thanks a bunch.

Sinndho · #2 (**permalink**) 07-08-11, 06:53

I cannot imagine any good reason why someone would want such a thing, but here's a possible solution:

1. In the declarations section of the module of the Form where the code is located insert this line:

Code:

Private m_booWait As Boolean

2. Create an Event handler for the Click event of each buttons (Command_Pause and Command_Resume):

Code:

Private Sub Command_Pause_Click()

    m_booWait = True
    
End Sub

Private Sub Command_Resume_Click()

    m_booWait = False
    
End Sub

3. Modify the loop, so that it looks like this:

Code:

    With rst
        Do Until .EOF
            ' Do something
            Do While m_booWait = True
                DoEvents
            Loop
            DoEvents
            .MoveNext
        Loop
        .Close
    End With

If the loop is not in the module of the Form that contains the 2 buttons, m_booWait must be declared in the declarations section of an independant module, like this:

Code:

Public m_booWait As Boolean

kez1304 · #3 (**permalink**) 07-08-11, 07:13

Thanks for the reply buddy.

I'll give it a try shortly and let you know the results.

I want the button because there is a button to loop through a table imported through an ODBC. The data is then manipulated (things like changing the quantity field from 5, to 1, and creating 4 cloned records), and appended, according to an order number, to the main database table.

Because orders can change, retaining the same order number, but have products cancelled/added/amended/etc., I need to check with the user that they are happy to overwrite all data for the existing order stored in the main table.

In order for the user to check that everything seems correct, they need to review the records in question, both from the ODBC import table and the main table. This is setup with two list boxes, one populated with the existing order, the other with the proposed ammendment.

Problem is, unless there's a pause, the user doesn't get a chance to review the proposed update. So with a pause after each order number being imported, the user can freely scroll the list boxes, check everything seems in order, hit the resume button, and carry on with the import/update process.

Does that make sense? If you'd do it differently, I welcome your 2 cents on the situation. Just seemed suitable to me is all.

Internet Services

Web Development

Digital Marketing

Consumer Tech