Private Sub Criteria_Tracker_ID_Exit(Cancel As Integer)
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim childRS As Recordset
Dim var As Variant
Dim CriteriaName As String
Dim strRange1 As String
Dim strRange2 As String
CriteriaName = ""
Set db = CurrentDb
Set rst1 = db.OpenRecordset("SF Data and Technology Priorities")
strRange1 = "SELECT [Criteria Tracker ID] FROM [SF Data and Technology Priorities] WHERE [ID] = " & Me.[ID].Value
' "Me" identify current selected record on the form.
Set rs1 = db.OpenRecordset(strRange1)
' Multivalued field is actually a recordset. Here "Criteria Tracker ID" is multivalued field
Set childRS = rs1![Criteria Tracker ID].Value
If childRS.RecordCount = 0 Then
Me.[Criteria Name].Value = ""
Me.[Criteria Name].SetFocus
Exit Sub
End If
childRS.MoveFirst
' Loop through the records in the child recordset.
Do Until childRS.EOF
strRange2 = "SELECT [Sector] FROM [SF Criteria Tracker] WHERE [ID] = " & childRS.Fields(0).Value
Set rs2 = db.OpenRecordset(strRange2)
CriteriaName = CriteriaName & rs2![Sector].Value & "; "
rs2.Close
childRS.MoveNext
Loop
rs1.Close
Me.[Criteria Name].Value = CriteriaName
Me.[Criteria Name].SetFocus
' [Criteria Name] can be written as Criteria_Name, we can use _ to replace space in the field name
End Sub