List Boxes, Access Bugs, and More

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Sep-2002 >

List Boxes, Access Bugs, and More


Chris Weber


In this jumbo-sized edition of Access Answers, Chris Weber has something for everyone. You’ll find out about a subtle Access bug, some more obvious bugs, and how to avoid using edit masks (and why you’d want to).


I have a list box on a form that displays all of my reports for my users so that they don’t need to have direct access to the database container. My list box uses a value list generated from the documents list in the reports container. However, as the list of reports has grown, my list box has stopped working. Access tells me that the string for the value list’s row source is too long. Is there any way around this limitation?


I generally avoid using the forms and reports containers to generate these lists for this very reason. The listbox and combobox controls in Access have a row source size limitation of 2,048 characters. Instead of looping through the reports documents collection, you can query the MSysObjects system table in Access to get your list using the value –32764 for the Type. To build a list of forms use the value –32768:


'for reports SELECT Name FROM MSysObjects WHERE Type = -32764

'for forms SELECT Name FROM MSysObjects WHERE Type = -32768


The Type values for each kind of object can be gleaned from MSysObjects by comparing the object names in your database to the numbers. In the sample database for this month’s column (available in the Download file at, cboSelectFromMSysObjects on the form frmColumnSizeToFit uses Type –32768 along with a Like clause to fill the list with the database’s subforms. Incidentally, if you’re filling the list for an add-in, you can use the IN clause along with the name of the calling database to make JET look in your current database:


"SELECT Name FROM MSysObjects IN '" & _  CurrentDb.Name & "' WHERE Type = -32768"


Unfortunately, you can’t get at other properties of the document object through the MSysObjects table. For

example, if you want to display only nonhidden reports, or reports that have descriptions, you’ll still have to resort to using the Reports collection.


We’ve recently had a couple of clients whose forms opened more slowly day after day even though tests on our network showed no signs of degradation using the same back end. A dataset that took five seconds to load across our network began to take upwards of four minutes at the client site. We spent hours tracing down possible network problems and repeating tests. Reinstalling the front end stopped the problem temporarily, but after a few days it began again. What are we doing wrong?


You aren’t doing anything wrong. I suspect you’ve taken advantage of the Name AutoCorrect option under the Tools | Options | General tab or, more accurately, it has taken advantage of you. Name AutoCorrect allows Access 2000 to auto-magically cascade field name changes to your queries, forms, and reports. This is a great feature if you’re evolving a data structure. However, checking these options can cause extremely poor performance in your Access 2000 database over time. We ran into the same problem until we noticed that the prior release to the client didn’t have AutoCorrect features enabled. After disabling the options, the performance problem was immediately resolved. Microsoft only acknowledges the problem if security is enabled and if the Access 2000 Service Pack 1 is NOT installed. This is not true. In our case, the client had the service pack installed and wasn’t using Access security. Just turning AutoCorrect off solved the problem.


I’ve noticed that I can get a better compact for my database by copying all of my objects to new database. However, I can’t seem to find a way to copy my toolbars. Any ideas?

You know, I used to do the same thing for simpler databases. Unfortunately, this doesn’t handle your toolbars as you noted, as well as other niceties such as startup properties. In Access 97, it’s still easy to copy toolbars by linking to the MSysToolbars table in the previous database and copying the records to the new database (The MSysToolbars is visible after selecting Tools


Read More Here:

List Boxes, Access Bugs, and More