Not sure if I understand your explanation, but you can do this easier than I think you are explaining.
Simply create a table. Take a field and set the lookup property to combobox. In the tables lookup properties set it to value list and put in your values. Make a form based off the table. Remove the values from the combos rowsource. Close the form. Go back to the table and set the lookup to textbox.
Inherit value list, Gets or sets whether a combo box's value list is inherited from its field. Read/write Boolean. The values are part of the field.
Not sure if I understand your explanation, but you can do this easier than I think you are explaining.
Simply create a table. Take a field and set the lookup property to combobox. In the tables lookup properties set it to value list and put in your values. Make a form based off the table. Remove the values from the combos rowsource. Close the form. Go back to the table and set the lookup to textbox.
Inherit value list, Gets or sets whether a combo box's value list is inherited from its field. Read/write Boolean. The values are part of the field.
@MajP
I was only DESCRIBING how to replicate the original puzzle, NOT EXPLAINING how it works.
Yes of course you can populate the table and do it that way.
However, the table was empty in the original puzzle and that's what I was replicating.
The Inherit Value list property is only part of the explanation. I think its now time to explain the effect in full
===============================================
The effect depends on two factors
1. The row source property for the field used as the combo source field
2. The combo box property Inherit Value List which by default is set to Yes
NOTE:
a) If you set Inherit Value List property to No, this behaviour does not occur
b) The property was added in A2007, so it doesn’t occur in A2003 or earlier
c) Each of the steps above could of course have been done in the same form. Using different forms just makes it seem more mysterious
Below is my description of how to replicate this from post #18 with added explanations in RED
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
Set Allow Value List Edits = No
Set the default value to "*"
Save, close and reopen the form.
The value list has now been added to the field’s row source property
This can be viewed by using the Database Documenter:
Alternatively you can view the property by typing the following into the VBE Immediate window substituting with the actual names
Thanks to TheDBGuy for reminding me of that approach
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 The values are still there because they are inherited from the field row source property PROVIDED the combo’s Inherit Value List property = Yes (the default value)
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? Same reason as in item 3 – they are indeed inherited
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!!!! The row source inherited from the table takes priority over the new list you typed
6. Create a third form Form3
Repeat all steps as for Form2 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 Clicking the builder exposed the original row source for the field
It also allows you to edit that row source
7. Reopen Form1 and Form2. Both combo lists also now show the new list of values That’s because the row source has been updated
8. The above instructions also work for listboxes For the same reasons
Hopefully the full explanation makes total sense
As I said in my last reply, with hindsight it was blindingly obvious but even so, the puzzle foxed several very experienced developers for some time ... definitely including myself
This is another great reason why lookup fields are evil, especially since the default is inherit value list. Another un-needed feature. Imagine you make a lookup field with values "Red","Blue","Green" and you create your form. Sometime later you come back to that form You see 'Red';'Blue';'Green'. and you add some more colors. You save it and reopen the form and you only get Red, Blue, Green since it "re-inherits". You do this about ten times with no idea why it will not save. So you say screw it and delete that combo and create a new combo. Use use the wizard and add 15 colors. Then you open it and get Red, Blue, Green only.
This is another great reason why lookup fields are evil, especially since the default is inherit value list. Another un-needed feature. Imagine you make a lookup field with values "Red","Blue","Green" and you create your form. Sometime later you come back to that form You see 'Red';'Blue';'Green'. and you add some more colors. You save it and reopen the form and you only get Red, Blue, Green since it "re-inherits". You do this about ten times with no idea why it will not save. So you say screw it and delete that combo and create a new combo. Use use the wizard and add 15 colors. Then you open it and get Red, Blue, Green only.
I couldn't agree more about the evils of lookup fields but perhaps the subject of this thread isn't the best example to use.
The reality is that it took several days for anyone to work out why this was happening.
I've NEVER actively used the Inherit Value List property NOR thought about what it meant till now.
I've also never personally experienced an issue with phantom lists.
If many other people had issues with these phantom lists, I expect there would already be lots of posts on the subject and explanations of how to solve it. However that doesn't seem to be the case.
On a different subject, please can you report your own posts to moderated areas.
A reminder that is the only way we get notified of new posts.
Its only because I've been adding my own comments today that I've noticed your contributions and approved them
I am glad to see this mystery has been solved. For the record, I did know the reason or cause of my Gremlins back when I wrote the original article with the help of Phil Stiefel, but I guess I didn't care to know the "why" until Colin brought it up again, so I decided to ask Microsoft for an explanation.
So, just to close the loop, I have just published a new article explaining everything including the official word from Microsoft about this topic.
