Running Public Sub from Cmd Button

My application successfully runs Public Functions from On Click events by
simply setting the On Click event equal to the Function name, but I feel I
"should" be using Subroutines since I'm not looking for a returned value.
However, the default behavior of Subroutines is to be Private, that is, the
code is married to one button on one form. The default name of any and every
Subroutine is [Event Procedure].

My question is: How do I run a Subroutine (I mean a real public subroutine,
not these little private procedures) from an On Click event? I find no
discussion of this in my reference books, which seems very strange.

If you create a Subroutine in a General Module and give it a Public
designation, it is a real public subroutine. You call it by simply adding

Call your subroutine name here

in your OnClick event. Something like this:

Private Sub cmdMyButton_Click()
On Error GoTo Err_cmdCreate_Click

Call MySubroutine

Exit_cmdMyButton_Click:
Exit Sub
Err_cmdMyButton_Click:
MsgBox Err.Description
Resume Exit_cmdMyButton_Click
End Sub

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com


"oldblindpew" wrote in message
...
My application successfully runs Public Functions from On Click events by
simply setting the On Click event equal to the Function name, but I feel I
"should" be using Subroutines since I'm not looking for a returned value.
However, the default behavior of Subroutines is to be Private, that is,
the
code is married to one button on one form. The default name of any and
every
Subroutine is [Event Procedure].

My question is: How do I run a Subroutine (I mean a real public
subroutine,
not these little private procedures) from an On Click event? I find no
discussion of this in my reference books, which seems very strange.

Thanks for your help.

I'm still confused, though. Are you saying that when the OnClick event
says: = function(arguments), Access knows to look for a function, but when it
says: Call subroutine(arguments), Access knows to look for a subroutine?

In your example, it appears the recommended technique is to embed the
calling of the public subroutine within a private OnClick event procedure.
If so, why wouldn't the OnClick event just be [Event Procedure], as usual?
And if the OnClick event doesn't say [Event Procedure], how does Access know
to execute the private OnClick event procedure? Is there no way to run the
public sub without first going thru a private sub?

I assume your OnError line is supposed to reference Err_cmdMyButton.
Thanks again for your time and help

"Roger Carlson" wrote:

If you create a Subroutine in a General Module and give it a Public
designation, it is a real public subroutine. You call it by simply adding

Call your subroutine name here

in your OnClick event. Something like this:

Private Sub cmdMyButton_Click()
On Error GoTo Err_cmdCreate_Click

Call MySubroutine

Exit_cmdMyButton_Click:
Exit Sub
Err_cmdMyButton_Click:
MsgBox Err.Description
Resume Exit_cmdMyButton_Click
End Sub

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com


"oldblindpew" wrote in message
...
My application successfully runs Public Functions from On Click events by
simply setting the On Click event equal to the Function name, but I feel I
"should" be using Subroutines since I'm not looking for a returned value.
However, the default behavior of Subroutines is to be Private, that is,
the
code is married to one button on one form. The default name of any and
every
Subroutine is [Event Procedure].

My question is: How do I run a Subroutine (I mean a real public
subroutine,
not these little private procedures) from an On Click event? I find no
discussion of this in my reference books, which seems very strange.

The properties box will say [Event Procedure], but when you click the
Builder Button [...] next to it, you go into a code window where you put
your call. Yes, you have to call a public subroutine from a private
subroutine. That's the nature of Access forms. I'm not sure why it makes a
difference.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L





"oldblindpew" wrote in message
...
Thanks for your help.

I'm still confused, though. Are you saying that when the OnClick event
says: = function(arguments), Access knows to look for a function, but when
it
says: Call subroutine(arguments), Access knows to look for a subroutine?

In your example, it appears the recommended technique is to embed the
calling of the public subroutine within a private OnClick event procedure.
If so, why wouldn't the OnClick event just be [Event Procedure], as usual?
And if the OnClick event doesn't say [Event Procedure], how does Access
know
to execute the private OnClick event procedure? Is there no way to run
the
public sub without first going thru a private sub?

I assume your OnError line is supposed to reference Err_cmdMyButton.
Thanks again for your time and help

"Roger Carlson" wrote:

If you create a Subroutine in a General Module and give it a Public
designation, it is a real public subroutine. You call it by simply
adding

Call your subroutine name here

in your OnClick event. Something like this:

Private Sub cmdMyButton_Click()
On Error GoTo Err_cmdCreate_Click

Call MySubroutine

Exit_cmdMyButton_Click:
Exit Sub
Err_cmdMyButton_Click:
MsgBox Err.Description
Resume Exit_cmdMyButton_Click
End Sub

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com


"oldblindpew" wrote in message
...
My application successfully runs Public Functions from On Click events
by
simply setting the On Click event equal to the Function name, but I
feel I
"should" be using Subroutines since I'm not looking for a returned
value.
However, the default behavior of Subroutines is to be Private, that is,
the
code is married to one button on one form. The default name of any and
every
Subroutine is [Event Procedure].

My question is: How do I run a Subroutine (I mean a real public
subroutine,
not these little private procedures) from an On Click event? I find no
discussion of this in my reference books, which seems very strange.

It makes a difference because it gives Functions a clear advantage over
Subroutines in that the former can be called directly from the property box
of an OnClick event.


"Roger Carlson" wrote:

The properties box will say [Event Procedure], but when you click the
Builder Button [...] next to it, you go into a code window where you put
your call. Yes, you have to call a public subroutine from a private
subroutine. That's the nature of Access forms. I'm not sure why it makes a
difference.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L





"oldblindpew" wrote in message
...
Thanks for your help.

I'm still confused, though. Are you saying that when the OnClick event
says: = function(arguments), Access knows to look for a function, but when
it
says: Call subroutine(arguments), Access knows to look for a subroutine?

In your example, it appears the recommended technique is to embed the
calling of the public subroutine within a private OnClick event procedure.
If so, why wouldn't the OnClick event just be [Event Procedure], as usual?
And if the OnClick event doesn't say [Event Procedure], how does Access
know
to execute the private OnClick event procedure? Is there no way to run
the
public sub without first going thru a private sub?

I assume your OnError line is supposed to reference Err_cmdMyButton.
Thanks again for your time and help

"Roger Carlson" wrote:

If you create a Subroutine in a General Module and give it a Public
designation, it is a real public subroutine. You call it by simply
adding

Call your subroutine name here

in your OnClick event. Something like this:

Private Sub cmdMyButton_Click()
On Error GoTo Err_cmdCreate_Click

Call MySubroutine

Exit_cmdMyButton_Click:
Exit Sub
Err_cmdMyButton_Click:
MsgBox Err.Description
Resume Exit_cmdMyButton_Click
End Sub

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com


"oldblindpew" wrote in message
...
My application successfully runs Public Functions from On Click events
by
simply setting the On Click event equal to the Function name, but I
feel I
"should" be using Subroutines since I'm not looking for a returned
value.
However, the default behavior of Subroutines is to be Private, that is,
the
code is married to one button on one form. The default name of any and
every
Subroutine is [Event Procedure].

My question is: How do I run a Subroutine (I mean a real public
subroutine,
not these little private procedures) from an On Click event? I find no
discussion of this in my reference books, which seems very strange.

Functions and Subroutines have two different purposes, so one doesn't
have an advantage over the other. You could say that an apple has an
advantage over an orange because you don't have to peel it before you
eat it, but if you want to make orange juice an apple doesn't do you much
good, so the so-called advantage is irrelevant.

The only difference between being able to type a public sub name directly
in the property box, and typing it in the actual event procedure, is 2 clicks
of the mouse (or 1 if you have Access configured to go directly to the code
window when you click the build (...) button).
--
_________

Sean Bailey


"oldblindpew" wrote:

It makes a difference because it gives Functions a clear advantage over
Subroutines in that the former can be called directly from the property box
of an OnClick event.


"Roger Carlson" wrote:

The properties box will say [Event Procedure], but when you click the
Builder Button [...] next to it, you go into a code window where you put
your call. Yes, you have to call a public subroutine from a private
subroutine. That's the nature of Access forms. I'm not sure why it makes a
difference.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L





"oldblindpew" wrote in message
...
Thanks for your help.

I'm still confused, though. Are you saying that when the OnClick event
says: = function(arguments), Access knows to look for a function, but when
it
says: Call subroutine(arguments), Access knows to look for a subroutine?

In your example, it appears the recommended technique is to embed the
calling of the public subroutine within a private OnClick event procedure.
If so, why wouldn't the OnClick event just be [Event Procedure], as usual?
And if the OnClick event doesn't say [Event Procedure], how does Access
know
to execute the private OnClick event procedure? Is there no way to run
the
public sub without first going thru a private sub?

I assume your OnError line is supposed to reference Err_cmdMyButton.
Thanks again for your time and help

"Roger Carlson" wrote:

If you create a Subroutine in a General Module and give it a Public
designation, it is a real public subroutine. You call it by simply
adding

Call your subroutine name here

in your OnClick event. Something like this:

Private Sub cmdMyButton_Click()
On Error GoTo Err_cmdCreate_Click

Call MySubroutine

Exit_cmdMyButton_Click:
Exit Sub
Err_cmdMyButton_Click:
MsgBox Err.Description
Resume Exit_cmdMyButton_Click
End Sub

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com


"oldblindpew" wrote in message
...
My application successfully runs Public Functions from On Click events
by
simply setting the On Click event equal to the Function name, but I
feel I
"should" be using Subroutines since I'm not looking for a returned
value.
However, the default behavior of Subroutines is to be Private, that is,
the
code is married to one button on one form. The default name of any and
every
Subroutine is [Event Procedure].

My question is: How do I run a Subroutine (I mean a real public
subroutine,
not these little private procedures) from an On Click event? I find no
discussion of this in my reference books, which seems very strange.

On 11/18/08 7:57 PM, in article
, "Beetle"
wrote:

Functions and Subroutines have two different purposes, so one doesn't
have an advantage over the other. You could say that an apple has an
advantage over an orange because you don't have to peel it before you
eat it, but if you want to make orange juice an apple doesn't do you much
good, so the so-called advantage is irrelevant.

The only difference between being able to type a public sub name directly
in the property box, and typing it in the actual event procedure, is 2 clicks
of the mouse (or 1 if you have Access configured to go directly to the code
window when you click the build (...) button).

On Tue, 18 Nov 2008 16:57:01 -0800, Beetle
wrote:

Functions and Subroutines have two different purposes, so one doesn't
have an advantage over the other. You could say that an apple has an
advantage over an orange because you don't have to peel it before you
eat it, but if you want to make orange juice an apple doesn't do you much
good, so the so-called advantage is irrelevant.

The only difference between being able to type a public sub name directly
in the property box, and typing it in the actual event procedure, is 2 clicks
of the mouse (or 1 if you have Access configured to go directly to the code
window when you click the build (...) button).

Actually there is an advantage to using a Function, in that you can use a
"light" form (with no VBA code in its Module) by just calling functions in the
event procedure. If you use Subs then you must have two layers, the [Event
Procedure] sub calling your generic sub.
--

John W. Vinson [MVP]

"oldblindpew" wrote in message
...
My application successfully runs Public Functions from On Click events by
simply setting the On Click event equal to the Function name, but I feel I
"should" be using Subroutines since I'm not looking for a returned value.
However, the default behavior of Subroutines is to be Private, that is,
the
code is married to one button on one form. The default name of any and
every
Subroutine is [Event Procedure].

That's not exactly true. It is not the default nature of Subs to be
Private; they are Private or Public depending on where and how they are
declared. However, the way Access event properties work is that, if the
property value is not "[Event Procedure]" or the name of a macro, it must be
an evaluatable expression. Because Subs don't return a value, they are not
evaluatable.

My question is: How do I run a Subroutine (I mean a real public
subroutine,
not these little private procedures) from an On Click event? I find no
discussion of this in my reference books, which seems very strange.

I always use a Function for this purpose. Even if there's a little bit of
extra overhead compared to calling a Sub -- and I don't know that there
is -- who cares?

However, if you have a public Sub that you want to call directly from the
OnClick property line, you can use the Application.Run method:

=Run("YourSub")

=Run("YourSub2", "Argument for Sub2")

Look up "Run Method" in the online help.

I'm not sure when this method was introduced. I'm pretty sure it didn't
exist in Access 97, and I don't know if it existed in Access 2000. Maybe.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"John W. Vinson" wrote in message
...
Actually there is an advantage to using a Function, in that you can use a
"light" form (with no VBA code in its Module) by just calling functions in
the
event procedure. If you use Subs then you must have two layers, the [Event
Procedure] sub calling your generic sub.


I agree. Lightweight forms are elegant.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)