'Break out' button not accepting clicks while another sub is running (1 Viewer)

[Margarita]

Hello,
I am in access 2003. I have a form on which the user enters numbers into one field and clicks a button called CrossCheck which calculates and auto-populates numbers into other fields based on the values which she has entered and then she verifies that those numbers are what she expects.
I want her to also have the option of breaking out of the cross check before it is complete, in case she realized she entered something wrong and doesn't want for the cross check procedure to finish running before she can change her entry. However, some of my users are not so great with remembering keyboard shortcuts, so I don't want to rely on them remembering Ctrl+ Break. Besides, I like to design things with as many action buttons as possible, so the user never has to feel lost when looking at a form and always knows what to click to perform any given task.

So for this purpose, I have declered a public variable called CrossCheckRan. In the load event of the main form, the variable is set to "Never ran".
At the very end of the CrossCheck procedure, CrossCheckRan is set to "Finished". This value is then used in another sub which is called when the user is ready to save the values she entered and checked (she won't be able to save unless CrossCheck= "Finished").

I have made a button called CrossCheckStop whose on click event is this:

Private Sub CrossCheckStop_Click()
CrossCheckRan = "Broke out"
End Sub

In the Cross check procedure, I have a do while loop, which goes through the records until it reaches the end of the recordset and also checks if CrossCheckStop has been clicked since the last iteration of the loop began, like so:

Do while Me.Nameslist.Form.CurrentRecord < Me.Nameslist.Form.Recordset.RecordCount 
And Not CrossCheckRan = "Broke out"

I assume that this is supposed to check at the beginning of every iteration for the value of CrossCheckStop.

However, I am missing something in my set-up. When I try to click the CrossCheckStop button, it does not accept clicks. That is, it appears flat (the outline of the button does not turn dark) when I click it. That tells me that I can't click it while the CrossCheck code is running, and therefore the value is never set to "Broke out" and the loop of CrossCheck just keeps going until the end.

Also, when I click CrossCheckStop repeatedly 3-4 times, the code freezes and just sits there until I hit Ctrl+Break and then it breaks out successfully. But hitting Ctrl+Break is what I want to avoid to begin with.

Would anyone be able to point out why I can't get my CrossCheckStop button to terminate CrossCheck with just one click and avoid using Ctrl+Break?

Thank you!

 

[vbaInet]

It's a very long post. I've noticed you tend to write long posts

How many minutes does the process take?

 

[Margarita]

It's a very long post. I've noticed you tend to write long posts

How many minutes does the process take?

Errrr..... Guilty! I like to write long. I guess I do it because of my own experience of browsing threads for help. I like when people give detailed explanations of how they set their procedures up- that way anyone reading the thread can use that as inspiration to use in their own applications. If someone just writes two lines about a snippet of code- that doesn't really help anyone browsing the thread looking for ideas. So I like to write out my questions with details so noobies like me can use my set-up for inspiration, even if the thread is not completely resolved. Sometimes the question can provide as much guidance as the answer.
I guess I ake 10-15 minutes per post, on average.

Anyway... yeah. I write too much.

 

[Margarita]

Oh, forgot to answer your question:

The Cross Check procedure take about 4-5 minutes to run because it's a long recordset. I don't want the user sitting there that long if they just want to break out and start the data entry over.

Thanks.

 

[vbaInet]

I tend to click away from long posts unless it's a very complex problem that's hard to describe

I meant, how many minutes does the cross check process take?

 

[Margarita]

I tend to click away from long posts unless it's a very complex problem that's hard to describe

I meant, how many minutes does the cross check process take?

I posted a short follow up when I realized I forgot to anwer the question:
cross check takes about 4-5 minutes, which is not so long really, but it is long to sit there and stare at something that you're just itching to stop.

 

[vbaInet]

Wow... why so long? How many records is it thrawling through? Might be worth asking for optimisation tips for this process in a new thread.

Tell us in bullets points what is involved in the process. Short and sweet please

 

[Margarita]

haha ok this is as short as I can make it:

-checks Employee table for current pay rate for the employee
-checks if rate is not null, if it is, sets to 0
-checks for value of the field WorkTimeType (regular time or overtime)
-If Reg, multiplies the HoursWorked (the field that the user fills in) by payrate
-If OT, multiplies the HoursWorked (the field that the user fills in) by payrate *1.5
-Updates the AmountPaid field in the form and also two other currency fields which are calculated from the AmountPaid field

There are about 40-50 names on each page of the form.
I should say that on my machine, I get this to run in under 1.5 minutes, but the user reports 4-5 on her machine, so that's what I have to go by.

Thanks.

 

[vbaInet]

What you're intending to do is not straightforward and I advise against it. You will be looking at Transactions to get the desired result and this is an advanced topic. With Transactions it gives you the ability to roll back any changes made during a process and it allows you to isolate all processes for a particular session.

 

[spikepl]

Hmmm ... so you want a break-out because the undertaking takes minutes .... Nowadays, things that take minutes are suspect, unless you have a huge volume of data. How many thousands of people are you processing data for? Anything less and then the process is probably suboptimised, and therefore requires the band-aid you are looking for.

 

[Margarita]

What you're intending to do is not straightforward and I advise against it. You will be looking at Transactions to get the desired result and this is an advanced topic. With Transactions it gives you the ability to roll back any changes made during a process and it allows you to isolate all processes for a particular session.

Do you mean that breaking out of the code requires looking at Transactions? I didn't realize that... I thought it was just a simple 'ok, now end this process at whatever point it is at' kind of thing. As in, whatever was updated can stay, and the procedure just won't get to the rest.

If you mean my calculated fields updating is a bad idea- I'm not sure that it's all that complicated. The form Nameslist is based directly on the table Nameslist_for_entry that contains the names and all the fields that Cross Check calculates. What happens during the cross check is these fields get updated directly in the table and the form is reloaded and shows everything that has been committed to this table. Once the user is done looking at the numbers and is ready to hit the save button, the records are committed to the real table where they are supposed to go, the numbers from the table Nameslist_for_entry are flushed and the form is reloaded. It has been working ok for me for some time. All the data entered using this process made it into the table without a problem.

So, just so I'm understanding you correctly, you advise against breaking out of code using buttons? Should I just tell the user to wait it out or use Ctrl+Break if she absolutely needs to?

Thank you!

 

[Margarita]

Hmmm ... so you want a break-out because the undertaking takes minutes .... Nowadays, things that take minutes are suspect, unless you have a huge volume of data. How many thousands of people are you processing data for? Anything less and then the process is probably suboptimised, and therefore requires the band-aid you are looking for.

You may be absolutely right- I could probably make it faster. I just assumed it was being slow because there is a whole sql update action going on at each iteration of the loop.
But I guess the speed issue isn't my main concern (at least right now). I was just trying to get that 'break out' button to work for the user.
Thanks!

 

[vbaInet]

Do you mean that breaking out of the code requires looking at Transactions? I didn't realize that... I thought it was just a simple 'ok, now end this process at whatever point it is at' kind of thing. As in, whatever was updated can stay, and the procedure just won't get to the rest.

As long as you are happy with keeping whatever has been updated then you don't need transactions.

If the operation was in an event that has a Cancel argument, i.e. the likes of Before Update event, you can stop it using the DoCmd.CancelEvent action. Otherwise, you will need to create a global boolean variable and in each section of your code, and when it reaches one of those sections, perform a check like this:

If booleanVariable = True Then Exit Function

So when the user clicks the Pause button on the form, you simply set the boolean variable to False. But remember to reset the variable accordingly.

As for stopping the execution of a query, they key combination is Ctrl + Break but don't count on it if you're running a complex query. If Access is too busy then your attempt to stop a piece of code from running during runtime will be futile because Access will be unresponsive.

 

[Margarita]

As long as you are happy with keeping whatever has been updated then you don't need transactions.

If the operation was in an event that has a Cancel argument, i.e. the likes of Before Update event, you can stop it using the DoCmd.CancelEvent action. Otherwise, you will need to create a global boolean variable and in each section of your code, and when it reaches one of those sections, perform a check like this:

If booleanVariable = True Then Exit Function

So when the user clicks the Pause button on the form, you simply set the boolean variable to False. But remember to reset the variable accordingly.

As for stopping the execution of a query, they key combination is Ctrl + Break but don't count on it if you're running a complex query. If Access is too busy then your attempt to stop a piece of code from running during runtime will be futile because Access will be unresponsive.

I will try out the global variable method right now. Thank you!

 

[vbaInet]

Let us know how it goes.