Repeated rebuild of right-click menu throws error (1 Viewer)

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
I have an Access application that was originally built in Access 2000. Several textboxes and comboboxes use custom right-click menus to call filter routines. The RC menus are built in code, and some are quite extensive - hundreds of items, nested up to four levels deep.When data in a field changes, I need to rebuild the menu for that field, because the menus filter on the database's actual contents.

In 2000, it all worked well, but in newer versions, repeated rebuilds throw an error.

The relevant part of the code is here:

Code:
Dim rst0 As DAO.Recordset, rst1 As DAO.Recordset, rst2 As DAO.Recordset, rst3 As DAO.Recordset, qdf0 As DAO.QueryDef, qdf1 As DAO.QueryDef, qdf2 As DAO.QueryDef, AEp As CommandBarPopup, AEp1 As CommandBarPopup, AEp2 As CommandBarPopup, aec As CommandBarButton
    With Application.CommandBars("RCDatum")
        Do Until .Controls.count = 0
            If .Controls.count Then .Controls(1).Delete
        Loop
        Set rst0 = CurrentDb.QueryDefs("RCDatumDekady").OpenRecordset(acReadOnly)
        Set qdf0 = CurrentDb.QueryDefs("RCDatumRok")
        Set qdf1 = CurrentDb.QueryDefs("RCDatumRokMesic")
        Set qdf2 = CurrentDb.QueryDefs("RCDatumRokMesicDen")
        Do
            qdf0.Parameters("LoRok") = rst0.Fields("Dekada")
            qdf0.Parameters("HiRok") = rst0.Fields("Dekada") + 9
            Set rst1 = qdf0.OpenRecordset(acReadOnly)
            Set AEp = .Controls.Add(msoControlPopup)

The last line is where it bombs with the error "Unable to add control", but not always. It runs several times, but after about four or five rebuilds, it suddenly starts throwing an error on that line. Shutting it down, doing a C & R and retrying makes it work again, and again several times okay, then starts bombing.

I sort of suspect I'm abusing the RC system, that it was intended for a few items, more or less permanantly left in place, not such intensive build/rebuild activity with so many items, but I've not found any documentation on the subject, or anyone who claims to have experience with it.

Anybody have any ideas?
 

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
Whilst occasional use of C&R is a good thing, having to do so repeatedly is generally an indication of problems.
You haven't said which 'newer versions' you are using but it is true that each new version has been less tolerant of code errors than the last.
You may have corrupted code - recommend you try DECOMPILING ?

I can't make specific comments on your code as I've not tried to do anything like this using command bars for several years. If you are using ACCDB files, I would expect there are different ways of doing this that might be more appropriate. Hopefully someone else with experience of this type of coding can advise on a way forward.
 

Minty

AWF VIP
Local time
Today, 10:05
Joined
Jul 26, 2013
Messages
10,353
I sounds as if the constant rebuilding is filling up a background object limit?

Forms can only have a set number of controls, so there may well be a similar limit on the command bar object, doing a C & R would "clean out" the deleted entries and hence let it work again?
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
I sounds as if the constant rebuilding is filling up a background object limit?

Forms can only have a set number of controls, so there may well be a similar limit on the command bar object, doing a C & R would "clean out" the deleted entries and hence let it work again?

That's kind of what I'm thinking, but I have found no documentation on the subject. I'm hoping someone here knows something about it.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
Whilst occasional use of C&R is a good thing, having to do so repeatedly is generally an indication of problems.
You haven't said which 'newer versions' you are using but it is true that each new version has been less tolerant of code errors than the last.
You may have corrupted code - recommend you try?

I can't make specific comments on your code as I've not tried to do anything like this using command bars for several years. If you are using ACCDB files, I would expect there are different ways of doing this that might be more appropriate. Hopefully someone else with experience of this type of coding can advise on a way forward.


I'm using 2007 and 2010, both 32 and 64 bit versions.

Yes, I decompile regularly, so regularly, in fact, that I have a .BAT file to do it alongside every app I work on. Besides using it whenever an app starts misbehaving, I always do that and recompile, and C & R as the last step before releasing an app to a customer. I wish MS would put that in as a regular feature, just like the C & R button. Anybody that develops in Access a lot needs it frequently.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
One thing I suppose I could try is to build a very simple form with a single control with a right-click menu, and just load it up until it crashes. If I try various build configurations and it always crashes at or around the same number, that would give me an upper limit on the number of items. If I then try a smaller number total, and continually rebuild it, that might give me a number of possible rebuilds, like the total number of controls a form can have. That would be a fairly simple experiment and might provide some positive answers, which my searching around the net has so far failed to produce.
 

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
I also decompile before releasing apps to clients and use a desktop shortcut for that purpose. However having to do so very frequently is another indication that there are issues with your applications.

I have just one app which needs decompiling more often. Possibly once a fortnight at most. That has very unusual features which require use of many make table queries based on downloaded data from different online sources. Doing that repeatedly can cause instability which decompiling solves as and when needed.

I'm not totally clear why you need to keep rebuilding your context menu. What exactly has to change when data is added or edited? Is it possible for you to upload a cut down version of your app so forum members can look at it and advise as otherwise I think people may be guessing in the dark.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
I also decompile before releasing apps to clients and use a desktop shortcut for that purpose. However having to do so very frequently is another indication that there are issues with your applications.

I have just one app which needs decompiling more often. Possibly once a fortnight at most. That has very unusual features which require use of many make table queries based on downloaded data from different online sources. Doing that repeatedly can cause instability which decompiling solves as and when needed.

I'm not totally clear why you need to keep rebuilding your context menu. What exactly has to change when data is added or edited? Is it possible for you to upload a cut down version of your app so forum members can look at it and advise as otherwise I think people may be guessing in the dark.

The app doesn't need decompiling or C & R in regular use. -I- do that frequently when developing, because I try all sorts of odd things, and make lots of mistakes in the process. I have batch and script files for a number of such regular tasks, including making archive copies to a remote server with lots of free disk space, which I run frequently, so that I can ditch a blown version and go back to a previous working copy. It's saved my bacon, more than once.

The reason I need to rebuild the menus is that they are based on the database's actual contents.

For instance, one of the RC menus is for a 3-piece date field (day, month, year). I can't use a regular date field, because there was some legacy data that I needed to import, and that had partial dates, like only year and month.

The RC menu has a 4-level cascade - I'd add a screenshot, but I'm new here and not allowed to add images yet, so I'll have to describe it. The first level is decade 1860-1869, 1870-1879, 1880-1889 and so on. Those intervals are hard-coded, except that the upper and lower range of the decade blocks is taken from the extreme ranges of the year field. With each decade block, there is the next level down to individual years, and there it starts being selective. If there is nothing in the database from the year 1972, for instance, the block 1970-1979 will not have a next level for that year. But if the user enters a new record with that year, or edits an old one from a different year to that year, the RC menu needs to be updated to reflect that. There are two more levels - all months within a year, and all days within a month. But again, only those that actually exist in the database are available as a menu option. If there is nothing in the database for a particular day, that date will not appear in the RC menu. There are also leading group filters on the second and third level, labelled 'Entire year' and 'Entire month', but again, only if there is something in that year or month.

It allows the user to very quickly filter the entire database by content. One right-click on any of the three date fields and this menu pops up. She needs only mouse over the expanding selections until she hits the proper date, then one more click and the entire database is filtered for that date. The cascade keeps it all to manageable size (no scrolling through the RC popups), which was the primary reason for the decade groups - there were too many years to fit on the screen at once - the decades level kept it compact. The RC mechanism is very quick and very smooth - only one click to start the process and only mouse-over from then on, until the final selection. The user loves it and I'm fairly proud of it - I really don't want to give up on the idea.

My previous version of this just set a flag, and did the menu rebuild on closing, along with a C & R, because the rebuild took a while. I didn't like it, because it didn't respond in real time to changes. Forcing the user to close and re-open the entire database to bring its behavior current is not an elegant solution. However, newer hardware and some tuning of the code has gotten it down to acceptable run times, except for this crashing issue. I can't release it like this, and I really don't want to go back to the closing flag, so I'm trying to figure out what is wrong, and how to deal with it.
 

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
OK I think I understand it but a picture or better still a cut down version of the actual app will still be beneficial. As you will have ten posts with your next reply, you can upload files ...but you could have done that anyway by zipping them.

If I had gone to all that effort, I'd also be proud of the result and not want to give it up lightly BUT I have to ask whether you have considered using cascading combo boxes. That could achieve very similar results without any of the issues you describe.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
OK I think I understand it but a picture or better still a cut down version of the actual app will still be beneficial. As you will have ten posts with your next reply, you can upload files ...but you could have done that anyway by zipping them.

If I had gone to all that effort, I'd also be proud of the result and not want to give it up lightly BUT I have to ask whether you have considered using cascading combo boxes. That could achieve very similar results without any of the issues you describe.

Okay, here is a screen shot. The text is in Czech, but it should be obvious what's going on. I captured the menu expanded out to four levels.

Your idea about cascading comboboxes is not at all a bad one - I've used that technique in other places. In fact, this very database uses it - the top two boxes, with the yellow background are just such a construct. Genus and Species, and the genus selection determines what shows up in the species box. It works well, but I don't want to have four more controls on this form. Besides the extra clicking around that it would require, as you can see, it's already pretty crowded. The map on the right side takes up a fair amount of room, and is crucial to the functioning - as a record appears on the screen, the map displays dots in every quadrant where that same genus and species is found.

The RC menus are absolutely perfect for this job - fast, unobtrusive and simple to operate, if I can just get past this crashing problem.
 

Attachments

  • LichenCascade.jpg
    LichenCascade.jpg
    100.6 KB · Views: 413

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
Thanks for the screenshot. You described it very well in the previous post.
It is a bit crowded but I still would use cascading combos myself. Very little screen space needed.
I can't read Czech but the idea is clear enough.
I do know what an Acer Pseudoplatanus is however. Don't think I've ever seen lichen on the bark of any of my Acers. Cotinus Coggygria Purpurea is a different story as that is smothered in lichens.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
I can hash up the data in the tables and send along the actual database, if you think it will help, but I can't do it tonight. Messing up the data to the point where it is not identifiable will take a bit of time, and I have some people waiting for something right now.

I don't care about the code - anybody finds something useful in it, they are more than welcome to copy my work. I share openly on tech forums, and have benefitted immensely from other people doing the same. But the scientific data inside is another matter. Mainly, it's not mine to share, and scientists can get quite upset about their data getting loose. I will have to gum it up to the point that I can be certain it is completely obfuscated.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
Thanks for the screenshot. You described it very well in the previous post.
It is a bit crowded but I still would use cascading combos myself. Very little screen space needed.
I can't read Czech but the idea is clear enough.
I do know what an Acer Pseudoplatanus is however. Don't think I've ever seen lichen on the bark of any of my Acers. Cotinus Coggygria Purpurea is a different story as that is smothered in lichens.

Acer Pseudoplatanus is a sycamore maple. Lichens may or may not grow on any particular tree - some of them are extremely fussy about conditions, and even a small change in temperature, humidity, sunlight, acidity or any number of other things can suddenly make it inhospitable for a particular species. That is one reason why lichens make excellent biomonitors. They react to pollutants, and small changes in a city's air quality can mean an enormous change in lichen populations.

As for the comboboxes, maybe I'm not understanding how you think I could use them. As far as I know, besides the data field, I would have to have (in this case) four more comboboxes, to accomodate the four levels of nesting. I don't know where I would put them - the large green space at the bottom is an artefact of the monitor on which I happen to be writing this. The user's machine does not have that. And remember - these boxes would not be for data entry, as are the genus/species boxes on top, but for filtering existing data. And I would need such a set for every field that uses these RC menus - they are on other fields besides the date. Even if I had a room to put them all, I worry that the user would get confused between boxes for data entry and boxes for filtering. But maybe I don't understand your idea properly. Could you maybe explain it a bit? Or draw some rough idea of how you think it could work on that screenshot? I can send another screenshot without the menus expanded, if that would help.
 

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
I think your RC context menus will be interesting to others....assuming the crashes can be fixed...but I think a little tweaking could be done to fit 4 cascading combos in a row next to (or instead of?) the date.

BTW
1. I'm not sure if its relevant but there is a parallel thread about altering form design in form view. See https://www.access-programmers.co.uk/forums/showthread.php?t=304507
2. I see you've crossposted at http://www.accessforums.net/showthread.php?t=76389
In future please state you have done so and provide the link … at both sites.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
I think your RC context menus will be interesting to others....assuming the crashes can be fixed...but I think a little tweaking could be done to fit 4 cascading combos in a row next to (or instead of?) the date.

BTW
1. I'm not sure if its relevant but there is a parallel thread about altering form design in form view. See https://www.access-programmers.co.uk/forums/showthread.php?t=304507
2. I see you've crossposted at http://www.accessforums.net/showthread.php?t=76389
In future please state you have done so and provide the link … at both sites.

Okay, I'll post it when I get the data fixed up.

AccessForums is a completely different site - do you think linking to unrelated forums is proper? I've also asked the question on StackOverflow, because I've actually been battling this for quite a while, and always ended up with nothing. For a while I was getting an Out Of Memory error, but I wasn't getting it on any particular line of code. It wasn't a trappable condition - it would just start throwing that error after a menu rebuild, sometimes even even when no code was running. This is the first time I've gotten it down to a trappable, repeatable error, on a specific line of code, which is why I have some hope that I may be able to track it down this time.

I'd like to see how you think those comboboxes would fit and work. I can't put them INSTEAD of the date - that's where the user enters the actual data, after all. There are a few things that are not visible in the previous screenshot. I've included one in design view, so you can see everything on the form. I really don't see where you think I could fit these four extra boxes. Can you elaborate? Also, would these boxes not require clicking into each one? The RC menus do not need that - only one right click to start the process, then simply mouse-over to expand further, or contract back down again. Avoiding unnecessary clicking is one of the reasons I like this so much.

And again, I would need an extra box or set of boxes for EVERY control that has an RC menu.
 

Attachments

  • LichenCascadeDesignView.jpg
    LichenCascadeDesignView.jpg
    99.9 KB · Views: 412

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
There is no objection to cross posting providing you follow the guidelines
Please read https://www.excelguru.ca/forums/showthread.php?518-Article-A-message-to-forum-cross-posters

I am suggesting cascading combos as an alternative that will work without crashes. Your solution sounds neat but is no longer working...at the moment anyway.
I agree that combos will need a few clicks but I've used them in multiple apps and they work well.
You could look at my cascading combos example which has five of them http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723

BTW I do know about lichens and how they are used as a pollution indicator
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
I am suggesting cascading combos as an alternative that will work without crashes. Your solution sounds neat but is no longer working...at the moment anyway.
I agree that combos will need a few clicks but I've used them in multiple apps and they work well.
You could look at my cascading combos example which has five of them


Thank you, I looked at that. Obviously, such a technique works well, but as I wrote earlier, it uses an unacceptable (for me) amount of screen real estate, and necessitates lots of extra clicking. I appreciate the link, and I know how to do that, but I don't want to. The reason I started this thread is that I want to figure out how to make my RC technique properly functional. I've thought of several ways to address the problem in the interim. All will be a good bit more work, but I believe they look promising.

First is to build a test DB that will explore limits of the RC system - build an RC menu and keep adding controls until it crashes. Running that with a number of variations in control type, number and nesting level should give me some indication of what the hard limits are. Then I can back away from that limit a bit and try continual rebuilds, again until it crashes. That should tell me if there is some upper limit on the number of add/re-add operations.

Finally, I can do a proper job of managing the menus: (1) simply change the caption of controls whose underlying data has changed; (2) insert only the new control into the proper place when a new value is added to the database, and remove only the proper control when the last instance of a value is removed from the database. There is no need to rebuild the entire menu every time. I did it that way initially, because it was simpler, and I didn't know that much about what I was doing when I first wrote this app. I've learned a lot since then. It will mean a good bit more code managing the menus, and tests for detecting when the last item disappears from a level, and therefore the parent item for that level should also be removed, but it will be a much cleaner and faster menu update, and will probably mean an end to the crashing, because I will be only doing at most a few menu item inserts and deletes, rather than the hundreds or even thousand I try to do currently.

Finally, I munched up the text data in a copy of the database, and wanted to post it here, but even zipped, it's over 6MB. The size limit for attachments is 2MB. I had to zip it, split it (using https://pinetools.com/split-files), and rezip the split pieces before I could attach it. You will need to unzip the pieces, join them with the file join tool on that same site, and unzip the result. The only fields that currently have an RC menu are the two bordered in purple. They should let you play with it enough to see how it works. I welcome any suggestions you may have, as long as the suggestion is not to give up on the concept. And if you see anything in there that you think might be useful to you in your own work, help yourself. If my efforts can be of benefit to someone else, I am only pleased.
 

Attachments

  • Lichen.zip.000.zip
    1.5 MB · Views: 422
  • Lichen.zip.001.zip
    1.5 MB · Views: 408
  • Lichen.zip.002.zip
    1.5 MB · Views: 408
  • Lichen.zip.003.zip
    1.5 MB · Views: 398

essaytee

Need a good one-liner.
Local time
Today, 21:05
Joined
Oct 20, 2008
Messages
512
I have an Access application that was originally built in Access 2000. Several textboxes and comboboxes use custom right-click menus to call filter routines. The RC menus are built in code, and some are quite extensive - hundreds of items, nested up to four levels deep.When data in a field changes, I need to rebuild the menu for that field, because the menus filter on the database's actual contents.

In 2000, it all worked well, but in newer versions, repeated rebuilds throw an error.

The relevant part of the code is here:

Code:
Dim rst0 As DAO.Recordset, rst1 As DAO.Recordset, rst2 As DAO.Recordset, rst3 As DAO.Recordset, qdf0 As DAO.QueryDef, qdf1 As DAO.QueryDef, qdf2 As DAO.QueryDef, AEp As CommandBarPopup, AEp1 As CommandBarPopup, AEp2 As CommandBarPopup, aec As CommandBarButton
    With Application.CommandBars("RCDatum")
        Do Until .Controls.count = 0
            If .Controls.count Then .Controls(1).Delete
        Loop
        Set rst0 = CurrentDb.QueryDefs("RCDatumDekady").OpenRecordset(acReadOnly)
        Set qdf0 = CurrentDb.QueryDefs("RCDatumRok")
        Set qdf1 = CurrentDb.QueryDefs("RCDatumRokMesic")
        Set qdf2 = CurrentDb.QueryDefs("RCDatumRokMesicDen")
        Do
            qdf0.Parameters("LoRok") = rst0.Fields("Dekada")
            qdf0.Parameters("HiRok") = rst0.Fields("Dekada") + 9
            Set rst1 = qdf0.OpenRecordset(acReadOnly)
            Set AEp = .Controls.Add(msoControlPopup)

The last line is where it bombs with the error "Unable to add control", but not always. It runs several times, but after about four or five rebuilds, it suddenly starts throwing an error on that line. Shutting it down, doing a C & R and retrying makes it work again, and again several times okay, then starts bombing.

I sort of suspect I'm abusing the RC system, that it was intended for a few items, more or less permanantly left in place, not such intensive build/rebuild activity with so many items, but I've not found any documentation on the subject, or anyone who claims to have experience with it.

Anybody have any ideas?

Just throwing this out there, where you loop through every control within the 'RCDatum' commandbar and delete, why not delete the 'RCDatum' commandbar directly; no need for the 'Do Until' code. Code I use, though not nearly as many items as what you require, is as follows:
Code:
    Dim strBarName As String
    strBarName = "rcm_frm_Car_Bookings"
     
    On Error Resume Next
    CommandBars(strBarName).Delete
    On Error GoTo 0
 
    Set cmbRC = CommandBars.Add(strBarName, msoBarPopup, False)
    ' then go about adding your controls
I don't know if the above method resolves your issue but, maybe it's worth a try. It's one delete as opposed to hundreds or more. Maybe the system wouldn't count the deletes of the individual controls, I don't know, just speculating.
 

Petr Danes

Registered User.
Local time
Today, 11:05
Joined
Aug 4, 2010
Messages
150
Just throwing this out there, where you loop through every control within the 'RCDatum' commandbar and delete, why not delete the 'RCDatum' commandbar directly; no need for the 'Do Until' code. Code I use, though not nearly as many items as what you require, is as follows:
Code:
    Dim strBarName As String
    strBarName = "rcm_frm_Car_Bookings"
     
    On Error Resume Next
    CommandBars(strBarName).Delete
    On Error GoTo 0
 
    Set cmbRC = CommandBars.Add(strBarName, msoBarPopup, False)
    ' then go about adding your controls
I don't know if the above method resolves your issue but, maybe it's worth a try. It's one delete as opposed to hundreds or more. Maybe the system wouldn't count the deletes of the individual controls, I don't know, just speculating.

Thanks, it's worth a try. I'll add it to the experiments in my test database, although the deletes are quick, and the adds are where it crashes. But it's something to test, and see if deleting the entire root command bar changes the situation in any way.
 

isladogs

MVP / VIP
Local time
Today, 10:05
Joined
Jan 14, 2017
Messages
18,186
I'll try and look at it later and let you know if I have any other suggestions beyond your test plan which is roughly how I would proceed in your situation
 

Users who are viewing this thread

Top Bottom