Cancel button - stop code & close form

[supmktg]

I have built a form that matches fields and then appends records. I want to give the user an out (Cancel button) if they have selected the wrong file or if the process is taking too long.

The OnClick sub of the Begin button checks a bunch of combo boxes to make sure vital fields are selected, then calls a function that appends records from one recordset to another. I want the Cancel button to stop the code that is running. How can I do this?

Thanks,
Sup

 

[pbaldy]

Does the process take long enough to make that practical? I would probably use an "Are you sure" Yes/No message box to confirm before processing, rather than trying to interrupt processing. Most of the time those types of processes go so fast the user wouldn't be able to stop it anyway.

 

[supmktg]

It depends on the import file. Could have 1,000 records or it could have up to 30,000. Not all of the import files will contain all of the importable fields, so the code checks if the field exists before it tries to add it. And the form has a progress bar which slows it down a bit so it could take a minute or two if the file is large.

The user browses for an excel file to import. They could easily grab a file with 30,000 records when they meant to import a file with 1,000. Or, they could grab a file that has the wrong data altogether. I'd like to give them the ability to cancel the process if it is possible.

Is there a way to stop/exit the code once it begins or am I looking for something that doesn't exist?

Thanks,
Sup

 

[pbaldy]

No, it can be done. Search here on DoEvents, which will cause your process to yield control to the OS. That will let you grab the fact that the user clicked on your cancel button. You should find code examples with that search. Fair warning, DoEvents will likely slow down your process.

 

[supmktg]

Paul,

It was as simple as 1-2-3 once I knew where to look!

1) At the top of my module (before the Function) I placed:
Public blnCancel as Boolean

2) After DoEvents (which was in my Loop) I added the code:
If blnCancel = True Then
Exit Function
End If

3) On my form I added a Cancel button with the OnClick event:
blnCancel = True

When I click cancel, it stops my code just as I wanted.

Thanks a bunch,
Sup