• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

Want to create a button to terminate VBA code if the user feels it is taking too long

I have a function in VBA that opens several recordsets, manipulates data, and writes records to a new table.  This process can take several minutes, and I want to give the user the option to cancel the process if they feel it is taking too long.  

I have created a form with a "homemade" progress bar that updates as the main function moves through it's logic.  I have also put a command button on the form containing the progress bar that says "Cancel".  My intention was to use this button to set a flag or perform some other action to stop the main function's execution.  However, when the main function is executing, it will update (repaint) the progress bar, but as the user I cannot click the Cancel button - nothing happens, and the function continues until it is finished.

Why can't I click the cancel button?  The form is "frozen" until the main function is finished.
0
possmer
Asked:
possmer
  • 7
  • 7
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
what are the codes behind the Cancel button?
0
 
possmerAuthor Commented:
All I have behind the cancel button right now is a msgbox to test to see if it was clicked (OnClick).  I can't get the msgbox to pop up, indicating that it was not successfully clicked.
0
 
Rey Obrero (Capricorn1)Commented:
you need to declare a global variable in a regular module

global blCancel as boolean

at the start of your process set the value to false

blCancel=false



somewhere in your process check the value of blCancel

if blCancel =true exit whatever process is executing

___

in the click of cancel button set the value to True

private sub cmdCancel_click()
blCancel=true

end if
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
possmerAuthor Commented:
Thanks for the sample code on how to perform the cancellation, but that's not what I need.  The form that the cancel button resides on is "frozen" until the main function is finished executing.  I need to be able to click the cancel button so that I can perform some sort of cancellation code like you outlined above.

The problem, to reiterate, is that the form containing the cancel button is frozen until the main function completes, so therefore I cannot successfully click the cancel button to execute any code.
0
 
Rey Obrero (Capricorn1)Commented:
frozen??? not good.... design

if that is the case, just use ctrl + pausebreak  key
0
 
possmerAuthor Commented:
Let me try explaining one more time:

I have a "main" form.  When the user clicks a button on this form, a function executes that kicks off a long process of opening recordsets, manipulating data, and inserting data into a new table.  As a part of this main function's execution, it opens a  "secondary" form with a progress bar and a cancel button.  The progress bar is repainted by the main function as it loops.  If the main function is allowed to finish, the secondary form is closed automatically.

However, if the main function is taking too long to complete, I have given the user a cancel button on the secondary form that they can click to stop the main function from executing.  The problem that is occurring is that the user cannot click the cancel button, it is "frozen" while the main function is executing.

I need to find out why the user cannot click the cancel button while the main function is executing.  The user needs to be able to click this button so they can stop the main function from executing if they want to.

I know that I can use ctrl + pausebreak to stop the main function from executing, but that is not an appropriate activity for users to be doing.
0
 
Rey Obrero (Capricorn1)Commented:
are the forms modal or popup?
0
 
possmerAuthor Commented:
The "main" form is not modal or popup.  The "secondary" form with the cancel button I've tried all the combinations of modal and popup.  It doesn't appear to affect the form's behavior - the cancel button is not clickable whether modal & popup = yes or no.
0
 
Rey Obrero (Capricorn1)Commented:
is it locked? enabled?
0
 
possmerAuthor Commented:
The cancel button is enabled.  When I open the "secondary" form on it's own, I can click the cancel button and have it pop up a test msgbox successfully.
0
 
Rey Obrero (Capricorn1)Commented:
how are you opening the second form from the main form?
0
 
possmerAuthor Commented:
Inside of the main form, main function, I have the following code to open the "secondary" form:

DoCmd.OpenForm "frmProgressBar"
0
 
Rey Obrero (Capricorn1)Commented:
where is the main function located?
0
 
Jeffrey CoachmanMIS LiasonCommented:
possmer,

I'll interject here.

What you are asking is NOT commonly done in and Access application.
It can leave your database in an unstable, unpredictable, or unreliable state.
(Regardless of how "Safe" you think your code is or how robust your error handling is.)

If you ever noticed in most applications, what happens is the user clicks a button to perform a potentially time consuming task.
A message box appears that says:
  "This task may take several minutes, are you sure you want to proceed?"
...at which time the user either clicks Yes to continue, or No to cancel.
Done!
That's it.
Plain and simple.
No complex "Detection" code needed.
;-)

This is the standard way of doing this.
Use something like this and all these other issues (Modal, Can't click Cancel, Control-Break, blCancel) disappear.

Any code you write to guarantee a "Smooth Landing" for your code might end up being fairly complex (Look at all the trouble you are going through already, and it is still not satisfactory)

Besides, depending on your code, what you are asking might not even be possible.
:-O

   "Keep it Simple"

Thats my opinion.

;-)

JeffCoachmnan
0
 
possmerAuthor Commented:
Thanks for your insight.  While I think the ability to cancel a long running operation is a practical, reasonable activity to allow a user to perform, I will accept that Access is not capable of handling it easily (if at all).
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)

Yes I agree, the concept is practical, ...however actually writing the hundreds of lines of code to make this a reality, may not be.
;-)

JeffCoachman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now