Multi Value Fields

Most database programs, including earlier versions of Access, allow you to store only a single value in each field. In Office Access 2007, however, you can now create a lookup field that enables you to store more than one value. In effect, it creates a many-to-many relationship within the field and hides the details of the implementation using system tables. Users can easily see and work with the data because the items are all displayed in the field, separated by commas.

The easiest and most effective way to create these fields is to use the Lookup Wizard to create a multi-value lookup column. In line with so many other design changes, the Lookup Wizard can be initiated from the table Datasheet view as well as from Design view. In Datasheet view, either select Lookup Column from the Fields & Columns group or drag a field from another table in the Field pane into the current table's field list. (You can edit the field properties in Design view.)

The wizard automatically creates the appropriate field property settings, table relationships, and indexes. You can still create a lookup column manually or modify one that is created by the wizard. Working with multi-value fields is much like working with combo boxes but with additional settings and properties.

When you think about all the work involved with properly structuring tables to manage many-to-many relationships, what goes on under the hood is pretty impressive. And it is nice to know that the data is exposed for your use. In DAO and ADO, the data is a collection and exposed through the .Value property. With an MVF field, the .Value property contains a recordset that can be enumerated. You can also test if a field is a MVF by seeing if the IsComplex property of a Field object is true. If the value is true, then the .Value property will contain a DAO or ADO recordset. The data is also exposed through the Query Designer using .Value, similar to regular combo box settings, or by working with the collection and using the ID. Although the data is exposed, the join field is a system table that is not exposed to developers, so you may still prefer to create your own look-up fields and manage many-to-many relationships using a separate table (often referred to as a linking or xfer table) for the join field.

In support of multi-value fields, DAO uses the ComplexType object, Field2 object, and Recordset2 object. (See Chapter 6 on DAO to learn more about using code to manipulate these new field types.) The Field2 object contains several new properties and methods that support multi-value field types:

Property AppendOnly Use to create field history for memo fields. For all other

datatypes the value is False.

ComplexType

For multi-value fields.

IsComplex

For multi-value fields. Returns true if the field is a multivalued lookup field.

Method

LoadFromFile

For attachments.

SaveToFile

For attachments.

A Recordset2 object contains the same properties and methods as the Recordset object. It also contains a new property, ParentRecordset, that supports multi-value field types.

The Lookup Wizard can do a lot for you. Going back to the example of selecting a team for a project, let's say that in the past each project had one lead. With the growing complexity of development and shared workloads, some projects might now require co-leads. That would be a prime candidate for utilizing a multi-value field. Given that the database has an Employee table and a Project table, you merely drag the employee field from the employee table onto the project table and work with the wizard do the rest. It's critical that on the last page of the wizard, the checkbox "Allow multiple values" is checked. Keep in mind that that if you click Finish before the last page, you won't have the opportunity to check this box and request a multi-value field. When you add the new field type to a form, the list will have a check box beside each name and users can select as many people as they need. When you look at the employee record in the project table, it will display a delimited list of selected names. That's it; no struggling with linking tables and no complicated code for multi-select options.

Access uses multi-value fields for storing attachments within the database. And, you can see that multi-value fields are also excellent for creating your own solution for working with reference material. For example, a construction company might want to store photos of various stages of each project, and it likely has bios for key project personnel. When it's time to bid on a new project, the company could quickly create a proposal that illustrates experience and expertise by including personnel bios and project histories with photos. What a great segue to talking about the attachment field type.

Was this article helpful?

+2 -1

Responses

  • primula
    How to add value through check box in Access2007?
    8 years ago
  • silke
    How to add an item to a multivalued table?
    8 years ago

Post a comment