Access custom right-click menus

Question

I have written an app that makes extensive use of custom right-click menus on an Access form. The code works great and the user loves it, but lately I am having trouble making it work properly.

In earlier versions of Access it worked well, but newer version seem much more limited in how many items can be put in such menus. The documentation is silent on the matter, and nobody in any newsgroup has had any useful ideas, but I regularly get random error about stack space, out of memory, and general lockups when populating the menus. Doing a C&R used to help, but now even that is not enough, and some menus I can no longer populate at all.

I tried building an app that just built menus until it crashed, to get some idea of what the limits may be, and I am well below what that indicated, but the experimental app had nothing else, while the real app has a great deal else.

Is there any information on how much stuff can be put into these menus, and what the menus share space with? There may be something I can do another way to make more room for the menus. I tried moving all code out of the form, leaving only event stubs that called routines in standard code modules, but that did not help.

And how are they stored/activated? The app is MUCH slower to load when it has these menus, even though no code is running on start-up.

********** Edited to add this:

I use VBA to create a menu, like this:

Application.CommandBars.Add "RCStat", msoBarPopup, False, False

then add it to a control. like this:

Application.CommandBars.cboStat.ShortcutMenuBar = "RCStat"

I add controls (only popups and buttons) like this:

Application.CommandBars.Controls.Add(type:=msoControlPopup)
Application.CommandBars.Controls.Add(type:=msoControlButton, Parameter:="StatKod = 77")

It runs perfectly and the menu items work exactly as expected, except that it bombs after adding some number of controls. It doesn't seem to matter where I add them, just the total number of added controls hits some undefined threshold, and the app crashes.

I got the original code from Getz, Litwin and Gilbert, 2000 edition. Back then, it worked great. But as the versions advance and the app accumulates data, it is becoming less and less functional. However, there are only around 10,000 records, and the app itself is less than 100MB - nowhere close to any of Access's upper size limits.

Answer 1

Pete, I've done quite a bit of work with shortcut menus, and created the Access Shortcut Tool about 5 years ago, but have never attempted a menu with so many controls although some have 3 or 4 levels.

I am not aware of any restrictions on the number of elements in the commandbars collection, but I find that shortcut menus with too many options, like lists and combo boxes with too many items, are difficult for users to navigate. I generally break these up into segments and use buttons in the form header to display the appropriate menu. Sorry I'm unable to provide anything more helpful.

Dale

Answer 2

We have a commercial product, Total Access Components, that includes as one of its 30 components a right click popup menu that can include icons and font styles.

Here's the info for the popup menu control: https://fmsinc.com/MicrosoftAccess/controls/components/popup-menu/

There's a free trial if you want to try it.

Answer 3

Normally I would agree, that a menu construct of such size is unnecessarily complex and unwieldy, and likely a fundamental design mistake. However, these menus all do exactly one thing – filter the main dataset. Their structure reflects the contents of the dataset on that field, and their captions provide some useful information on the contents.

As an example, there is a date field. It is not a normal date field, but allows entry of year, year-month, and year-month-day, to allow for information not known to full accuracy. I could use some standard date-picker mechanism, but that requires the user to enter a date, or at least select one from some graphic. If there are no records with that date, that is wasted effort, and leads to frustration. My right-click menu solves this very nicely, by offering ONLY the dates that exist in the dataset. They are arranged hierarchically and chronologically, so that following the menu is very intuitive. The first level is a decade block, since the number of years in the dataset is large enough that it would not fit on the screen, and so would require scrolling the RC menu, which I try to avoid. Once a decade is selected, the second level is the year. When there is only a single value in the dataset for a specific year, the selection ends there, no matter whether the value is only year, year-month, or year-month-day. The control for that value is a button, with the data to the accuracy it was entered as the button's caption, and a count in parentheses of how many records contain that value. In the screenshot, you can see one such example as the first level 2 element – date 1983-VII-28, one record.

If there are more values for the year, only the year is displayed as the caption of a popup, and the third level goes into more detail. If there are only two values, the third level contains two buttons, each with the appropriate caption and count. If there are more, it then depends on how the date data has been entered. If all the dates are from a single month, or a bare year and single month, the third level contains only buttons, each with the appropriate caption and count. The first one or two items in the level are always 'summary' buttons: the first always for the entire year, and the second for the bare year, if there are records that have only the year. If there are not, that button does not exist.

If the dataset contains more than one month for the year and at least one month has more than one day, the RC menu expands to a fourth level. The third level contains popups for each month that has more than one day, as well as buttons for only month, or month with only one day. The fourth level starts the same way as the third, with one or two 'summary' buttons, this time for entire month and only month, then buttons for each day in that month, again, all with appropriate captions and counts.

As records get edited, this structure also gets modified to always reflect the exact and complete contents of that field in the dataset, including shifting controls up and down the structure, changing buttons to popups and popups to buttons, as dates get modified, added or deleted.

I know it sounds complex, and it was a bear to get coded right, but it works wonderfully, and the user (singular) loves it. In operation, it is extremely simple to use – a single right-click on the combo box containing the RC menu, then follow the data displayed as captions as far as she needs to. And if she is interested in only the count, which happens fairly often, she has it instantly, with no further action necessary. The RC menus have the great advantage of unrolling automatically, as the user mouses over popups, instead of requiring additional clicking or typing, which is very pleasant to use.

It used to work very nicely, and still does, when I can get the menus loaded. However, I am more and more running into this capacity issue, and I don't understand why. If it continues to get worse, I will have to disable this system entirely. Given how much work it took to get it operational, and how much the user likes it, I am very reluctant to give up on the design.