preventing command button being clicked twice (1 Viewer)

[upnorth]

Good evening
I Have an access database in use for about 15 years with minimal probs running our bottled milk business
From the home page a button opens a form where invoices can be created.
As the form opens a query grabs data from the sales tbl & drops it into the preinvoice tbl
then a query marks the records in the sales tbl to show they have been moved.
About 3 times a year we get some duplicate records in the preinvoice table.
Only reason I can think of an impatient user clicks the button twice so the routine runs again grabbing the data before the 1st routine has marked the data.
The routine takes about 5 seconds from click to create invoice form is open
Any sugggestions ?
Timer so button can not be clicked twice 5 seconds
message box saying "wait"
Appreciate your thoughts
TIA
David

 

[Gasman]

Set a global boolean variable or tempvar at the start. Unset it at the end.
If still set, do not run the code?
Or disable button until code has run?

 

[Edgar_]

I'd also use DoEvents.

Private Sub someCommand_Click()
    Me.someCommand.Enabled = False
    Me.someCommand.Caption = "Working..."
    DoEvents
    ' slow code
    Me.someCommand.Caption = "Ready"
    Me.someCommand.Enabled = True
End Sub

The DoEvents is helpful to show the disabled state of the button, otherwise it may not show and not really become disabled. As to why it behaves like that, I'm not sure, but the DoEvents helps with that. Many times, it's better to include the DoEvents inside the slow code, before or after the part that takes the longest, because it prevents Access from temporary freezing, but without it, I usually don't get the disabled state of the button and all clicks execute.

 

[The_Doc_Man]

I don't think any button can be disabled if it still has focus, which would certainly be the case immediately after clicking the button. However, the idea having of a software flag - to indicate that the particular button's code is busy - is certainly viable.

However, I'm wondering about this problem from a control-flow viewpoint. This might require cooperation from at least one other routine as well. The catch is that Access event code is single-threaded because of the way event code is processed. MSACCESS.EXE calls each event routine in its appropriate turn, one event at a time, so a double-dip (clicking the same button twice not as a double-click but as two single clicks) would be treated as two distinct click actions for which the event code CANNOT overlap. The second click would trigger an entry into the process's event queue - a list of events waiting for event handler execution.

Indeed you CAN click that button more than once in the interval of about 5 seconds. The problem is that the event code will be linearized into that queue such that the second click won't even BEGIN to be processed until the first click's event reaches the END SUB or EXIT SUB to close it out. Therefore, if you reset the flag variable inside the _Click event, it will be reset in time for the next event routine to see that it is FALSE.

I think you will need that flag to block the button until the actions it triggers are dismissed. I.e. the code on the button launches a form and DOES something with it - save it or cancel it - but DO something first. Therefore, put the variable in a General Module header that will be visible both from the form holding that button and the form being launched. You should then set the flag from the button's _Click routine but LEAVE it in the "blocked" state. You probably should not reset the flag until that form has "done its thing" - which implies action in some other event code such as the launched form's OnClose event.

 

[upnorth]

Thanks for the replies.
@The_Doc_Man s reply saying code instructions are queued puts my theory for the cause of the problem out of the window.?
Anyway, finishing time, back tomorrow.

 

[Edgar_]

Another thing to keep in mind is concurrence, if two users are using the same resources when only one should, you should block the resources immediately after one has started using them, so another user can't use them until the first is done. For that, a simple table level flag can be queried right before any operation and see if some user has started using the resources and is not done yet.

 

[Pat Hartman]

About 3 times a year we get some duplicate records in the preinvoice table.

Add a unique index to prevent this from happening.

 

[Isaac]

Good evening
I Have an access database in use for about 15 years with minimal probs running our bottled milk business
From the home page a button opens a form where invoices can be created.
As the form opens a query grabs data from the sales tbl & drops it into the preinvoice tbl
then a query marks the records in the sales tbl to show they have been moved.
About 3 times a year we get some duplicate records in the preinvoice table.
Only reason I can think of an impatient user clicks the button twice so the routine runs again grabbing the data before the 1st routine has marked the data.
The routine takes about 5 seconds from click to create invoice form is open
Any sugggestions ?
Timer so button can not be clicked twice 5 seconds
message box saying "wait"
Appreciate your thoughts
TIA
David

Vote/Agree with @Gasman. I almost always Disable (enabled=false) a button on its click event, in just about the first line of code.

Think VISUAL CUES - the more informative visual cues you can give your user, the more user-friendly your application will be deduced to be.

Obviously, if a database-style solution is warranted, implement it also - such as a BeforeUpdate or a unique index to absolutely prevent duplicate records from the 'server perspective', if I may - but from a GUI design perspective, always disable a button that takes any substantial amount of time instantly upon click.

 

[isladogs]

I don't think any button can be disabled if it still has focus, which would certainly be the case immediately after clicking the button.

In fact that hasn't been the case for over 10 years. Controls including buttons can be disabled or locked whilst they have focus.
You can even use that feature to then hide a button with focus

Click Me And I Disappear!

In older versions of Access up to A2010, you could only disable, lock or hide controls if they did not have the focus. In recent years, that limitation was changed . . . with some interesting side effects!

www.isladogs.co.uk

 

[Pat Hartman]

The problem goes beyond pushing the button twice when a form is open. The problem is also caused if the user views the same record again later and presses the button.

If you want to disable the button, you need to know how to identify the updates to records as the result of depressing the button.

As the form opens a query grabs data from the sales tbl & drops it into the preinvoice tbl
then a query marks the records in the sales tbl to show they have been moved.

This seems to be a recipe for errors. No action like this should ever be the result of simply opening a form, PERIOD. This action should always be the result of a button click to work on a single record or perhaps to run a query that affects all selected records.

So, please be absolutely clear on what causes the records to be "marked". then clearly specify how to identify "marked" records.

Then in the form's Current Event and also in the form's After Update event, run a dCount() to count the "marked" records. If the count is >0, then set the enabled property of the control to false.

 

[The_Doc_Man]

In fact that hasn't been the case for over 10 years

It figures that I have a 13-year old version of Access.

@The_Doc_Man s reply saying code instructions are queued puts my theory for the cause of the problem out of the window.?

Not necessarily. Click on the button from form A, it launches form B. Once form B is launched, the event code under form A could exit. If you don't have form B somehow "hardened" to check for "overlapped clicks" then (even given my error about disabling buttons that have focus), if you HAD clicked the button in form A a second time before B's launch, you would re-execute the code that would launch form B - and if it did so with information currently (still?) on form A as its target, you would get a second record with that information. So a second click before the launch of form B could still cause you issues. The only real solution is to recognize that NORMALLY, form A and form B are independent of each other, implemented as child processes of your master process. They could BOTH be active. Access isn't multi-threaded - but forms being in separate child processes bypasses that restriction. For instance, if there is a timer in form A, form B can still be actively running. And we have seen a few threads where someone uses API calls to create a "fork" process that also has the effect of simultaneously active form events. So that single-threaded rule isn't absolute.

 

[AHeyne]

The catch is that Access event code is single-threaded because of the way event code is processed. MSACCESS.EXE calls each event routine in its appropriate turn, one event at a time, so a double-dip (clicking the same button twice not as a double-click but as two single clicks) would be treated as two distinct click actions for which the event code CANNOT overlap. The second click would trigger an entry into the process's event queue - a list of events waiting for event handler execution.

Indeed you CAN click that button more than once in the interval of about 5 seconds. The problem is that the event code will be linearized into that queue such that the second click won't even BEGIN to be processed until the first click's event reaches the END SUB or EXIT SUB to close it out..

This is usually the case, but I found an 'exception' to this rule a few weeks ago:
If the code that is executed by the button contains a 'DoEvents', it is quite possible that the code behind the button is executed more than once 'quasi in parallel'.
The first click on the button runs up to the DoEvents, whereby a further click on the button is also recognized/allowed.
The code is then called again and executed in full (if the button was not clicked again).
Only then is the original program thread of the first click executed to completion.
So it is not really executed in parallel, this rule is of course valid, but the code is paused at the 'DoEvents' and one or more 'instances' (depending on how fast you can click) are executed.

@upnorth : I would suggest to search for and disable (if possible) any DoEvents which may be called in your executed button code.

 

[ebs17]

As the form opens a query grabs data from the sales tbl & drops it into the preinvoice tbl

I would deal with the data. First measure: Unwanted duplicates can be prevented using a unique index, it has already been written.
Second measure: With a built-in inconsistency check in the append query, ONLY NEW records would be entered into the target table. You could then press the button ten thousand times. Without new data, there is no new reaction.

So my goal would be to focus on data integrity, not to temporarily chain up a button.

 

[upnorth]

Thanks everyone for the suggestions.
Does suggest that clicking the button more than once is causing the intermitent problem.
@ebs17 yes it makes sense to check the data
I`ve checked & the unique record number from the sales table is one of the fields the update query drops into the preinvoice table.
That gives us the chance to look for duplicate record numbers in the preinvoice table
when the create invoice routine is run.

It`s over 10 years since I regularly did MS Access work so I`m a bit rusty.

Thinking the 1st step on the invoice create routine would be a query that checks for duplicates. ??

This seems to be a recipe for errors. No action like this should ever be the result of simply opening a form, PERIOD. This action should always be the result of a button click to work on a single record or perhaps to run a query that affects all selected records.

My poor description @Pat Hartman The routine works as you say it should.
The invoice section of this database was created with a huge amount of help from Allen Browne almost 20 years ago & has been amazingly robust & reliable. Only in the last 12 months has this issue appeared

 

[ebs17]

the 1st step

With a good query you can take several steps in one go.

How exactly do you transfer records? Concrete suggestions must be based on specific circumstances.

 

[AHeyne]

Only in the last 12 months has this issue appeared

Maybe a mouse button has started bouncing recently?

 

[GPGeorge]

Thanks everyone for the suggestions.
Does suggest that clicking the button more than once is causing the intermitent problem.
@ebs17 yes it makes sense to check the data
I`ve checked & the unique record number from the sales table is one of the fields the update query drops into the preinvoice table.
That gives us the chance to look for duplicate record numbers in the preinvoice table
when the create invoice routine is run.

It`s over 10 years since I regularly did MS Access work so I`m a bit rusty.

Thinking the 1st step on the invoice create routine would be a query that checks for duplicates. ??



My poor description @Pat Hartman The routine works as you say it should.
The invoice section of this database was created with a huge amount of help from Allen Browne almost 20 years ago & has been amazingly robust & reliable. Only in the last 12 months has this issue appeared

Issues "appear" for only a handful of reasons, one of which is a change in data, such as the introduction of new data with different datatypes from that originally expected. Another is a deliberate change in VBA or a modification to an interface design. Another might be a change in the way users are working with the application. Another is, potentially, a change in Access itself. Perhaps other things changed, but those would be the first things I would investigate.

What happened 12 months or so ago in your environment?

After reading through the thread, I agree that the solution will not be to create a new wad of code stuck on top of existing code to patch up the form.

"Thinking the 1st step on the invoice create routine would be a query that checks for duplicates. ??"

Actually, that would be one of those "code wads" stuck on top of the existing code. There is a more efficient way. I think adding a unique index to the field or fields in question is going to go a long way towards preventing unwanted duplicates. And rethinking how you trigger new additions is in order as well.

 

[Pat Hartman]

You still never clarified that pushing the button multiple times during a single "open" of the form is not the only issue. Can you click the button a second time by leaving the record and then coming back to it.

It is very important to know the full scope of the problem.

 

[upnorth]

What happened 12 months or so ago in your environment?

People !
A user who is prone to being impatient if he is under pressure.
Hence my initial thought, the simple solution was to prevent multiple clicks of the button

 

[ebs17]

the simple solution was to prevent multiple clicks of the button

It's not that simple. At some point you have to allow clicking again - do you have a reliable rule for this?

If you have moved beyond your first thought: you already have material for more.