1 out of 4 rated this helpful - Rate this topic

How to: Manipulate Multivalued Fields With DAO

Office 2007

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 example illustrates instantiating 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 example illustrates 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

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD