Navigating Recordsets with Multi-Value Lookup Fields

You've seen the Recordset and Field objects for accessing data in a table or query. There are, however, new objects in DAO that are used to manipulate and navigate multi-value lookup fields. These objects are appropriately named Recordset2 and Field2. In fact, if you declare a Recordset or Field object in an ACCDB file, you are actually using a Recordset2 or Field2 object. This happens regardless of whether you open a recordset that contains a multi-value lookup field.

Because a multi-value lookup field can store many values, the value of the field is actually a recordset. In other words, you can navigate through the values in a multi-value lookup field in the same manner as other recordsets.

For example, say you are an administrator at a small college and would like to track students and the classes that those students take. You have already created the table of classes that contains information about each class. You start by creating the Students table and adding a multi-value lookup field named Classes to store all of the classes taken by a particular student. The following code shows how to print the list of students and the classes they take:

Sub PrintStudentsAndClasses()
    Dim dbs As DAO.Database
    Dim rsStudents As DAO.Recordset2 'Recordset for students
    Dim rsClasses As DAO.Recordset2 'Recordset for classes
    Dim fld As DAO.Field2

    'open the database
    Set dbs = CurrentDb()

    'get the table of students
    Set rsStudents = dbs.OpenRecordset("tblStudents")

    'loop through the students
    Do While Not rsStudents.EOF

	'get the classes field
	Set fld = rsStudents("Classes")

	'get the classes Recordset
	'make sure the field is a multi-valued field before
	'getting a Recordset object
	If fld.IsComplex Then
	    Set rsClasses = fld.Value
	End IF

	'access all records in the recordset
	If Not (rsClasses.BOF And rsClasses.EOF) Then
	    rsClasses.MoveLast
	    rsClasses.MoveFirst
	End If

	'print the student and number of classes
	Debug.Print rsStudents("FirstName") & " " & rsStudents("LastName"), _
	    "Number of classes: " & rsClasses.RecordCount
	'print the classes for this student
	Do While Not rsClasses.EOF
	    Debug.Print , rsClasses("Value")
	    rsClasses.MoveNext
	Loop

	'close the Classes recordset
	rsClasses.Close

	'get the next student
	rsStudents.MoveNext
    Loop

    'cleanup
    rsStudents.Close

    Set fld = Nothing
    Set rsStudents = Nothing
    Set dbs = Nothing
End Sub

Because the related class data is stored as a recordset, you can use the following line to retrieve the classes for a student:

This creates a Recordset2 object that contains one field named Value. This field contains the value of the bound column as displayed in the multi-valued combo box or list box in Access.