Skip to content

Multi-Valued Fields in Access 2007

How to use Access 2007’s new “multi-valued fields” feature.

Access Archon #159

Access 2007 has a new feature for fields:  one field can now hold multiple values.  This feature is called “multi-valued fields,” and is implemented by creating a field with the Lookup Wizard.  On the first screen of the Wizard, select the I want the lookup column to look up the values in a table or query option:


Figure A.  The first screen of the Lookup Wizard

On the next screen, select the table to use for looking up the values (this would generally be a lookup table, such as tblSalesRegions in the sample database).  On the next screen, select the lookup field.  Select a field for sorting on the next screen, and adjust column width on the next screen:


Figure B.  Adjusting the width of the lookup column for a multi-valued field

On the final screen, give the column a label, and check the Allow Multiple Values checkbox.


Figure C.  The final screen of the Lookup Wizard for creating a multi-valued field

Now, when you drop down the list from the SalesRegion field, you will see the lookup choices, and you can check as many as are needed for each record:


Figure D.  Selecting multiple values for a field.

The values display as a comma-delimited list:


Figure E:  Multiple field values in datasheet view.

In some cases, multi-valued fields can replace complex code for add-to combo boxes; I will deal with this topic in an upcoming AW.


References

The code in the sample database does not need any special references.


Supporting Files

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











Document Name

Document Type

Place in

Multi-Valued Fields.accdb

Access 2007 database

Wherever you want

 

About this author