How to Add and Edit Multivalued Fields in Microsoft Access VBA with DAO

A multivalued field is a field that can store more than one value in a single record. For example, you may have a field called Skills that can store multiple skills for each employee in a table. Multivalued fields are represented as Recordset objects in DAO (Data Access Objects), which is a programming interface that allows you to access and manipulate data in Microsoft Access databases. The recordset for a multivalued field is a child of the recordset for the table that contains the multivalued field. To instantiate the child recordset, you can use the Value property of the multivalued field as follows:

VB Set childRs = rs.<multi-valued field>.Value

The child recordset has the same functionality as any DAO Recordset object. You can use methods and properties such as MoveFirst, MoveNext, EOF, AddNew, Update, Delete, and Value to manipulate the data in the multivalued field.

Procedures

To set a multivalued field when adding a record to a table-type or dynaset-type Recordset object, you can follow these steps:

1. Use the AddNew method to create a record you can edit.
2. Assign values to each of the record’s fields, except for the multivalued field.
3. Set a Recordset object to the Value property of the multivalued field.
4. Use the AddNew and Update methods to add values to the child recordset of the multivalued field.
5. Use the Update method to save the new record to the parent recordset.

Explanation

To illustrate the procedures with an example, let us assume that we have a table called Employees, with the following fields:

• EmployeeID: a numeric field that stores the unique identifier of each employee.
• Name: a text field that stores the name of each employee.
• Skills: a multivalued field that stores the skills of each employee.

We want to add a new record to the Employees table, with the following values:

• EmployeeID: 1001
• Name: Alice
• Skills: Excel, VBA, SQL

We can use the following VBA code to accomplish this task:

`VB Dim db As DAO.Database ’ Database object Dim rs As DAO.Recordset ’ Parent recordset object Dim childRs As DAO.Recordset ’ Child recordset object

Set db = CurrentDb ’ Set the database object to the current database Set rs = db.OpenRecordset(“Employees”) ’ Open a recordset for the Employees table

rs.AddNew ’ Create a new record rs!EmployeeID = 1001 ’ Assign the value to the EmployeeID field rs!Name = “Alice” ’ Assign the value to the Name field Set childRs = rs!Skills.Value ’ Set the child recordset object to the Value property of the Skills field

childRs.AddNew ’ Add a new value to the child recordset childRs!Value = “Excel” ’ Assign the value to the Value field childRs.Update ’ Save the value to the child recordset

childRs.AddNew ’ Add another new value to the child recordset childRs!Value = “VBA” ’ Assign the value to the Value field childRs.Update ’ Save the value to the child recordset

childRs.AddNew ’ Add another new value to the child recordset childRs!Value = “SQL” ’ Assign the value to the Value field childRs.Update ’ Save the value to the child recordset

rs.Update ’ Save the new record to the parent recordset `

Scenario

To demonstrate the result of the code, let us use an Excel table to show the data in the Employees table before and after running the code. The table has four columns: EmployeeID, Name, Skills, and Values. The Skills column shows the multivalued field, and the Values column shows the values stored in the child recordset of the multivalued field.

As you can see, the code has successfully added a new record to the Employees table, with the EmployeeID of 1001, the Name of Alice, and the Skills of Excel, VBA, and SQL. Each skill is stored as a separate value in the child recordset of the Skills field.