Dynamic Command Bar Buttons

[Paul]

Hi,

I am trying to test the feasibility of a request by some users to be able to
complete a field using a 'Windows style menu with sub menus' instead of a
combo box (they feel there will be too many options to locate the desired
one within the list), but I am hitting some barriers that perhaps someone
can suggest a solution to, or an alternative solution.

My Solution 1.

Use a treeview control on a pop up form.
Cons:
* Ensuring users have correct version of MSCOMCTL.DLL
installed/registered.
* Not really what was requested.

My Solution 2.

Use multiple combo boxes (one for category, one for sub category, one for
the option) that would update as an option is selected from each.
Cons:
* Slow to make a selection (multiple clicks required and difficulty to
check different categories).
* Not really what was requested.

My Solution 3.

Dynamically populate a shortcut menu with the necessary sub menus and items.
This can then be displayed when a button is clicked next to the control to
be completed and the selected control can be populated with the item chosen
from the list. I have got a fair way with this but hit the following
problems.

The only way I can come up with to know which button was actually clicked on
the menu is to programmatically create a function for each button, and set
the OnAction event of the button to that function (which then punches the
selected value into a text box). This needs to be done every time the button
next to the control is clicked in case there have been any updates to the
avialable optoins (held in a table). This leads to the following issues:

1. I will be unable to deploy this as an mdb (or in run time Access?) as
changes to modules are made by the code (the functions being created).
2. When the new code is created and saved, some variables/references appear
to be lost. I have a class module for resizing controls as forms are
resized, this code stops working (fails to fire) after running the routine
that creates the functions and saves the module. This leads me to fear that
other code could also react unpredictably after running the 'function
creation' code (like when code is 'ended' at the End/Debug prompt).


If anyone has any suggestions for other controls to use, or a better way of
determining which button on a dynamically created menu was clicked, please
share your ideas with me.

Many thanks

Paul

 

[Albert D. Kallal]

Hi,

I am trying to test the feasibility of a request by some users to be able
to complete a field using a 'Windows style menu with sub menus' instead of
a combo box (they feel there will be too many options to locate the
desired one within the list), but I am hitting some barriers that perhaps
someone can suggest a solution to, or an alternative solution.

My Solution 1.

Use a treeview control on a pop up form.
Cons:
* Ensuring users have correct version of MSCOMCTL.DLL
installed/registered.
* Not really what was requested.

I think the fact that you will have dll problems distributing a treeview is
a real problem here. Thus, I agree with the above assessment. We are asking
for a build in treeview control....but that is a future request!

My Solution 2.

Use multiple combo boxes (one for category, one for sub category, one for
the option) that would update as an option is selected from each.
Cons:
* Slow to make a selection (multiple clicks required and difficulty to
check different categories).
* Not really what was requested.

What you could do is consider launching a form like the first one here:

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

In the above, you thus get a nice category drill down, and the result on the
right side are a list box which would allow multiple selections.

However, one thing missing in your explains is "where" the multiple results
will go? Those multiple selections don't go into a single field...do they?
(if they are going into a single field...then you can report, search, group
etc on the resulting in that field). Anyway, the above screen shots might
give you some ideas..as the above is what I used in place of a treeview.

My Solution 3.

Dynamically populate a shortcut menu with the necessary sub menus and
items. This can then be displayed when a button is clicked next to the
control to be completed and the selected control can be populated with the
item chosen from the list. I have got a fair way with this but hit the
following problems.

Above is not bad...but you don't have a multi select ability here.

The only way I can come up with to know which button was actually clicked
on the menu is to programmatically create a function for each button, and
set the OnAction event of the button to that function (which then punches
the selected value into a text box). This needs to be done every time the
button next to the control is clicked in case there have been any updates
to the avialable optoins (held in a table). This leads to the following
issues:

Why not create ONE function, and then in the on-action pass the values

=MySelect("One")

=MySelect("Two")

Thus, you can use the function in the on-action to pass a value to a
"general" routine that will know what button was pressed. (or, simply put
the value right in the on-action. Of couse if you are creating the menu via
code..then YOUR code gets to set the on-action value anyway.

1. I will be unable to deploy this as an mdb (or in run time Access?) as
changes to modules are made by the code (the functions being created).

Hum, if your coding the menu bar..and not creating it the standard way (ie:
via drag and drop), then you should be able to make a dynamic menu. (not
sure why this is a problem...but it should work)

2. When the new code is created and saved, some variables/references
appear to be lost.

Hum...don't know why that would be the case.

Anyway, you can pass values from the function in the on-action as above. You
can even use a number

=MySelect(1)

And, then

Public Function MySelect(v as integer)

select case V

case 1
result = "bla bla bla
case 2
result = "bla bal

etc.

end select

However, since you need multiple selections, I would use some type of drill
down (like my example screen shots), and then finally wind up with a nice
little listbox displayed. That will allow the user to click on several
choices. As mentioned, it is not 100% clear where the values selected are
going..

Also, it is not clear if you are launching this selection from a continuous
sub-form..but you also might consider doing that, and then allowing the user
to select only ONE value in the popup process.

My instincts tell me that launching some nice form with drill down for the
user to select is about the best approach here.

 

[Paul]

Hi,

Thanks for your response, some useful stuff there but I don't think I made
my requirements fully clear.

The users only need to select one value, hence the combo plus list, or some
lists side by side is probably the way to go, but I have got myself a little
hooked on the challenge of the dynamic menu bar approach (which is what was
actually requested).

Just to restate my problem with this approach.

In the on action event of a menu (commandbar) button, I can only give the
name of a function, no parameters. If I put in a parameter, accsess treats
it as part of the function name. Hence

CBarCtl.OnAction = "MyFunction(1)"

Actually calls a function called MyFunction(1), not the function MyFunction
with a parameter of 1. I tried this a number of times but it just does not
seem to work the same way as a normal (form) button and I just cannot get
Access to recognise that there is parameter.

My solution therefore was for the first button to set a refrence to a
'scratch' module, delete out all the code, then populate it with functions:

MyFunction_1 for the first button
MyFunction_2 for the second button, etc.

The number I give is actually the ID of the selected option, and all each of
the MyFunction functions do is punch that value into a text box.

This solution has created its own problem in that Access can behave very
oddly when you start programmatically creating and deleting functions and
saving modules.

I suspect that I am up against a brick wall with this approach, but apart
from the darn parameter problem I've got it licked.

 

[Albert D. Kallal]

In the on action event of a menu (commandbar) button, I can only give the
name of a function, no parameters. If I put in a parameter, accsess treats
it as part of the function name. Hence

CBarCtl.OnAction = "MyFunction(1)"

You need like most expressions (such as for a control etc). to put a "=" in
front

So, try:

CBarCtl.OnAction = "=MyFunction(1)"

Of course, if you want to pass a string, then you would need:

CBarCtl.OnAction = "=MyFunction(" & chr(34) & "Hello" & chr(34) & ")"

This solution has created its own problem in that Access can behave very
oddly when you start programmatically creating and deleting functions and
saving modules.

Yes..don't even go there...You do NOT want something that generates code at
runtime...just don't do it!!

 

[Paul]

Thanks,

I have tried

CBarCtl.OnAction = "=MyFunction(" & rsItem!ItemID & ")"

Looking at the menu button created, its On Action property is =MyFunction(1)
so it is not the code that is at fault.

I have also tried entering "=MyFunction(1)" (no quotes) directly into the
menu bar button action property

In all cases I just get messages that "The expression you entered has a
function name that MS Office Access can't find."

It really does appear that you just cannot pass parameters from menu bar
buttons. :-(

Yes..don't even go there...You do NOT want something that generates code
at runtime...just don't do it!!

LOL - I'd seen it done by other people and wanted to figure out how it was
done. Now I've experimented with it a bit I won't be including it in any
apps or ever going there again!

 

[John Spencer (MVP)]

Have you looked at the possibility of using
Screen.PreviousControl or
Screen.ActiveControl
To determine which control is involved

I'm not sure that either will help, but it was a possibility that entered my mind.

 

[Albert D. Kallal]

It really does appear that you just cannot pass parameters from menu bar
buttons. :-(

I been doing this for years...it does work.

Try downloading the 3rd example here:
http://www.attcanada.net/~kallal.msn/msaccess/DownLoad.htm

Try the "features" menu..the last option takes a value from the menu..and
passes it to a function the module called main.

And, if you look at the "cascade" menu example...all 3 options also pass
values to the msgbox function. Check it out....

 

[Paul]

Fantastic! Thank you, thank you.

Having worked out how to code the shortcut menu and data shape the recordset
used to populate it I had obviously used up my quota of brain power for the
day as I managed to put in

Public *Sub* ValueFromMenu(strMessage)

Instead of a Public Function. Thanks for posting the link to your example.
Once I ran that and went back to mine the problem was clear (though I could
start to ask why Sub won't work, I think I'll just stick to being happy that
my dynamic menu is now *functional* <excuse the pun - it is Monday
morning>).

Thanks again,