Custom Menus under Access 2003

  • Thread starter Thread starter Phila
  • Start date Start date
P

Phila

How does one create custom menus for a form under Access
2003? I am looking for something like the MSVB
6.0 'Tools|Menu Editor' to add and edit custom menu items
for a form. If this feature is more convoluted -
requiring a lot of code - where can I see a complete
explanation with example code.

Thanks for your help.
 
To create forms, or reports in ms-access, you use the mouse (and drag and
drop).

While is *possible* to create forms and reports via code, it would be WAY
too much work.

The same applies to menus and tool bars. You simply use the mouse to drag
and drop and create the menu bars.

Once you get the hang of it, you can create some really cool menus in a
matter of minutes. (tip: use control drag when you "raid" the existing menus
to steal buttons...that way you "copy" and not actually "move" buttons.

Of course, those custom menu bars you create WILL call YOUR custom code
(just like a form is built via drag and drop..but often some controls and
things on the those forms uses YOUR code...the same concept applies to menu
and tool bars).

All you need to do is make the code (a function) public, and then simply
place the function name in the buttons on-action event code.

Further, likely often you will have specific code to a particular form, and
once again, you simply declare those functions (in that form) as public.

The syntax to call the code then is:

=YourFunctionName()

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 AskInvoicePrint()

Dim tblgroupid As Long
Dim frmActive As Form

Set frmActive = Screen.ActiveForm

tblgroupid = frmActive.frmMainClientB.Form!ID

If frmActive.InvoiceNumber = 0 Then
frmActive.InvoiceNumber = nextinvoice
frmActive.Refresh
End If

DoCmd.OpenForm "guiInvoicePrint", , , "id = " & tblgroupid

End Function

The above is code that the invoice print button runs. note how I right away
pick up the active form. After that, I can easily ref the forms object as if
the code was running much like the code would if put behind a button on the
form. In the above example, I also check if a invoice number has been
generated before printing. And, the Refresh forces a disk write if in fact I
do change the invoice number. And, in addition the above clip also passes
the currently selected sub-form item that the invoice print form needs.

Also, if the code you write is for the particular form, then as mentioned,
you can simply place the code into the forms module code.
There is no need to pick up the active screen...and you can use me. as you
always used.

Remember there is a bug if you use the full forms qualifier in menu button
code, so
do NOT qualify function name with the forms. This is ok, as then you can
actually
make a menu bar apply to more then one form (if you use a consistent
function
name for each function you have in a form).

If you want to see some sample menu bars screen shots, and why I use them,
you can read
the following:

http://www.attcanada.net/~kallal.msn/Articles/UseAbility/UserFriendly.htm

And, here is a acess97 ref on making menus:

However, the same concepts and ideas apply to a2003 as in the following
article:
http://www.microsoft.com/Accessdev/articles/bapp97/chapters/ba01_6.htm
 
Albert,
Apparently, the menus must be MDI style. How would I
load/unload a custom menu onto the menubar depending on
which form is loaded (i.e. each form has it's own custom
menu items). Also, I would want public functions on the
form to be invoked. When I statically define the menu,
I won't successful invoke a public function on the form.
Access can't find the function if the form isn't loaded
and if it is loaded, then an ActiveX Control Licensing
error is displayed when the menu item was selected.

The onAction value I defined was:
=Forms![Download_Supplier_Data].cmdSubTest_Click()

This type of reference works when invoked via the
immediate window.

I appreciate your generous help.
 
Albert,
Apparently, the menus must be MDI style.
Click to expand...

Yes, unfortunately, all of office (word, Excel, and ms-access) for a very
long time has the mdi style (I think its been this way for at least 10 years
now...a very long time!!). And, of course the apple Mac is still that way
today in terms of forcing the menu bar at the top.

So, we are kind of stuck with this. However, it is not so bad, since all
users are so familiar due to most people being exposed to word, and excel
(both these products are likely the most popular software products in the
world, and they are mdi).
How would I
load/unload a custom menu onto the menubar depending on
which form is loaded (i.e. each form has it's own custom
menu items).
Click to expand...

All you do is specify the menu in the forms "other" tab.
If you do this, then all the menu bar switching is totally automatic, and
requites no code. (if the focus changes, then the menu bar will change
also).
(you need to setup your own main "default" bar for this to work).
Also, I would want public functions on the
form to be invoked.
Click to expand...

Yes, this is why I mentioned as follows::
Remember there is a bug if you use the full forms qualifier in menu button
Click to expand...
code, so do NOT qualify function name with the forms.

What the above means is that any function you define in a forms module as
PUBLIC
will be picked up. So, for example if you have a menu option to delete code,
you
could call

Public Function MyDelete

In the menus's on-action, you place:

=MyDelete()

The beauty of the above system is that the current form with the focus will
have
its public function MyDelete run (in the forms code). And, if you don't have
a
function in the form, then a public function in a star module will run!
(this is great!!).
you can simply place the code into the forms module code (as a public
function in the forms module) In this case there is NO need to pick up
the active screen...and you can use "me." qualifier (as you always used
for button code)..
When I statically define the menu,
I won't successful invoke a public function on the form.
Click to expand...

If the form is loaded, and the function is defined as public, then
the function will (should) run. If the form is NOT loaded, OR YOU
have a function with the same name in a standard module, then
it will be run! (so, if form does not have the function name, then
the function in a standard module is run, or if the form is NOT
loaded, then again, the function in a standard module will run).
Access can't find the function if the form isn't loaded
and if it is loaded, then an ActiveX Control Licensing
error is displayed when the menu item was selected.
Click to expand...

That don't seem right????

Try downloading the following example of mine that has a menu bar. It is the
3rd example here:

http://www.attcanada.net/~kallal.msn/msaccess/DownLoad.htm

Try the above..and see how it works...
The onAction value I defined was:
=Forms![Download_Supplier_Data].cmdSubTest_Click()
Click to expand...

You did not read carefully what said in the previous post, I will repeat::
Remember THERE IS A BUG if you use the full forms qualifier in menu button
Click to expand...
code, so do NOT qualify function name with the forms


You need to use:

=cmdSubTest_Click()

However, I am betting that the above is not defined as public, and further I
am betting that the above is NOT a function
(it has to be a function).

If you are tying to duplicate existing buttons, then create a public
function. You could call it MyTestClick(), and use for the on action:

=MyTestClick()

(again, I stress to NOT use the forms qualify, as it will not work!).

And, your public function in the forms module could be :

Public Function MyTestClick()

Call cmdSubTest_Click()

End Function


good luck!!
 
Thanks to your excellent help, most of my custom menubar
works - except that menu items are activated when the
mouse is positioned over the menu item rather than by
clicking on the menu item. How does one select the type
of event (on mouse click) that activates the menu item's
action?

Thanks again.
-----Original Message-----
Albert,
Apparently, the menus must be MDI style.
Click to expand...

Yes, unfortunately, all of office (word, Excel, and ms- access) for a very
long time has the mdi style (I think its been this way for at least 10 years
now...a very long time!!). And, of course the apple Mac is still that way
today in terms of forcing the menu bar at the top.

So, we are kind of stuck with this. However, it is not so bad, since all
users are so familiar due to most people being exposed to word, and excel
(both these products are likely the most popular software products in the
world, and they are mdi).
How would I
load/unload a custom menu onto the menubar depending on
which form is loaded (i.e. each form has it's own custom
menu items).
Click to expand...

All you do is specify the menu in the forms "other" tab.
If you do this, then all the menu bar switching is totally automatic, and
requites no code. (if the focus changes, then the menu bar will change
also).
(you need to setup your own main "default" bar for this to work).
Also, I would want public functions on the
form to be invoked.
Click to expand...

Yes, this is why I mentioned as follows::
Remember there is a bug if you use the full forms
Click to expand...
qualifier in menu button
code, so do NOT qualify function name with the forms.

What the above means is that any function you define in a forms module as
PUBLIC
will be picked up. So, for example if you have a menu option to delete code,
you
could call

Public Function MyDelete

In the menus's on-action, you place:

=MyDelete()

The beauty of the above system is that the current form with the focus will
have
its public function MyDelete run (in the forms code). And, if you don't have
a
function in the form, then a public function in a star module will run!
(this is great!!).
form, then as mentioned,
you can simply place the code into the forms module code (as a public
function in the forms module) In this case there is NO need to pick up
the active screen...and you can use "me." qualifier (as you always used
for button code)..
When I statically define the menu,
I won't successful invoke a public function on the
Click to expand...
form.

If the form is loaded, and the function is defined as public, then
the function will (should) run. If the form is NOT loaded, OR YOU
have a function with the same name in a standard module, then
it will be run! (so, if form does not have the function name, then
the function in a standard module is run, or if the form is NOT
loaded, then again, the function in a standard module will run).
Access can't find the function if the form isn't loaded
and if it is loaded, then an ActiveX Control Licensing
error is displayed when the menu item was selected.
Click to expand...

That don't seem right????

Try downloading the following example of mine that has a menu bar. It is the
3rd example here:

http://www.attcanada.net/~kallal.msn/msaccess/DownLoad.ht m

Try the above..and see how it works...
The onAction value I defined was:
=Forms![Download_Supplier_Data].cmdSubTest_Click()
Click to expand...

You did not read carefully what said in the previous post, I will repeat::
Remember THERE IS A BUG if you use the full forms
Click to expand...
qualifier in menu button
code, so do NOT qualify function name with the forms


You need to use:

=cmdSubTest_Click()

However, I am betting that the above is not defined as public, and further I
am betting that the above is NOT a function
(it has to be a function).

If you are tying to duplicate existing buttons, then create a public
function. You could call it MyTestClick(), and use for the on action:

=MyTestClick()

(again, I stress to NOT use the forms qualify, as it will not work!).

And, your public function in the forms module could be :

Public Function MyTestClick()

Call cmdSubTest_Click()

End Function


good luck!!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.
Click to expand...
 
Thanks to your excellent help, most of my custom menubar
works - except that menu items are activated when the
mouse is positioned over the menu item rather than by
clicking on the menu item. How does one select the type
of event (on mouse click) that activates the menu item's
action?

Thanks again.
Click to expand...

Golly, I don't have that problem. The menus should behave the same way as
word, or the rest of ms-office.

Did my sample with the menu also do that?

In my startup code for most database, I have the following code:

On Error Resume Next
Application.SetOption "ShowWindowsInTaskbar", False
Application.SetOption "Themed Form Controls", True
Application.SetOption "Show Startup Dialog Box", False
Application.CommandBars.AdaptiveMenus = False
On Error GoTo 0

The "themed Form" stuff is for a2003, you can see the difference between
them turned on, and off here:

http://www.attcanada.net/~kallal.msn/Atheme/index.htm

It could be the adaptive menus that is messing this up...give that try, and
take a look at my example...does it do that?
 
When I ran your word merge example under office 2003, it
didn't result in the usual drop down menu items.
Instead, clicking on the icon brings up a dialog box with
the menu items embedded in a list box which can be
selected. This is not at all like the behavior of
previous versions of Word etc. I didn't see any other
examples on your site that would directly illustrate drop-
down menu items on the MSAccess 2003 menubar. I inserted
you startup code snippet into a stripped down menu
tester - the results are the same: a click on the menubar
opens it and a mouse over any menu item activates it w/o
a click. This nasty behavior kills the concept. In my
simple experiment I defined one table with one field and
one form associated with the table and a menubar with two
menu items with actions to separate public functions. Do
you have something like this example that works as
advertised? I could try it on my end to see if my
installation of MSAccess is brain dead.

Thanks again.
 
I assume you converted that sample in the link to a2003...right?

Yes, when you run it, auto launches a main form with a few options.

however, if you look at the menu bar, there is only one option

File


However, placing your cursor on the above "File" does not cause it to fire.

Further, if you click on the file, then the menu drops down, and you get two
options:

File
->>>View Maim Form
->>>Exit Application

So, try clicking on the "exit box" on the form that appears (that is in the
lower left hand of this main form). the form now closes.

Now try:

File-
-> View Main Form

Once again, the menu (as far as I can tell) works like a regular menu, and
the "view main form" does NOT fire until you click on it
If you click on it...the main form appears.....

Do you experience the above?
 
In fact, just for you, I just took all the button code and MOVED them to the
menu bars in that sample.

So, try re-downloading that example. (the third one here:)

http://www.attcanada.net/~kallal.msn/msaccess/DownLoad.htm

Make sure you try all the options (you don't need to try the word merge),
but try each buttion and then also try each menu option.

The orignal example only had a "file" menu. Now, I just simply moved up all
the buttions to the menu bar.

Try it...and also try the report one, as it shows how a report looks.......

You can see how the menu bar switches, and shows differnt options/menus for
each form....
 
Sorry I spent too much time looking at the Word Merge and
missed the "File" menubar. Your menubar/menu items work
as advertised; however, when I try to add another menu
item to your menubar, it has the problem I originally
stated.

I follow the standard procedure to add the menu item
using Tools/Customize/Commands/New Menu with drag and
drop into the slot under your previous menu items.

The notable difference is that the new menu item has an
arrow on the right side with a gray box to drop menu
items into and all the right-click options are disabled
except for Delete, Name:, Begin a Group, and
Properties. Looking at it's properties, Shortcut Text
and Style are disabled.

Your menu items don't have the arrow on the right side
and all right-click options are enabled except 'Reset'
and all property settings are allowed.

I don't see any MSAccess options to control the menu item
style.

What's your prognosis?
 
Sorry I spent too much time looking at the Word Merge and
missed the "File" menubar. Your menubar/menu items work
as advertised;
Click to expand...

As mentoend, if you re-download, you can now see an appcltion, and I move
all buttions up to the meu bar

It will now give a good "fell" for what menus look like/function.
You can play with this. (if anything, you most ceranly want to see/try the
reprot, as it has a LOT of fuctions on it,
and I use that menu bar for all my reprots).

however, when I try to add another menu
item to your menubar, it has the problem I originally
stated.
Click to expand...

The question is what do you want to add?

Anohter menu (if yes, then obivliery an arrow will appear to "expand"
(this would give you a casecade menu). Most people have trouble
creating these...but you are doing it by accident!

So, yes, when you create your first menu bar, then each dropdown for the
menu bar is a menu.
I follow the standard procedure to add the menu item
using Tools/Customize/Commands/New Menu with drag and
drop into the slot under your previous menu items.
Click to expand...

Yes, that makes sense. However, if you just want a command, then don't put
a menu there (menus do fire by thems selfs, and menus are menus).
So, then you would simply
from the customize menu go:

Commands tab->
(the "File" on the left side shold be by defalt select)

Then you drag and drop from the "right" side, the first selection which
should be "custom"
(we want a custom buttion here).

Once you place the custom button where ever you want, then right click on it
for properties....

You can examine my examples. Often, a lot of the time you can use built in
buttons (for example to launch another form, you just select:
"All Forms" in the categories widows, and then on the right side you can
drag and drop the button into your bar.

In my sample, some buttons are made by this drag and drop to open a form,
where as others simply run code....
 
Back
Top