ComboBox.ShowOnlyRowSourceValues (Access) Problem. What it do, exactly?

Question

I understand that this property allows you to choose whether manually written options that are outside the origin of the list are shown in a combo box dropdown or a list box. Here's an example: Field Products with values "car," "boat," "motorcycle." The combo box displays these three options. If I set the property Limit to List to NO, I can manually add other options in both the field and the controls. I do not edit the list; I only write other values manually. According to Microsoft's documentation on the "Show Only Values of Row Source" property (link below), choosing YES would only let me see "car," "boat," "motorcycle" in the dropdown. If I choose NO, I should see the manually entered words in the dropdown. But that never happens. I always see only the list origin. No matter what I do, whether I choose a list of values or a table field as the source, I can't get any differences between YES/NO. So I don't know what this property actually does. Does it really do anything? I need a very specific context, maybe adjusting another property as well, I don't know.

Answer 1

The ShowOnlyRowSourceValues property only applies to Multi-Valued Fields. 

In the case of a combo box bound to a foreign key column in a referencing table, the NotInList property should normally be set to True (Yes).  This allows the relationship with the referenced table to be enforced, which would not be the case if the control, and hence the column, accepted values not returned by the RowSource query. 

Should you need to enter a value not currently in the list, then the control's NotInList event procedure can be used to insert a new row into the referenced table, and update the list.  The code can either transparently insert a new row into the referenced table, where it is not necessary to insert values into other columns in the table. Where it is necessary to insert values into other columns in the table, on the other hand, the code can open a form bound to the referenced table in dialogue mode, and pass the new value typed into the combo box to the form as its OpenArgs property.  It can then be assigned to the DefaultValue property of the control in question in the form, ready for the user to insert values into other columns, before closing the form and updating the combo box's list. 

Note that, where the combo  box's visible column shows values which can legitimately be duplicated in the referenced table, e.g. personal names, then the NotInList event procedure can not normally be used.  Instead a separate command button in the form can be used to open a form a form bound to the referenced table in dialogue mode.  Again the combo box's list can be requeried when the dialogue form closes to update the list. 

For example of the above take a look at NotInList.zip in my public databases folder at: 

https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAAB3aLXYo7DdARg01KnQIyoLg?e=fASl3m 

Place names can also be problematical, because these can legitimately be duplicated.  However, if place names are inserted by means of a set of correlated combo boxes, the NotInList event procedure can be used, subject to the value at each level of the hierarchy being distinct per value at the next higher level.  For an example take a look at DatabaseBasics.zip in my same OneDrive folder.  In the section on 'entering data via a form/subforms' the first form includes three correlated combo boxes for inserting Country, Region, and City values in that order.  For the purposes of the demo it is assumed that region names are distinct per country, and city values are distinct per region.  In reality this is not the case as city names can be duplicated per region in some countries.

Note BTW that a value list should only be used as the RowSourceType property in the case of a set of values immutably fixed in the external world, e.g. days of the week or months of the year. Otherwise a query which draws its rows from a referenced table should be used. Codd's Law #1, The Information Rule, which requires that all data are stored as values at column positions in rows in tables is then not violated.