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

3
votes

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?

2
vbams-accessuser-controls

2 Answers

5
votes

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.

0
votes

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