Menu/Toolbar Command Best Practices

[Jack Leach]

I was wondering if anyone could advise on some sort of 'best practices' for
organizing the command procedures for various custom menus and toolbars.

I've done this by each command as a seperate public function to do what I
need, but its a lot of public functions to have laying around. I've done it
by having one public function and passing a long to it as a command ID, but
that's a lot of select case statements to go through in one function... not
the least bit efficient.

I'm now considering storing the cmd procedure in a table. One public
function with the cmd ID passed to it, a table lookup against the ID to pull
the required procedure, and then Eval to run the procedure, which could at
that point be private to the module. It seems like it would be well
structured with minimal overhead.

Any thoughts?

thanks,

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Graham Mandeno]

Hi Jack

I don't see any problem with "a lot of public functions to have laying
around".

There's really no advantage that I can see in using private functions over
public ones. In fact I would say it was rather the reverse. If you call a
public function from a CommandBarControl then you can call the same function
from, say, a command button on a form if you want to.

And I think that seeing =PrintJobSummaryReport() in the properties sheet of
a CommandBarControl is a lot more informative than =MyGeneralFunction(42).

 

[Jack Leach]

That works. I didn't know if there was some performance increase for not
having all the functions public (I guess overhead isn't all that much
relatively, and it probably takes longer to process the table fetch than find
a preloaded function).

Just wondering how the pros do it.

Thanks,
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Hi Jack

I don't see any problem with "a lot of public functions to have laying
around".

There's really no advantage that I can see in using private functions over
public ones. In fact I would say it was rather the reverse. If you call a
public function from a CommandBarControl then you can call the same function
from, say, a command button on a form if you want to.

And I think that seeing =PrintJobSummaryReport() in the properties sheet of
a CommandBarControl is a lot more informative than =MyGeneralFunction(42).

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I was wondering if anyone could advise on some sort of 'best practices' for
organizing the command procedures for various custom menus and toolbars.

I've done this by each command as a seperate public function to do what I
need, but its a lot of public functions to have laying around. I've done
it
by having one public function and passing a long to it as a command ID,
but
that's a lot of select case statements to go through in one function...
not
the least bit efficient.

I'm now considering storing the cmd procedure in a table. One public
function with the cmd ID passed to it, a table lookup against the ID to
pull
the required procedure, and then Eval to run the procedure, which could at
that point be private to the module. It seems like it would be well
structured with minimal overhead.

Any thoughts?

thanks,

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Albert D. Kallal]

I suspect before you used a lot of custom menu bars, or now the ribbon, I
assume that the standard practice was to place a bunch of buttons on the
bottom of the form that you click on,and some code runs that belongs to a
particular form in question.

So as you start moving all those buttons up to custom menu bars, I assume
that your code remained in the form's code module? correct??

When you say public functions for a menu bar, we are usually talking about
code that belongs in a particular form, and therefore you don't just Willy
nilly throw functions all over the place into a public code module. One
would assume that you place the public functions for a given forms menu bar
in the given forms code module. This approach will significant reduce code
clutter, and placing functions that belong with a given form in its code
module means you can move the form to another application, or copy the form
and all the appropriate code/functions for the given form in question will
move with that form.

I would say about 90% of my custom menu bars now don't run macros, and in
fact simply just run public functions in a given form. You can specify the
name of a public function in the menu bars on action as follows:

=NameOfPublicFunction()

Often, (if not most of the time), you code you call will need to pick up
some information about the current screen etc. So, my code most of the time
starts out, and grabs the current screen name. I use:

Public Function MyDelete()

Dim frmActive As Form

Set frmActive = Screen.ActiveForm

If isnull(frmActive!ID) = false then
. code here to prompt and ask user to delete the record


However, what happens if for 10 different forms, the 9th form does NOT use
"id" For the primary key in the table? Or for that one particular formed you
have an advanced delete routine that has to do a bunch of extra code or
check for child reocrds etc. What this means is that for that one particular
form, you want to different =MyDelete() code to run, yet you want to be able
to use the SAME custom menu bar.

The simple solution is to place your custom delete code (public function) in
the form's code module. This means is that for all forms that do NOT have a
public function MyDelete, the one fucntion in the standard code module will
run, but for the ONE form, the MyDelete code in the forms code module will
run.

The above situation is ideal. Access attempts to run the public function
from the menu bar in the form code module that has the FOCUS. If that form
code module does not have have the public function, then the code in a
standard public code module is run. This means is that you can have a custom
menu bar that runs completely different code for different forms. And for
ALL those forms that don't have a particular menu function in their
respective code module, then the standard fallback position of running the
function that exists in a standard non forms code module will run!!

So the above should give you the design methodology and approach as to how
this can done. You should not be having a bunch of functions strewn all over
in a public code module that ONLY belongs to one form and one particular
menu bar.

You ONLY want to use public functions in a standard "module for menu bars
for functions that are truly global in nature and that will operate for all
forms, or at least those forms with that menu bar specifed.

For any custom menu bar + code that belongs to a given form, then those
public functions should be placed inside of that particular forms code
module.

 

[Jack Leach]

Hi Albert, thanks for the reply.

Actually my question pertained only one menubar. I use a "global" menubar
at the top of my applications that is there all the time (with certain menus
displayed for certain users). As far as forms go, I don't use menu/toolbars,
all commands on the forms are executed from commandbuttons (which may then be
rerouted to a public function, depending, but that's fairly cleaned up).

My MenuBar, the one that replaces the standard access menubar, is what I'm
working with. There isn't so much "add new record" or "calculate PO total"
type commands as you might see on a form's bar, but rather commands like
"open purchasing module" or "backup database" "login/out" (e.i. - global
commands, no specific form required).

These are the commands I'm wondering the best way to handle. As of now,
these commands are all in a module called modCmds, and the 40 or 50 of them
that there are aren't exactly as tidy as I would like (each procedure is
fine, but the module on a whole is more of a cluster than I prefer), but I
suspect that this is where they'll stay for the time being. Possibly I can
move some of these commands into some form menus, but I do prefer to have the
user have access to every "major" thing they need without the requirement of
opening forms to do it.

Thanks,

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)