Combining repetitive code + Accessing global modules 1

[nicgood]

I have a growing Access application that contains a lot of forms & subforms with repetitive code - e.g. buttons to move between records.

What I would like to do is make global subroutines for these common event procedures, and have the various buttons etc just call the global sub instead of the event procedure.

I've tried to do this, by creating a new global module, copying one of the private event procedures into it, changing the name & changing "Private" to "Public".

However when I go back to my form, I'm not able to access this function. My new subroutine name doesn't appear in the event property drop-down list, and if I put "=MySubName()" in the button onclick event property manually, I get an error, usually something like "The expression On Click you entered as the event property setting produced the following error: The expression you entered has a function name that MyApp can't find."

Am I going about things the right way, and if so, how do I reference a subroutine in a global module?

If I'm not doing this correctly, any pointers to the method I should be using to combine all these event procedures (if I can) would be great. (The db is currently over 10mb with virtually no data in it..)

Thanks heaps for any help,
Nicola

 

[danvlas]

Well, the list only displays the macros in the database, not the functions and subs.
To use code, type or select from the dropdown list:

[Event Procedure]

and click the 3 dots (or clear the box and click the 3 dots and select Build Event - Code Builder)

The VBEditor will open and you'll see the event procedure.
Just type:
Call FunctionName(Arguments)

That's all for calling a procedure from an event.

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[nicgood]

Ahh... thanks Daniel. So I can't set the event directly to the procedure then - I *must* call the procedure within the event... is that correct?

Also, another question - where does Access put the code for macros? It's not in my global or form modules and there doesn't seem to be any macro modules..?

Thanks again
Nicola

 

[danvlas]

Macros...hmmm I wish I knew how to read the macros actions from code...searched over and over again and found nothing on the subject. There must be something (how else could they convert the macros to VB) but I myself couldn't find it.

But...if you started with VB, don't bother with macros. The main problem is that you can't trap the errors - they just stop running. There are only two macros that are worth keeping: AutoExec and AutoKeys.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[MarkMcD]

Hi Nicola,

You can put "=MySubName()" directly against the onClick event (instead of "[Event Procedure]&quot provided MySubName is actually a Function and not a Subroutine.

So, in your global module use "Public Function MySubName()" instead of "Public Sub MySubName()".

Cheers,
Mark.

 

[nicgood]

Thanks heaps Mark, that's excellent.

Why can't I call a subroutine from an event? (just curious)

And what happens to the return value of the function? Do I need to do something with it?

Actually, these translated subs-as-functions won't have return values... does that matter?

Thanks so much for all the help!

Nicola

 

[danvlas]

Mark is right...that is possible.

But it doesn't mean you should use the method. Calling a function means loading the module where it resides. Sometimes you only need to do something within form's class module, which is anyway loaded. Why using more resources than needed?

As for the impossibility of calling subs...probably there must be code that is supposed to be available only from code. Usually Subs have the meaning to process data, while functions are used to take parameters, calculate something and return the result.

Not much of a difference between functions that return values and those that don't. Those not returning values are actually "fake" subs created exactly to be accessible from event procedures, menus, toolbars and so on.


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[jrbarnett]

Carrying on from MarkMcD's suggestion, the reason for that is that the difference between a function and a sub is that a function can (but doesn't have to) return a value, whereas a sub can't under any circumstances.
Therefore =Functionname(Parameterlist) means return the value of a function.

John

 

[nicgood]

Hey thanks Daniel. I'm not sure if I am using more resources... it seems less to me?

The situation - I have a main form which has 4 levels of nested forms within it. On about 80% of the forms are the same 4 buttons -- First/Prev/Next/Last Record.

I have one global module that now includes 4 global functions for the above buttons, and I have consequently removed the form modules behind all these forms, meaning my app essentially only loads up one main module now.

Would this really be using more resources? Certainly the file size of the whole thing is smaller.

Does Access unload & reload the global module each time a form using it is loaded?

Or would it keep it open, considering that the main parent form also uses it, and stays open the whole time...?

Thanks again for all the help here, it's fantastic

Nicola