Manipulate multivalued fields with DAO
Multivalued fields are represented as Recordset objects in DAO. The recordset for a field is a child of the recordset for the table that contains the multivalued field. To instantiate the child recordset, use the Value property of the multivalued field as follows.
Set childRs = rs.<multi-valued field>.Value
The following code example shows how to instantiate the child recordset of the AssignedTo field of the Tasks table.
Set rs = db.OpenRecordSet("Tasks")
Set childRs = rs.AssignedTo.Value
The child recordset has the same functionality as any DAO Recordset object.
The following code example shows how to iterate through a parent recordset and its child recordset. The example prints the tasks in the Tasks table along with the people assigned to the tasks to the Immediate window.
Sub BrowseMultiValueField()
Dim db As Database
Dim rs As Recordset
Dim childRS As Recordset
Set db = CurrentDb()
' Open a Recordset for the Tasks table.
Set rs = db.OpenRecordset("Tasks")
rs.MoveFirst
Do Until rs.EOF
' Print the name of the task to the Immediate window.
Debug.Print rs!TaskName.Value
' Open a Recordset for the multivalued field.
Set childRS = rs!AssignedTo.Value
' Exit the loop if the multivalued field contains no records.
Do Until childRS.EOF
childRS.MoveFirst
' Loop through the records in the child recordset.
Do Until childRS.EOF
' Print the owner(s) of the task to the Immediate
' window.
Debug.Print Chr(0), childRS!Value.Value
childRS.MoveNext
Loop
Loop
rs.MoveNext
Loop
End Sub
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for