Editing Value Lists in Access 2007

How to edit Value Lists in Access 2007.

Access Archon #169

In Access versions through 2003, if you selected Value List as the row source type for a listbox or combo box, you had to type in the list values as a semicolon-separated list, which is a tedious chore for a list longer than three or four items.  Access 2007 adds a new feature:  a Build button that opens a dialog for entering and editing value list items.

Figure A.  The Build button on a listbox’s Row Source property

When you click the Build button, you get the dialog shown in Figure B, where you can enter the list items, each on its own row:

Figure B.  A dialog for entering list items

When you are looking at the form in Form view, when you select an item in the list, a pale grey icon appears under the listbox (possibly next to another control, which can cause confusion).  The icon takes on a little color when you hover your mouse over it, as shown in Figure C.  You can click on this icon to reopen the Edit List Items dialog to add new items on-the-fly – a very nice feature, as often you realize that you need a new item while entering data on the form.

Figure C.  An icon for opening the Edit List Items dialog in Form view

There is a similar feature for add-to combo boxes in Access 2007; it is no longer necessary to write code (as in my Access Archon #161) to add new items to a simple table that is the row source of a combo box.  Instead, you have the option of opening a form to edit the combo box’s row source.  To use this new feature, set the combo box’s Limit To List and Allow Value List Edits properties to Yes, and select the form to use for editing the row source table, as shown in Figure D. 

Figure D.  New properties for editing value lists for add-to combo boxes

On the frmProducts form, there is a Category combo box where the product category can be selected.  If you have set the combo box’s properties as described above, when you type a new Category name into the combo box, you will get a dialog asking if you want to edit the list:

Figure E.  A dialog on entering a “not in list” value in a combo box

If you click Yes, the selected form opens, where you can enter the new Category record.

Figure F.  A form for adding new values to a row source table

On closing the form, the new value is saved to the table, and is then available to select on other records of the main frmProducts form.

Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is accarch169.zip, which is the last entry in the table of Access Archon columns for Access Watch.