Solved

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

Posted on 2008-10-10
16
410 Views
Last Modified: 2013-11-29
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
Comment
Question by:possmer
  • 7
  • 7
  • 2
16 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22689516
what are the codes behind the Cancel button?
0
 

Author Comment

by:possmer
ID: 22689540
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22689584
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
 

Author Comment

by:possmer
ID: 22689738
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22689795
frozen??? not good.... design

if that is the case, just use ctrl + pausebreak  key
0
 

Author Comment

by:possmer
ID: 22689961
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22690021
are the forms modal or popup?
0
 

Author Comment

by:possmer
ID: 22690093
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22690125
is it locked? enabled?
0
 

Author Comment

by:possmer
ID: 22690226
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22690570
how are you opening the second form from the main form?
0
 

Author Comment

by:possmer
ID: 22690641
Inside of the main form, main function, I have the following code to open the "secondary" form:

DoCmd.OpenForm "frmProgressBar"
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22690696
where is the main function located?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 22691969
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
 

Author Closing Comment

by:possmer
ID: 31505136
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22708351
;-)

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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now