How can user cancel routine cycling thru updating records? 1

[VicM]

Hi,

I have an import routine initiated with a form. The form has a text box which points to external data that is required to update a table in the DB. The form has two buttons: a Cancel button and an Import button.

The import data is usually greater than 3500 records with multiple fields that need to be checked for change, so this routine can take some time as a function of processor speed. (On my new laptop it might take about 10-15 minutes; while on the user's older desktop it can take upwards of half-hour or so.)

If the Cancel button on the form is clicked before the routine is started, the form closes without executing the routine. When the Import button is clicked, the OnClick routine executes and initially a Msgbox is presented to the user warning of possible extended time required for the routine. The user can select Cancel from the Msgbox window (not the form), and the routine is not started.

However, I'd like the user to have the option of canceling the routine at anytime during the import process. (Part of the process updates a field on the form telling the user the record number of the total being processed.) The problem is once the routine is cycling through the data, the Cancel button on the form no longer is polled.

I've also tried to create another form containing only a Cancel button. That form opens on the desktop at the beginning of the import routine, The OnClick routine for the 'Cancel' form is supposed to set a global variable to true. As part of the import routine I check the global variable after each import record is updated. But this button too does not seem to be polled while the import process is running.

The routine itself is very code heavy with many branches and called subroutines. It works as coded very well; but I'd still like to give the user the option to cancel.

Right now, once the routine starts the only thing I can do is call up the Task Manager and cancel the process. This doesn't allow the DB to properly exit and do the required cleanup coded into the routine.

Any suggestions?

Thanks,
Vic

 

[PHV]

Put some DoEvents in the routine ?
Ctrl-Break ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

One way to try it is:

Dim bCancel As Boolean

For i = 1 To 1000000
    ...
    UpdateSomeRecords[blue]
    DoEvents
    If bCancel = True Then
        Exit Do
    End If[/blue]
Next i

DoEvents

Have fun.

---- Andy

 

[Golom]

What Andrzejek said but also

Drop a Button on your form with its visible property set to FALSE.
Then

[blue]' InGeneral Declarations[/blue]
Dim bCancel As Boolean

Sub myButton_Click()
    bCancel = TRUE
End Sub

Sub RunLongProcess()
bCancel = FALSE
myButton.Visible = TRUE
myButton.Caption = "Shut 'er Down"
For i = 1 To 1000000
    ...
    [blue]' UpdateSomeRecords[/blue]
    If i Mod 100 = 0 then
        DoEvents
        If bCancel Then Exit For
    End If
Next i 
myButton.Visible = FALSE
End Sub

 

[VicM]

Guys,

Thank you all for your suggestions. While you all had the same idea, it was Golom's code that I was able to utilize. Works the nuts!!

I was not familiar with the DoEvents function before. Guess I'm never too old to learn something new.

Again, thanks all!

Vic

 

[Golom]

Just a word of caution about DoEvents ...

The reason I did it on every hundredth pass through the For-Loop rather than every pass is that DoEvents allows the system to process other events in the event queue. The operating system usually has a whole bunch of things going on in the background and if you issue a DoEvents too often it can really slow down your processing. Its called "Starving the Loop" in programming jargon.