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 |