Execute a Public Function from a control on a form

Question

Hi, I am beginning to delve a little more into VBA in my Access database. I made an event procedure on the onClick of a form's control.  I realized I would be needing this same code on many forms throughout my database, so I moved it from a Private Sub to a Public Function in a module. 

What is the correct way to execute this public function from the onClick command of the form's control? To make it work, I created an event procedure ( a private sub) for the form's control that says **Call nameOfPublicFunction.That seems like a roundabout way to get there. I tried puttingnameOfPublicFunction()**directly in the onclick property of the control, but that didn't work.

Answer 1

You can execute the function by putting it directly in the OnClick property - preceded by an = sign:

=NameOfPublicFunction()

If the function can be called from multiple forms, I'll often pass the form reference:

Public Function NameOfPublicFunction(frm As Form) As Integer

...

and set the event procedure to

=NameOfPublicFunction([Form])

You can then refer to other controls or properties of the form within the procedure:

If frm.Name = "SpecialForm" Then...

The = sign apparently tells the VBA engine that you're expecting a value to be returned; that's why it must be a Public Function (which returns a value), not a Public Sub (which doesn't). So far as I've been able to determine, you can't do anything with the returned value, so even though you could assign NameOfPublicFunction a value in the code, there doesn't seem to be anything you can do with it!

Answer 2

and it is greatly appreciated!!!

Answer 3

Just marking as Answer is fine... but a polite thank-you like yours is appreciated. It's good to know that I'm having a personal impact!

Answer 4

Thank you!

Please someone tell me, is it proper etiquette to say thanks, or just mark as answer? I want to say thanks, but I'm wondering if that may just clog up the system...sometimes I say TIA, thanks in advance, but if I forget that...

Answer 5

To call a Function from a Command Button...

Private Sub cmdYourCommandButton_Click()

     Call nameOfPublicFunction

End Sub