How do I run VBA code in the background?
Dear experts,
I'm using Access 2003. My frontend and backends are secured mde files.
I have a subroutine that activates when I press a command button on Form1 that creates a new database with all the data from the backend files into an unsecured mdb file. (see Thread #24912990 )
The problem:
The process of creating the new db takes some time. So, I would like to show a progress meter for the user, and allow for the user to cancel the process. I have played with the syscmd(acCmdInitMeter), but this is too subtle for my liking, and doesn't allow the user to cancel.
SO I have created a form (frmProgMeter) that containsa command button (Caption:Cancel) and 2x ActiveX progress meter bars and appropriate labels that are updated as the process runs.
However, as the process runs, I can't click on the cancel button. What I would like to do is have the subroutine run in the background, so that the cancel button can be clicked by the user. My plan was to have a global variable (bActive) set to False when the user clicked Cancel, and then have the subroutine periodically check (afte each step) if bActive is False, and if so, return the various databases back to original state, before closing the frmProgMeter.
I tried DoEvents in a couple of spots (in the subroutine, and in the frmProgMeter_OnOpen event) to no avail.
Not sure what the best way to do this is.
Should I have the CreateDB subroutine part of the button on Form1, or nested in the frmProgMeter code somehwere?
How do I allow the user to cancel the process and capture this easily in code, given that a simple break in code could occur durin any of a number of processes, leaving the application in various unstable states?
Any advice would be great.
I'm using Access 2003. My frontend and backends are secured mde files.
I have a subroutine that activates when I press a command button on Form1 that creates a new database with all the data from the backend files into an unsecured mdb file. (see Thread #24912990 )
The problem:
The process of creating the new db takes some time. So, I would like to show a progress meter for the user, and allow for the user to cancel the process. I have played with the syscmd(acCmdInitMeter), but this is too subtle for my liking, and doesn't allow the user to cancel.
SO I have created a form (frmProgMeter) that containsa command button (Caption:Cancel) and 2x ActiveX progress meter bars and appropriate labels that are updated as the process runs.
However, as the process runs, I can't click on the cancel button. What I would like to do is have the subroutine run in the background, so that the cancel button can be clicked by the user. My plan was to have a global variable (bActive) set to False when the user clicked Cancel, and then have the subroutine periodically check (afte each step) if bActive is False, and if so, return the various databases back to original state, before closing the frmProgMeter.
I tried DoEvents in a couple of spots (in the subroutine, and in the frmProgMeter_OnOpen event) to no avail.
Not sure what the best way to do this is.
Should I have the CreateDB subroutine part of the button on Form1, or nested in the frmProgMeter code somehwere?
How do I allow the user to cancel the process and capture this easily in code, given that a simple break in code could occur durin any of a number of processes, leaving the application in various unstable states?
Any advice would be great.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK.
So I've had a look at the two supplied Progressbars. Unfortunately, neither have a Cancel button.
The one from msdn has a cancel button, and I have already integrated it into my code.
When you update the meter (acbUpdateMeter()), it checks to see whether the cancel button has been pressed and returns True or False depending. This way, I can determine the route of action to take when the Cancel button is pressed. I hvae altered the original form as posted on the msdn website, to include:
1. When the Cancel button is pressed, the progress label changes to "Cancelling..."
2. I have added another label to the form, which meant adding another argument to the acbUpdateMeter function.
The way I have got it to work making the Cancel button click-able 100% of the time, is by opening a new instance of Access and exporting the Progress meter form (and associated modules) to a temp file. I then call the functions from that instance of access. As stated above, this works perfectly, except that when in an mde file, you can't export forms or code. I could simply have an extra mde file on the server waiting to be downloaded and accessed for this purpose - after all, this subroutine won't be clicked that often! - which will save me the hasle of trying to recrete the form exactly as in original via code.
However, having said that, I'm still interested in the DoEvents solution. Peter57r suggested that the DoEvents can do the job I ask, but I need to make sure I place it in the correct location. I have supplied some sample code from my routine (calling the acbUpdateMeter function as if it were in the current file). If anyone is able to suggest where to put the DoEvents in my code to make it work, I would be most grateful.
[ Explanation of code: This is part of the whole. The first time I call the acbUpdateMeter fn in this code, the second argument updates a label on the meter saying what step of the process we're up to. I use 1 as the starting percentage done, because if I feed 0 to the update function, the label shows "%" only and not "0%". The second time the function is called, I supply only the percentage done.]
So I've had a look at the two supplied Progressbars. Unfortunately, neither have a Cancel button.
The one from msdn has a cancel button, and I have already integrated it into my code.
When you update the meter (acbUpdateMeter()), it checks to see whether the cancel button has been pressed and returns True or False depending. This way, I can determine the route of action to take when the Cancel button is pressed. I hvae altered the original form as posted on the msdn website, to include:
1. When the Cancel button is pressed, the progress label changes to "Cancelling..."
2. I have added another label to the form, which meant adding another argument to the acbUpdateMeter function.
The way I have got it to work making the Cancel button click-able 100% of the time, is by opening a new instance of Access and exporting the Progress meter form (and associated modules) to a temp file. I then call the functions from that instance of access. As stated above, this works perfectly, except that when in an mde file, you can't export forms or code. I could simply have an extra mde file on the server waiting to be downloaded and accessed for this purpose - after all, this subroutine won't be clicked that often! - which will save me the hasle of trying to recrete the form exactly as in original via code.
However, having said that, I'm still interested in the DoEvents solution. Peter57r suggested that the DoEvents can do the job I ask, but I need to make sure I place it in the correct location. I have supplied some sample code from my routine (calling the acbUpdateMeter function as if it were in the current file). If anyone is able to suggest where to put the DoEvents in my code to make it work, I would be most grateful.
[ Explanation of code: This is part of the whole. The first time I call the acbUpdateMeter fn in this code, the second argument updates a label on the meter saying what step of the process we're up to. I use 1 as the starting percentage done, because if I feed 0 to the update function, the label shows "%" only and not "0%". The second time the function is called, I supply only the percentage done.]
' Transfer all reports
With CurrentProject.AllReports
intCounter = intCounter + 1
If Not acbUpdateMeter(1, conStep(intCounter)) Then GoTo Cancelled
iTot = .Count
For j = 0 To iTot - 1
Set obj = .Item(j)
DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acReport, obj.Name, obj.Name
If Not acbUpdateMeter(((j + 1) / iTot * 100)) Then GoTo Cancelled
Next j
End WithASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
After Peter's response, I came across the Progress bar at this site:
http://msdn.microsoft.com/en-us/library/aa188220(office.10).aspx
which doesn't use activeX. Even though it claims to have the Cancel button click-able during processing of other code, it isn't. Maybe I'm doing something wrong. I cam accross another gliche - if you chose the Cancel button NOT visible argument, the form won't load, because the cancel button is the only control present that can receive the focus!. I created a work around for that, but I'm afraid I'm at a loss as to how DoEvents can help me.
I have howver come up with a solution that works - but only for an mdb file - which my production progrom won't be, so I'm still looking for a good solution. I shall look at LSM's and Jim's zip files to see if they can help.
drjdbb