Error when clicking too fast

[Petr Danes]

you can create a sub/function to Disable/Enable all command buttons:

Public Sub EnableDisableButtonsOnForm(ByRef frm As Form, ByVal Enable As Boolean)
' arnelgp
'
' purpose:
'   Enable/Disable command buttons
'
' note:
' to disable, passed False to Enable parameter, otherwise pass True value
'
    Dim ctl As Control
    For Each ctl In frm.Controls
        With ctl
            If TypeOf ctl Is CommandButton Then
                .Enabled = Enable
            Else
                If TypeOf ctl Is SubForm Then
                    Call EnableDisableButtonsOnForm(ctl.Form, Enable)
                End If
            End If
        End With
    Next
End Sub

to use:

'disable all buttons in the form and subforms
Call EnableDisableButtonsOnForm(Me, False)
' some query or process here
'
'
'enable them again when done processing
Call EnableDisableButtonsOnForm(Me, True)
'

Thanks - yes, that would work. I would have to make it a bit more complicated, because I have toggles, embedded subform and other things on the form, and some of them are already disabled for various reasons, so I would have to keep track of every control's status before calling this routine and put back only the ones that were supposed to be enabled. But the principle is sound - if I can't get anything more elegant to work, I'll give this a try.

 

[Petr Danes]

you can try it, if it flicker then just forget the suggestion.
on my test db, flicker did not happen.

And now that I think about it, I wonder if I could just disable the entire form, and that would cover everything in one command. I'm not at work for a few days now, but I have several things to test when I get back. This is going to be interesting.

 

[The_Doc_Man]

you can't interrupt a stuck loop with a mouse click on the VBA Stop execution button, unless you have a DoEvents line somewhere in the loop. So some mouse clicks get ignored, although there is no wait in such a code loop

Or if you are in a loop, you MUST be in an event that another event cannot interrupt (unless you have a DoEvents somewhere). So if you eventually get in something to stop the code, your mouse clicks in that context aren't ignored; they are flushed. A "stop code" isn't selective. It isn't "stop one." It is "stop all."

But submitting a pass-through query and waiting for the results isn't really returning from that process, is it?

Having pointed out that Access code isn't open-sourced, I can't answer that from certain knowledge. However, from the observed behavior in question, if you CAN click a button and have it respond immediately, I would say you must have returned from the event.

Do you mean if I launched a custom event to do the SQL Server call, and had THAT code waiting for the response, my code would then be immune to user clicks?

I think that is the case. Your question about getting stuck in a loop suggests exactly that behavior.

it seems to me you can have processes under as many forms as you have open, just not executing simultaneously. Or am I missing your point completely?

Missing the point. They won't be executing simultaneously - but there is only one event queue and if you have more than one event source, you won't be easily able to predict which event comes next. Further, most event routines are relatively short. Therefore if two forms are going through state changes at once, their events might intertwine. For instance, launch two forms (not sub-forms of a main, but two independent forms in parallel). They will both open. Once they open, they are on their own (since they aren't sub-forms.) They can have OPEN, LOAD, CURRENT, and other events depending on what they do. When an event routine executes that EXIT SUB (or the implied EXIT SUB found in an END SUB), the form is doing the same thing more or less as a DoEvents would do. Which means if the other form still has events pending, it's off to the races. They won't run in parallel, but then if you think about time-sliced time-sharing, that doesn't run things in parallel either - but it sure seems like it does.

I don't even know what a TRAP routine is, unless I know it by another name and am not aware of that.

Try "Error Handler." An error routine based on the ON ERROR THEN xxxx syntax represents an interrupt. When you get a "memory usage violation" or an "out of memory" error or an "arithmetic overflow" error, those are often detected in the hardware via a mechanism called a TRAP, which is like an interrupt in some cases but not in other cases. Traps also use a queue but that queue is separate from event queues. Traps fly by very different rules. I mentioned it as a precaution to clarify that when we are talking about "one event can't interrupt another event" that in fact an event routine CAN be interrupted by something else - in this specific discussion, a trap sequence.

 

[MajP]

Stupid question here, but why not a simple pop up progress meter that is modal dialog? I would think that is kind of the standard way to do this.

 

[Minty]

I don't think you can, without running the query from the modal form, as code execution in the calling form is halted until the modal form is closed.

 

[Petr Danes]

Stupid question here, but why not a simple pop up progress meter that is modal dialog? I would think that is kind of the standard way to do this.

Not at all a stupid question. That would work to block clicks, but the problem with a progress meter is that I have no way of knowing how far along SQL Server is in the job. These are pass-through queries - I build them, submit them and wait for the results to come back. They generally come back within at most a second or two, usually much quicker, which is how the users have gotten in the habit of not waiting, and just clicking the next thing they want. I have a simple background color change from green to yellow as a 'caution light' to indicate that something is going on, and back to green when it's done, but as usual, people eventually get used to how things work and completely lose awareness of the visual signal. They are used to being able to click on what they want, when they want, and the occasional instance when it lags is infrequent enough that the color change simply doesn't register.

And of course, it also varies by user. Some are more careful, some are headlong in their work and pay little attention to anything else. It does no good to chastise them, and that's not how I see my job anyway. My task is to make the software work how they need it to work, and that includes making it immune to their screwups, as much as I possibly can.

I do use progress meters when my code is doing some more extended operations, and that works nicely, but I can't use it to show progress on a simple query that is only a single statement - QueryDef("xxx").OpenRecordset.

 

[Petr Danes]

I don't think you can, without running the query from the modal form, as code execution in the calling form is halted until the modal form is closed.

Not quite - as Pat pointed out a few posts back (and I had spaced over when I wrote my own objection to it), a modal form halts all execution OTHER THAN in the modal form. The modal form blocks all other user activity, but its own code continues to run. It can make the query call, and wait without fear of being interrupted, then close itself when the recordset materializes, all without any user interaction. Of course, if the query hangs or something else goes wrong inside, you have no way to get control back and you have to kill Access .

 

[The_Doc_Man]

These are pass-through queries - I build them, submit them and wait for the results to come back.

This is essentially asynchronous. You trigger the query but then have to wait for it. The catch is, you don't HAVE to wait because once the query has been sent to the back-end, you are DONE. When you were denying earlier that you had done anything asynchronously, you were glossing over this little fact. A pass-thru query is handled by the BE server REGARDLESS of what you do next.

Using the ACE database engine, the "handshake" keeps everything synchronous because (as I understand it) the handshake between client and server involves a protocol that is of the "prompt/response" type (some call it "challenge/response") like working with a serial terminal interacting with a command-line based server. You get nothing from the server until you type something to trigger it.

But with a pass-thru query, that query is now an independent process. If you consider what you do in the front-end AFTER the query has been submitted, there is where you would put something to keep the event code in play. And if you don't, if you reach an EXIT SUB or END SUB, your code just became asynchronous (with respect to the overall process that is now running on TWO machines - the client and the server.)

 

[Petr Danes]

This is essentially asynchronous. You trigger the query but then have to wait for it.

Well - yes. That is what makes it NOT asynchronous - I'm waiting. An asynchronous process would be one that I send off and then continue on to do something else. I don't, and in fact, I don't even know how I would do that. I'm sure I could find out with a bit of digging (a property in the pass-through query?), but I've never had occasion to do something like that. All my interactions with the engine are done this way. The fact that I COULD make an asynchronous process is another matter.

The catch is, you don't HAVE to wait because once the query has been sent to the back-end, you are DONE. When you were denying earlier that you had done anything asynchronously, you were glossing over this little fact. A pass-thru query is handled by the BE server REGARDLESS of what you do next.

I understand that. Again, I COULD make this asynchronous by not waiting, but I'm not - I AM waiting.

But with a pass-thru query, that query is now an independent process. If you consider what you do in the front-end AFTER the query has been submitted, there is where you would put something to keep the event code in play. And if you don't, if you reach an EXIT SUB or END SUB, your code just became asynchronous (with respect to the overall process that is now running on TWO machines - the client and the server.)

I really don't know what else to say about this. Yes, I could go on. I don't. I wait until I get back the records I requested. I know that the code is waiting because I single-step it during trouble-shooting, and I have graphic events that take place immediately after the call. Single stepping does NOT continue after submitting the query - it waits until the query completes before I even have the option of executing the next line of code. And when I run it normally, none of the graphic things happen until after the records are delivered.

Asynchronous means the two process are running on their own, with no regard for each other. Synchronous processes communicate and take each other into account. THAT is what I am doing. SQL Server sits and waits. My Access app submits a query. SQL Server accepts that query and gets to work on it. Access sits and waits until SQL Server notifies it that it now has the requested records, here they are. Access takes those records and resumes what it was doing. SQL Server goes back into standby mode. That is exactly a synchronous process - two tasks communicating with each other, and each waiting until the other finishes and announces that it is finished before resuming its own work.

Although, if I'm going to split hairs, there is a slight bit of asynchronicity. Access gets back to work as soon as it has at least one record, whether from SQL Server or it's own internal JET engine. In the background, the engine continues delivering records until all have been returned, but Access does not wait for the entire recordset before resuming processing.

 

[The_Doc_Man]

Although, if I'm going to split hairs, there is a slight bit of asynchronicity. Access gets back to work as soon as it has at least one record, whether from SQL Server or it's own internal JET engine.

This is not entirely a correct viewpoint. With either JET or ACE, the interface used between the GUI and the DB engine involves synchronous I/O through a formal channel. In order for you to see ANYTHING from an Access-to-ACE/JET connection, the query has to already be done so that your BE can present you with the first element of your result set/recordset. You then synchronously ask for subsequent results (if there are any).

With a pass-thru query to an SQL server or equivalent "active SQL engine" back-end, that query was not synchronously connected, but rather involved an async connection. You have to decide whether the action is complete before you can retrieve data.

To emphasize the difference, if you did that same readiness test with JET/ACE, you would ALWAYS find the action to be complete. With an active SQL back-end, you MIGHT find the action to be complete, or might not.

To the best of my knowledge, with JET/ACE, the ONLY asynchronous activity would be a cleanup phase inside the back-end DB that does not present anything to any connections. The query itself is synchronous.

 

[Petr Danes]

This is not entirely a correct viewpoint. With either JET or ACE, the interface used between the GUI and the DB engine involves synchronous I/O through a formal channel. In order for you to see ANYTHING from an Access-to-ACE/JET connection, the query has to already be done so that your BE can present you with the first element of your result set/recordset. You then synchronously ask for subsequent results (if there are any).

With a pass-thru query to an SQL server or equivalent "active SQL engine" back-end, that query was not synchronously connected, but rather involved an async connection. You have to decide whether the action is complete before you can retrieve data.

To emphasize the difference, if you did that same readiness test with JET/ACE, you would ALWAYS find the action to be complete. With an active SQL back-end, you MIGHT find the action to be complete, or might not.

To the best of my knowledge, with JET/ACE, the ONLY asynchronous activity would be a cleanup phase inside the back-end DB that does not present anything to any connections. The query itself is synchronous.

Not in my experience. Even with local Jet, if I open a large recordset, checking the record count immediately after the OpenRecordset command, the count is less than the total. Repeatedly checking the count shows that it gradually increases. The only way to make sure you have all the records is to issue the .MoveLast command, and with a lare recordset, that can produce a noticeable delay.

My copy of Getz, Litwin and Gilbert is at work, but here is one site explaining that, and I'm sure there are many others.

https://learn.microsoft.com/en-us/p...007/bb243791(v=office.12)?redirectedfrom=MSDN

And I still don't understand why you're using the word async to describe the pass-through query. Access submits a query and waits until the DB engine responds. It doesn't matter whether the source is SQL Server or Jet. It issues a request and waits for a response. That is a pair of synchronous processes communicating over a task.

 

[Dave E]

What part of asynchronous do you not understand? When you send a request to the server this way, it is totally disconnected from the Access GUI so the GUI doesn't know what is going on. In a code loop, either one of yours or one created by the form's class module as it runs your form, the world is locked out until the code finishes. That is why if you want to keep the user updated as to where you are, you need to use Do Events to release control temporarily to Windows in order to allow you to refresh the form so you can update the record count or whatever you are using to inform the user of progress.

That is what I suggested earlier.

From your mouth to God's ear. That isn't the way Access works. The problem is by creating an asynchronous process, you told Access to NOT wait for a response but to continue

Test it yourself. For one of the queries that takes a while, add a msgbox on the line after. Does the message box pop up BEFORE the query ends or after?

You've lost the patient approach you used to have, Pat; not the same Pat from 20 years ago.
I have a personal rule that says I won't be rude unless someone is rude to me. It might help you.

 

[Petr Danes]

You've lost the patient approach you used to have, Pat; not the same Pat from 20 years ago.
I have a personal rule that says I won't be rude unless someone is rude to me. It might help you.

AND he's wrong. As I've written here repeatedly, the code after the query call does NOT execute until AFTER the server responds. Access waits for the DB engine's response.

 

[The_Doc_Man]

Not in my experience. Even with local Jet, if I open a large recordset, checking the record count immediately after the OpenRecordset command, the count is less than the total. Repeatedly checking the count shows that it gradually increases.

You are confusing two events. The JET/ACE engine completes the query - but then AS A SEPARATE ACTION it starts loading the recordset. Your action to check the recordset is different than completion of the query.

And I still don't understand why you're using the word async to describe the pass-through query. Access submits a query and waits until the DB engine responds. It doesn't matter whether the source is SQL Server or Jet. It issues a request and waits for a response.

If you are talking about SQL Server and a pass-thru query, the response is that SQL Server accepted and started running the query.

If you are talking about JET/ACE and a query, pass-thru or otherwise, the response is that the DB engine completed the query.

 

[Pat Hartman]

You've lost the patient approach you used to have, Pat; not the same Pat from 20 years ago.

We are over 50 posts into this thread and the OP is still arguing with us and hasn't yet tried the suggestion I made very early on so please forgive me for getting a little testy.

 

[Dave E]

Forget the current OP, Pat. This is becoming a trend for you. You're showing a genuine lack of patience and the wording of your replies are sometimes demeaning to users looking for answers, not incivility.

It's not that you need to seek forgiveness from anyone but you should count to 10 before you reply and consider your responses more carefully.

 

[Pat Hartman]

You are entitled to your opinion. Thank you for the lesson. There are some people who just can't be helped. I'll try to recognize that earlier and drop out before they annoy me. Good advice. Merry Christmas

 

[Petr Danes]

You are confusing two events. The JET/ACE engine completes the query - but then AS A SEPARATE ACTION it starts loading the recordset. Your action to check the recordset is different than completion of the query.



If you are talking about SQL Server and a pass-thru query, the response is that SQL Server accepted and started running the query.

If you are talking about JET/ACE and a query, pass-thru or otherwise, the response is that the DB engine completed the query.

NO, it's not. I've tested this many times. Both SQL Server and Jet respond with the first record requested record and not before. SQL Server does NOT respond with a message saying, "Okay, I'll get to work on that." It responds with the recordset. Sometimes with just the beginning and gradually fills in the remainder, sometimes with all if it is a small set. Jet does exactly the same. And in both cases, Access waits for that response.

I don't know what to say when you tell me that something is happening which I can clearly see with my own eyes is not happening. I issue an OpenRecordset command, with a pass-through or a local, and Access STOPS AND WAITS until records start coming back. It does NOT continue on it's way UNTIL records start coming back.

If it does that for you, then you are doing something vastly different from me, and I don't even know what that might be. In any case, I see no point in continuing this. I appreciate the tips, and I have something to experiment with when I get back to work next week, but I think we're no longer making any headway.

 

[Petr Danes]

We are over 50 posts into this thread and the OP is still arguing with us and hasn't yet tried the suggestion I made very early on so please forgive me for getting a little testy.

I'm not at work now - shop is closed for the holiday. I can't try anything more until I get back to work, but I have thanked those who provided a testable suggestion, and have made notes of what I intend to try. And I'm only disputing things I know from my own experience - specifically the question of whether Access continues without waiting for a response after issuing an OpenRecordset command. It does not.

 

[The_Doc_Man]

NO, it's not. I've tested this many times. Both SQL Server and Jet respond with the first record requested record and not before.

SQL Server and Jet/Ace both respond with the first record. Agreed. But the question is whether at the time, the OTHER records were (Jet/Ace) or were not (SQL) available. Your freshly opened recordset ALWAYS gives you the first record for either case. That behavior of slowly filling the recordset even happens on my home system based on the Ace engine, which in my case is a monolithic database (since I don't share it.)