Happy Halloween to Gremlins and Zombies

About three years ago, I wrote this blog article on Combobox Gremlins. In perfect timing for the upcoming Halloween, I received a follow up comment from Colin Riddington trying to explain the reason for the issue I described in that article. He was so inspired by this phenomenon that he decided to post his own version of the same puzzle at Access World Forums.

So, in the spirit of Halloween we are about to celebrate tomorrow, I have decided to set the story straight, since I forgot to update the original blog with the solution to the puzzle. First, I must apologize to Philipp Stiefel, who helped me figure it out back then, because when I migrated my blog site to a new web host, his comment did not make it to the new site.

Philipp suggested the original table had a lookup field at one point but was then removed. However, the Row Source property for the field in the table still contains the Value List used by the original designer (it was an inherited database, I did not make it). If you download my original demo and execute the following code in the Immediate Window, you will see the Value List.

?CurrentDb.TableDefs("Searchtable").Fields("Appropiation").Properties("RowSource").Value

At the time, Philipp's explanation made sense to me and I just left it at that. It wasn't until Colin's follow up comment did I finally decide to find out the main reason why this is happening. At this point, I was lucky enough to be able to ask one of the Microsoft Access engineers (Shane Groff) for an explanation on this subject and this is what he said:

-QUOTE-

Phil is right about the original gremlin, it is easy to reproduce:

1) Create a table with a text field
2) Set the Display Control property to 'Combo Box', then set Row Source Type to Value List, and Row Source to a;b;c or whatever list items you want
3) Now change Display Control back to Textbox
4) Save and close the table
5) Make a new form, add a ComboBox, bind the form to your table, and the combo to your gremlin field
6) Switch to form view

Result: Your combobox has values, even though the don't appear anywhere in the table or form properties.

Even if you now switch the Display Control in the table back to Combo Box and the Row Source Type to Value List, you won't see the values (although if you save at that point, I think we clear the property).

This value is stored as a property on the table column, and we don't clear it when you change the Display Control property, so it still get's inherited by the form Combo Box (unless you  Inherit Value List to No).

-END QUOTE-

One other thing that Philipp told me (and reemphasized by Shane above) was setting the Inherit Value List property of the Combobox to No will remove the Gremlins (or Zombies) from the Form's Combobox; however, the Row Source property of the field in the table is still there (so, setting the property back to Yes will bring them back). To permanently remove the Row Source property from my Gremlins demo, the following code needs to be executed:

CurrentDb.TableDefs("Searchtable").Fields("Appropiation").Properties.Delete ("RowSource")

So, the above revelation seems to be one more reason to avoid using lookup fields at the table level. In fact, the owner of The Access Web is deciding to update the page on The Evils of Lookup Fields in Tables to include this information.

I hope this article helps explain what was going on with my Gremlins demo and Colin's Zombies puzzzle. I want to thank all who helped figure out this puzzle.

Happy Halloween to everyone!