Event Procedure in Module

[Jonas]

Is it possible to put event procedures in modules? If so, how is it
done? Here is the code that I have:

Private Sub Command7_Click()
Dim str4 As String

str4 = "INSERT INTO History "
str4 = str4 + "SELECT MailMergeTable.* "
str4 = str4 + "FROM MailMergeTable"

MsgBox str4

DoCmd.RunSQL str4

End Sub

 

[Jeff Boyce]

Jonas

It may be a matter of terminology.

Event procedures are procedures that are triggered by events.

Procedures are ... well, ... procedures.

You can put procedures in modules.

(and if you are talking about the modules that accompany forms or reports,
then I suppose you could "put [an] event procedure in [a] module")

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

Well, it would not be an event procedure in a standard module, but you can
execute code in a standard module from an event. To do so, the procedure has
to be a Function. It will not work with a Sub. Then all you need to do is
put the following in the Event text box in the properties dialog.

=MyFunction()

It can also accept argument like any other function. But, any value
returned by the function will be ignored.

 

[Jonas]

Well, it would not be an event procedure in a standard module, but you can
execute code in a standard module from an event.  To do so, the procedure has
to be a Function.  It will not work with a Sub.  Then all you need todo is
put the following in the Event text box in the properties dialog.

=MyFunction()

It can also accept argument like any other function.  But, any value
returned by the function will be ignored.
--
Dave Hargis, Microsoft Access MVP










- Show quoted text -

Dave,

Thanks for responding. It worked. I probably don't have the
foresight that I should have regarding this issue. Do you recommend
that code be set up as you mentioned above rather than putting the
code in the form? Why?

 

[Ray]

I can't vouch for others, but here are the rules that I follow regarding the
placement of code:

Events of forms or events of controls on a form go into that forms module.
Any code that references a control on a form also goes into that forms
module. And any functions/subs that are called only within that forms
module, can also go in the module. In other words, other than event
handlers, all of the subs/functions in a form module are declared as private.

Non-form modules generally contain code that will be called by form modules,
so for the most part will be declared public. Occasionally, I will have a
sub/function that is only intended to be called by another sub/function
within that module declared private (sometimes facilitates a logical
breakdown of work).

For the most part, forms can call subs/functions from modules, but a
sub/function in a module should never call a sub/function in a form.
Likewise, a function/sub in a module should never directly reference a
control on a form, although it can receive a control passed as a parameter.

And a short rant on variables. Public variables are flat out evil, and
should only be used after first smashing your fingers with a hammer.
Seriously...a variable should have the minimum scope possible while still
performing it's duty. Never use a form level variable when a sub/function
level variable will due. And never use a public variable when a private
variable will due.

<stepping down from soapbox>

Ray

 

[Klatuu]

I'm pretty much with Ray on this.
Normally, I only put code in standard modules that will be called from
multiple forms or from queries. Most API code should be in a standard module
by itself.

There is a bit of a trade off, however. The less code a form has, the
faster it will load, but these days with the speed of current computers, that
is not very often an issue.