VBA Access 2003 Only Allow Press Button Once Within Reasonable Amount Of Time

Question

I have a button on a form that takes the RowSource from a listbox on my form, creates an Excel, Worksheet and Query Table object, queries the information into excel and then formats it all. The problem is that if the user presses the mouse button quickly, he or she will activate this button just as many times.

I have attempted putting the code to sleep in-between, but this just causes each button press to last as long as the sleep and then however long the query takes. What I used:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

I also tried setting the focus to another control and disabling the button, but I'm not quite sure how to re-enable it after a set amount of time, because even putting it at the end of the sub, Access still executes the button for every button press .

Any suggestions?

HansUp · Accepted Answer · 2013-07-25T19:03:45.833

5

Instead of waiting a fixed amount of time, disable the command button when first clicked. Then do your long-running operations, and enable the command button again afterward.

Assuming the name of the command button is cmdToExcel ...

Private Sub cmdToExcel_Click()
    Me.txtNote.SetFocus
    Me.cmdToExcel.Enabled = False
    ' replace next line
    MsgBox "call long running code here"
    Me.cmdToExcel.Enabled = True
    Me.cmdToExcel.SetFocus
End Sub

Note you must SetFocus to a different control before disabling the command button because Access won't allow you to disable it while it still has focus.

You may not want to SetFocus back to the command button at the end --- I just made a wild guess on that one. :-)

It seems that code flow control returns to the click event procedure before the long running process has finished. So the command button is enabled again prematurely.

In that case you can insert a pause, using your Sleep API declaration, after calling long running code.

Private Sub cmdToExcel_Click()
    Me.txtNote.SetFocus
    Me.cmdToExcel.Enabled = False
    ' replace next line
    MsgBox "call long running code here"
    Sleep 5000 ' 5 seconds
    Me.cmdToExcel.Enabled = True
    Me.cmdToExcel.SetFocus
End Sub

I realize now that is what you asked for in the first place. Sorry I was thick.

edited Jul 25 '13 at 19:03

answered Jul 25 '13 at 18:45

HansUp

95,961
11
77
135

I've tried this and just tried it again just to be sure, and it still executes the "long running code" per button press. – Elias Jul 25 '13 at 18:50
Hmm. Does "long running code" return control to `cmdToExcel_Click` before it (long running code) has completed? Insert a break point in `cmdToExcel_Click` and step through the code if you need to check. – HansUp Jul 25 '13 at 18:53
It does not, the focus stays with a seperate control. I even tried putting the 'enable = false' before my variable initializations! – Elias Jul 25 '13 at 18:55
Your fine, Im terrible at wording things! Your second solution is actually what I did first with the sleep function. Sadly instead of disabling and then waiting 5 seconds, it will call per button click and do the sleep in between each click. – Elias Jul 25 '13 at 19:12
I was also thinking about trying to set a global bit that would be true/false based on the button's execution, but I REALLY don't want to use a global variable. – Elias Jul 25 '13 at 19:14
Yeah, you shouldn't have to use a global. But I don't understand how that code would permit a second button click in less than 5 seconds after the first. If you're willing to upload a copy of your db to a file sharing site, I'd like to examine it. If not, afraid I'm stumped. – HansUp Jul 25 '13 at 19:16
I'm afraid I can't upload the db I'm having the issue on due to my industry, but if I get a moment I'll try to reproduce it in a smaller similar format. Thanks for the help so far! – Elias Jul 25 '13 at 19:19
The long running code is actually pretty simple, it just creates a querytable in excel and shoves a query over. I actually ended up asking my supervisor to check it out and he suggested setting the focus in th MouseDown event, separate from the Click event. That dissolves about half of the issue. Adding the enable/disable I originally did and you also suggested gets rid of about 55% of the issue. I feel it is good enough for the severity of the problem, so thank you for all of your help. You got me the closest to my answer, so accepted! – Elias Jul 25 '13 at 19:40

score 0 · Answer 2 · answered Jul 25 '13 at 18:53

Say you want the button to be disabled for no less than 5 seconds

Me.cmdButton.Enabled = False
timing = Dbl(Now())
'
' query code
'
timing = Dbl(Now()) - timing
seconds = Int(CSng(timing * 24 * 3600))
If seconds < 5 Then
    Sleep (5 - seconds) * 1000
End If
Me.cmdButton.Enabled = True

VBA Access 2003 Only Allow Press Button Once Within Reasonable Amount Of Time

2 Answers2