Combobox Zombies Puzzle

isladogs · Oct 26, 2018

Here's a little puzzle for anyone interested.
It is based on a similar item posted at the DBGuy's website 3 years ago which he was unable to explain.
As I like riddles(!) I worked out how it was done and am posting a modified version here with his permission.

The attached database contains just 2 objects
a) A table with one record
b) A form with a combo box whose row source is an empty value list

However the combo shows the following items (back from the dead ... so zombies):

Its not too difficult to recover the row source info
BUT can you work out where the combo list info is stored?
Better still can you re-create this scenario in another database?

NOTE: There are no hidden objects or hidden code in this example. It is NOT another security challenge

If you succeed, please send me your solution by private message or email me using the link in my signature line

Please do NOT post your solution in this thread or it will spoil the puzzle for others

Gasman · Oct 26, 2018

I'd like to have a look at this, but would need a 2007 version?, sorry.
I cannot open that one.

MajP · Oct 27, 2018

Maybe I am missing something here, but when I open it I see in the rowsource
"*";"ABC";"DEF";"GHI";"XYZ"

That is a copy and paste from the row source. So what am I missing?
I am running Access 2016 on Win 10.

isladogs · Oct 27, 2018

@Gasman
As requested, attached is a 2007 version
It proved tricky to convert - in the end it seemed not to like the button as it used a theme!
Anyway I created & tested it in my A2007 VM.

@MajP
The row source may have got restored when you enabled content.
Try this to recreate the effect

In design view, clear the contents of the combo row source
Save the form.
Close the database
Reopen & it should show the combo list but the row source should (hopefully) be empty

See attached video to see the effect in action

MajP · Oct 27, 2018

Seems to depend on how I open it sometimes the row source shows sometimes it does not.

MajP · Oct 27, 2018

@MajP
The row source may have got restored when you enabled content.
Try this to recreate the effect

In design view, clear the contents of the combo row source
Save the form.
Close the database
Reopen & it should show the combo list but the row source should (hopefully) be empty

That is pretty interesting. I can "break it" to cause it to restore, but cannot figure out where it is coming from.

isladogs · Oct 27, 2018

Hi MajP
As i wrote in post #1

Its not too difficult to recover the row source info
BUT can you work out where the combo list info is stored?
Better still can you re-create this scenario in another database?

The first sentence was to cover the scenario where it sometimes reappears.
I know how to re-create this scenario in a new form / database
In fact I can create a phantom combo list without entering anything on a form.

Interestingly when I was creating a 2007 version for Gasman, I originally just converted it to A2003 MDB.
The zombie effect disappeared!

The original Gremlins DEMO from the DBGuy was part of an old MDB database which he converted to ACCDB.
He wondered if the effect was caused by hitting the limit for the number of objects over the lifetime of a form.
However, I proved that's not the case by re-creating the effect in a new form/database.
In the Gremlins example, you have to do something slightly different to get the rowsource to reappear. See attached

June7 · Oct 27, 2018

My observation so far. The form opens by default when db is opened. Click on combobox to open the dropdown. Switch to design view and will see RowSource list. Close the form. Now open directly to Design View from Nav Pane, nothing in RowSource. Now switch to Form view. Combobox has list. Switch back to Design View, RowSource is still empty.

I can only create new form and combobox with this effect in my db by importing/copying the original table.

Gasman · Oct 27, 2018

I *think* I know how it is done, but not where they values are stored yet.

theDBguy · Oct 29, 2018

June7 said:
I can only create new form and combobox with this effect in my db by importing/copying the original table.

Hello all,

I decided to join the forum today, so I can participate in this discussion. I wanted to post a screenshot, but I guess I'm not allowed to do that yet.

In any case, here's a partial quote from my original article on this subject:

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.

As it turns out, I was correct in my thinking back then. I didn't say it had to do with the number of controls on the form; but rather, it has something to do with "where" Access is keeping track of certain things, including the Row Source for a Combobox. In fact, this is why I quoted the last poster above because there is a clue in that statement. It "does" have something to do with the original table. So, to recreate this mysterious situation, one has to know how to recreate the original table setup from scratch (or at least understand how it was created in the first place).

Good luck to all!

Just my 2 cents...

DBG

theDBguy · Oct 29, 2018

Hi,

Sorry if this is inappropriate but just checking if I can do a "quick reply" since I tried to post a regular reply earlier but I can't see it now (yet).

Thanks!

isladogs · Oct 29, 2018

Hi DB Guy

Good to see you at AWF and I hope to see you sharing your wisdom with many more posts here.

You may not have realised that this is a moderated area. Unfortunately, the forum software doesn't alert mods to new posts. As such it gets picked up when one of the moderators notices a new item and I've been offline most of the day.

To speed things up, please report your own post. See sticky above.

New members can only upload files if they are zipped. After 10 posts that restriction is removed. As you have around 73600 posts at UA, hopefully that won't take too long!

EDIT Apologies for misquoting you (re form controls over form lifetime) in post #1. It wasted a fair bit of time on my part following up that point...should have read your blog more carefully!!

theDBguy · Oct 29, 2018

Hi Colin,

Thank you for the explanation. I suspected as much and have already started accumulating posts, so I can participate more fully around here. As soon as I have more than 10 posts, I will come back and post the screenshot I mentioned earlier.

Cheers,
DBG

Edit: Okay, as promised, attached is the screenshot of the original article.

pbaldy · Oct 29, 2018

Post 13 was moderated, I'm posting to trigger email notifications.

theDBguy · Oct 29, 2018

Gasman said:
I *think* I know how it is done, but not where they values are stored yet.

Hi Gasman,

I finally got the real answer from a Microsoft Access engineer as to where this information is stored. I will share it later so as not to spoil this thread for now.

If you want to email me to let me know "how" you think it's done, I'll be happy to give you a hint "where" it is stored (unless of course you figure it out as well).

Good luck!

DBG

MajP · Oct 30, 2018

It is in the inherit value list.

Gets or sets whether a combo box's value list is inherited from its field. Read/write Boolean.

expression A variable that represents a ComboBox object.

Remarks
The InheritValueList property is effective only when the combo box is bound to a Lookup field and the RowSourceType property is set to Value List.

So I went in and changed the look in TestField to "Rain; Snow; Sun" and that appears in the combobox.

NauticalGent · Oct 30, 2018

theDBguy said:
I decided to join the forum today....

Welcome to AWF DB, I have read countless posts from you in another Forum and borrowed your code almost as many times.

You will be a valuable addition here!

Again, welcome...

isladogs · Oct 30, 2018

OK as its Halloween tomorrow, I thought I'd post a detailed explanation of how to replicate the zombie effect in a new database.
It should also provide a strong clue to explain exactly what is going on, but without explicitly stating the answer.
Indirectly, it also explains why the effect cannot be replicated in A2003 or earlier

1. Create a new database with one table Table1.
I used fields ID (PK/autonumber) and Dummy (text). Do NOT populate the table.

2. Create a new form Form1 & bind it to the table.
Add a combo box cboDummy & set its control source to the text field from the table
Use a value list for the combo and add a few items starting with "*".
If using the wizard, set to save for later use or click cancel
Set Allow Value List Edits = No
Set the default value to "*"
Save, close and reopen the form.

3. Now delete all values from the row source EXCEPT "*"
Save, close and reopen the form in form view.
All values are still present even though these are no longer in the row source
(ZOMBIES BACK FROM THE DEAD)

4. Create a new form Form2 & bind it to the table.
Add a value list combo box & set its control source to the text field from the table
Do NOT populate any values in the row source
Otherwise follow all the steps from item 2 above
Save, close and reopen the form in form view.
The combo is populated with the same values as the first form but its row source is EMPTY.
Clearly the values are being inherited from the first combobox. Can you work out how/why?

5. In design view for Form2, add a different value list to that in Form1 but again starting with "*"
IMPORTANT: Do NOT use or even look at the builder (wait till step 6!)
Save, close and reopen the form in form view
The original list from Form1 should still be displayed though the value list row source is different!!!!

6. Create a third form Form3
Repeat all steps as in step 4 EXCEPT this time click the combobox row source builder - SURPRISE!
Edit the list using the BUILDER
Save, close and reopen the form in form view. The new combo list is displayed

7. Reopen Form1 and Form2. Both combo lists also now show the new list of values

8. The above instructions also work for listboxes

Attached is an updated version of this example database which includes the above explanation for your convenience

theDBguy · Oct 30, 2018

NauticalGent said:
Welcome to AWF DB, I have read countless posts from you in another Forum and borrowed your code almost as many times.

You will be a valuable addition here!

Again, welcome...

Hi,

Thanks for the warm welcome. I hope I could offer some value here. I’ll definitely try my best.

Regards,
DBG

isladogs · Oct 30, 2018

For info, I delayed posting several earlier posts on purpose until I had posted my detailed account of how to replicate this scenario.

Well done to both MajP & Gasman for guessing what determines whether the effect occurs.
With hindsight its blindingly obvious and staring us in the face (unless using A2003 or earlier)...but I think its fair to say all of us missed it at first.

For info, the DBGuy is going to post an updated comment in his blog explaining where the data is stored. Also obvious with the benefit of hindsight. I'll leave him to post the link

Combobox Zombies Puzzle

MVP / VIP

Attachments

Enthusiastic Amateur

You've got your good things, and you've got mine.

MVP / VIP

Attachments

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

MVP / VIP

Attachments

AWF VIP

Enthusiastic Amateur

I’m here to help

I’m here to help

MVP / VIP

I’m here to help

Attachments

Wino Moderator

I’m here to help

You've got your good things, and you've got mine.

Ignore List Poster Boy

MVP / VIP

Attachments

I’m here to help

MVP / VIP

Similar threads

Users who are viewing this thread