Thursday, February 25, 2016

Combobox Gremlins

This time, I thought it would be nice to have a little fun for a change. So, in this blog, I will need your participation. I will post a question, and I hope many of you will provide me with "an" answer. It doesn't have to be "the" answer, just tell me what you "think" is the answer. Later on, we can all "vote" on the best one. Are you ready? Let's get started...

To participate in this exercise, you will have to download this file. When you open it, you will see that it contains one table and one form. The form only has one control on it, a combobox. If you click the dropdown for the combobox, you will be presented with several choices. So far so good.

Now, here's the "fun" part... If you go to the form's design view and select the combobox, you can take a look at the Row Source property and see what's in it. Did you see it? So, here's my question. Where are the choices for the combobox dropdown coming from?


This sample database was actually part of something that I inherited recently. I was asked to modify the choices for the combobox and realized something was weird about it. I know where the choices originally came from, but I couldn't figure out how they are showing up in the dropdown. If I change the Row Source from "*" to something like "test," that change is not reflected in form view - I still see the old choices in the dropdown. If I remove the Row Source and change the Row Source Type from Value List to Table/Query, then it behaves normally with the dropdown showing empty. However, as soon as I change the Row Source Type back to Value List, the old dropdown list returns.

What do I think is happening?

Here is my speculation on the issue... You know how Microsoft said that a form can only have so much controls added to it over its lifetime? I was wondering how does it keep track of that number? So, my guess as to what's happening with this form is that the Row Source for the combobox is being tracked somehow and stored somewhere that when you change the Row Source Type back to Value List, the original Row Source returns. However, that doesn't explain why the old Row Source still displays even when I manually change the Row Source property.

What do you think?

I would like to hear your explanation of this issue, because I think it would be nice to learn the truth about something like this. You can either leave a comment below or send me an email.
I have always admitted that I don't know everything about Access, and this is one definite proof of that. But with your help, I hope to learn something new. Thank you for participating!

Original post date: May 28, 2015

1 comment:

  1. Only 3 years after you posted, I’ve just discovered your Gremlins demo !

    AFAIK the explanation is nothing to do with the number of controls over the form lifetime
    The values are just hidden in the row source (though I’m not sure why).
    To view them, select any value in the combo then change to layout view.
    You will see something like: “*";"ACICG";"AFMSDA";"APA";"APAF";"APN";"AWDF";"BRAC";"CBPDHS";"DERF"; ... etc
    Change back to design view & the values are gone (apart from *)

    Now revert to layout view & delete the "*"; from the start of the list
    Save. Revert to design view & it behaves normally

    I think the * at the beginning triggered that weird behaviour – notice it had no quotes

    I have emailed you with a fixed version of this file

    The file does also demonstrate another important point – the MSysObjects table contains a large number of objects starting with ~TMPCLP & ~sq
    These are related to items that were deleted but not removed from MSysObjects as the DB subsequently crashed rather than being closed properly.
    It is my belief that leaving such items in MSysObjects may eventually be a cause of corruption

    I have an article on my website about how you can remove these items from MSysObjects.
    Before cleaning up MSysObjects has 128 records. After cleanup, it has just 52 records with all ~TMPCLP & ~sq removed (except the form combobox item ~sq_fGWBS which is in use)

    I’ve included the code used to do the cleanup in the file sent by email – there are 3 routines though only two of them are needed in this case