Running Public Sub from Cmd Button

[oldblindpew]

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.

 

[Roger Carlson]

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

 

[oldblindpew]

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

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

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.

 

[Roger Carlson]

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/wa.exe?SUBED1=ACCESS-L

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

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

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.

 

[oldblindpew]

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.

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/wa.exe?SUBED1=ACCESS-L

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

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


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.

 

[Beetle]

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

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.

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/wa.exe?SUBED1=ACCESS-L

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

:

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


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.

 

[brlubman]

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).

 

[John W. Vinson]

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.

 

[Dirk Goldgar]

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]

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.

 

[Beetle]

I haven't had occasion to use that approach, but I do see your point and
humbly stand corrected.
--
_________

Sean Bailey

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.

 

[tkelley via AccessMonster.com]

However, doesn't putting them in the properties make search and/or search and
replace difficult? With everything in modules, I can easily find every
instance of it's being called, or use Shift+F2, etc.

Or is there a way that I haven't found?

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]

However, doesn't putting them in the properties make search and/or search
and
replace difficult? With everything in modules, I can easily find every
instance of it's being called, or use Shift+F2, etc.

Yes, that's a reasonable objection. You can use a search utility like Speed
Ferret or Find And Replace -- or a home-grown version search routine, which
I use when I need one -- but you can't just use the built-in Find feature to
find out where a routine is called from.

 

[oldblindpew]

Thanks for the help, but I can't get this to work. I get an error message
that my OnClick expression has a function name that my database can't find.
It seems Access is intent on finding a macro, function, or event procedure,
not a subroutine.

Here's what I put in my OnClick event property box:
=Run("SubOpenForms","formJob")
I put my subroutine in a separate module similiar to the one where I have my
public functions (that work successfully). I'm using Option Compare Database
and Option Explicit, then Public Sub SubOpenForms(strFormName As String). It
looks exactly like my code for doing this with a Function, except the word
Sub is used instead of Function. Of course I made sure to give my subroutine
a unique name.
The Visual Basic help for Run Method doesn't say anything about using it in
this fashion.

It strikes me that if it were true that a public subroutine could be called
from an OnClick event, just like a public function, then functions would not
have the advantage which has been so strongly affirmed. Also "Run", like
"Call", doesn't seem to have any particular meaning for subroutines over
functions, and therefore seems to lack the intrinsic power to change the way
Access treats subroutines.

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)

 

[Dirk Goldgar]

Thanks for the help, but I can't get this to work. I get an error message
that my OnClick expression has a function name that my database can't
find.
It seems Access is intent on finding a macro, function, or event
procedure,
not a subroutine.

Here's what I put in my OnClick event property box:
=Run("SubOpenForms","formJob")
I put my subroutine in a separate module similiar to the one where I have
my
public functions (that work successfully). I'm using Option Compare
Database
and Option Explicit, then Public Sub SubOpenForms(strFormName As String).
It
looks exactly like my code for doing this with a Function, except the word
Sub is used instead of Function. Of course I made sure to give my
subroutine
a unique name.
The Visual Basic help for Run Method doesn't say anything about using it
in
this fashion.

Maybe not, but I did use it in that fashion as a test, and it did work. So
something is different for you, and I don't know what it is. What version
of Access are you using? I'm tested with Access 2003. As I said, I don't
know exactly when the Application.Run method was introduced, but I don't
think it existed before Access 2000.

It could also be that the function is being blocked by macro security or Jet
Sandbox Mode. I would certainly expect it to be blocked by sandbox mode as
an unsafe expression., so if you haven't turned sandbox mode off, it
probably won't work for you.

I could give you instructions on how to disable sandbox mode, but frankly I
think this is really not a very worthwhile pursuit. I would just use a
Function instead of a Sub, if I want the procedure to be callable directly
from an event property.

It strikes me that if it were true that a public subroutine could be
called
from an OnClick event, just like a public function, then functions would
not
have the advantage which has been so strongly affirmed. Also "Run", like
"Call", doesn't seem to have any particular meaning for subroutines over
functions, and therefore seems to lack the intrinsic power to change the
way
Access treats subroutines.

No matter how you look at it, subroutines can be called via this method,
where the Run method is implemented and not blocked. However, you can't
expect to gain anything in efficiency over calling a function, because at
best you're putting your call to the Run method between your calling form
and the subroutine you want to call. I can't imagine that overhead wouldn't
trump any imaginable performance gain you'd get from calling a subroutine
rather than a function.

 

[Roger Carlson]

Personally, I like having all my code in a code window and not scattered in
various property boxes, but to each his own.

--
--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/wa.exe?SUBED1=ACCESS-L

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.

 

[oldblindpew]

Thanks again for your time. What started out as a simple desire to use the
best programming style has gotten totally out of hand. I'm using Access
2007, although my application has roots in an earlier version (97 I think).
This experience has tended to reinforce my negative impressions of Access.
After all, we're not talking about anything really arcane here, just when and
how to use functions and subroutines.

 

[tkelley via AccessMonster.com]

You can jump on the Access-haters bandwagon if you want. But I think it's
really not very complicated. I don't know many languages that only have one
way to do anything. The best approach for me is to develop standards, use
them, and stay consistent. Pretty simple.

Thanks again for your time. What started out as a simple desire to use the
best programming style has gotten totally out of hand. I'm using Access
2007, although my application has roots in an earlier version (97 I think).
This experience has tended to reinforce my negative impressions of Access.
After all, we're not talking about anything really arcane here, just when and
how to use functions and subroutines.
[quoted text clipped - 52 lines]

trump any imaginable performance gain you'd get from calling a subroutine
rather than a function.

 

[Dirk Goldgar]

Thanks again for your time. What started out as a simple desire to use
the
best programming style has gotten totally out of hand. I'm using Access
2007, although my application has roots in an earlier version (97 I
think).
This experience has tended to reinforce my negative impressions of Access.
After all, we're not talking about anything really arcane here, just when
and
how to use functions and subroutines.

I'm not sure what has caused your negative impressions. In any sufficiently
powerful development environment, there are usually several different ways
to accomplish any task, and there are usually have pluses and minuses
associated with each. But with regard to calling procedures directly from
event procedures, I look on the ability to do it with functions as a happy,
if fortuitous, circumstance, and so it doesn't really bother me that you
can't do it with subroutines.