2005.07.13 03:35 PM

Programmatically Click an Access Form Button

Imagine you have a good reason to programmatically cause an Access form button to be clicked.

Maybe the button's click event isn't bound to a VBA-based code handler, which you could just call, but is instead bound to a macro or expression.

Or, maybe you need to click it from another form, and even though the button is bound to a VBA-based code handler, the handler routine remains private and can't be changed (at least by you).

Or, maybe you've inherited a system and need to cause a form button to click based on specific activity in another control and can't figure out or spend the time to figure out what else happens when the current control looses focus or what if anything happens when the button gains focus, and even if you could you wouldn't have time to refactor and test the code to support direct execution separate from the event handlers and so just want to cause the button to be clicked instead.

All good reasons. So how do you do it?

Here's one way:

Public Sub ClickButton(ByVal Button As Access.CommandButton)
  On Error Resume Next
  Call Button.SetFocus
  If Err.Number = 0 Then
    Call SendKeys(" ")
  End If
End Sub

Put this routine in a module and call it, passing it a reference to the button to be clicked.

You can remove the On Error Resume Next if it offends, but you'll have to be certain the button is not disabled or invisible, and then do the same for its parent, and its parent, etc.


Comments

Could you post an example of how you pass a reference to the button being clicked.

I tried ClickButton(me.cmdNextRec) and was informed that the object did not support this property or method.

BTW, the reason I was doing this was because I could not get a text box to refresh properly without advancing the record and returning to the record. I used the .Repaint method of the form, and it updated some, but not all of the text boxes on the form.

Alan Jordan | 2005.09.13 08:38 AM

Hi Alan,

I'm not sure I know enough about what you're doing to say for sure, but I think the problem may be the format you're using to call ClickButton(). If you are going to use parentheses to wrap the parameters passed to a subroutine, you'll have to precede the routine's name with a Call statement:

Call ClickButton(Me.cmdNextRec)

Alternatively, you can just omit the parentheses:

ClickButton Me.cmdNextRec

Using parentheses without the Call statement is the same as this:

ClickButton (Me.cmdNextRec)

Note the space between the routine's name and the left paren. Wrapping a variable in parentheses like this causes VB to evaluate the variable to determine its value. This is sometimes used to dereference parameters passed ByRef in order to protect their contents.

In this case the evaluation of the statement Me.cmdNextRec is undefined, because command buttons have no default property. Hence, the 438 error.

Here's more info, if you're interested:

http://blogs.msdn.com/ericlippert/archive/2003/09/15/52996.aspx

Try it without the parantheses and let me know what you find.

Regarding the refreshing of a textbox, are you programmatically changing its value but not seeing the change reflected in the control?

Good luck!

ewbi.develops | 2005.09.13 03:40 PM

Hi
how do i link two combo boxes on a form and synchronised them.When I select a city in the top cmbo the bottom one should display that cities postal code for instance.

Schaun | 2006.10.11 10:39 AM

Schaun,

Rather than try to explain it here (and I don't have a lot of time to write up a separate post), I just did a quick check via Google and found lots of examples of doing this. Here are just a couple of the references I found:

http://www.fontstuff.com/access/acctut10.htm
http://www.techonthenet.com/access/comboboxes/linked_combos.php

With a little searching I'm sure you'll find plenty more. However, if after looking you still can't figure it out, come back and let me know and I'll try to help.

Good luck.

ewbi.develops | 2006.10.11 02:06 PM

I tryed your suggested method, but it only set the focus in the given button. It does not run the code behind the buttons' click event. How can I not only select the button, but simulate the click as well?

Sergio | 2007.09.05 06:39 PM

Hi,
do you have any idea how can I make the same thing with VBExcel form buttons? (That's the system I've inherited:)

Michael | 2007.12.20 06:54 AM

Hey guys, I would suggest editing this sub to be:

// *********************************

Public Sub ClickButton(ByVal Button As Access.CommandButton)
On Error Resume Next
Call Button.SetFocus
If Err.Number = 0 Then
Call SendKeys(" ", True)
End If
End Sub

// *********************************

By adding the "True" value in the Wait parameter for the SendKeys makes it WAY more stable. Sometimes the button was only being selected and sometimes the whole thing worked. I added the "True" value, and now it works all the time ;)

John | 2008.02.14 03:08 AM

Cool, thanks John!

ewbi.develops | 2008.02.14 08:44 AM

Grateful if you could provide some assistance. I have added this module to my database with the aim of programmatically clicking a button on an open form, but when I put the call message into the public sub routine, on execution I get the error message "Compiler error Expected variable or a procedure, not module" Is there anything I am obviously doing wrong.

Public Sub Form_Timer()
With Btn_Accept
.BackColor = (IIf(.BackColor = vbYellow, vbGreen, vbYellow))
End With
If Me.txt_timer < 0 And Me.tgl_Picklist = -1 Then
ClickButton (Me.Btn_Accept)
Else
Me.txt_timer = Me.txt_timer - 1
End If
End Sub

Cliff | 2012.09.05 02:25 AM

Hi Cliff,

Take a look back at the second comment on this post about how to make that call. The parens you've got around Me.Btn_Accept without the Call keyword are causing VBA to try and de-reference the button object. Either add Call or remove the parens and see what happens.

Good luck!

ewbi.develops | 2012.09.05 06:55 AM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8349d3d2169e2

Listed below are links to weblogs that reference Programmatically Click an Access Form Button: