access关于多值字段的处理（MVF）

1.多值字段有一个字段名 及 字段名.value
2.在查询中可使用字段名 与 字段名.value
3.多值字段本身就是一个记录集，可以设置为

Set childRs = rs("多值字段名").Value
Set rs  = db.OpenRecordSet("Tasks") 
Set childRs = rs.AssignedTo.Value 

4.使用dao循环多值字段的值

Sub BrowseMultiValueField()
   Dim db As dao.Database
   Dim rs As Recordset
   Dim childRS As Recordset
     
   Set db = CurrentDb()
     
   ' Open a Recordset for the Tasks table.
   Set rs = db.OpenRecordset("员工")
   rs.MoveFirst
     
   Do Until rs.EOF
      ' Print the name of the task to the Immediate window.
      Debug.Print rs!姓名.Value
         
      ' Open a Recordset for the multivalued field.
      Set childRS = rs!职务.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

表格中职务字段为多值字段

或者效果如立即窗口所示

5.其它相关的代码

SELECT * FROM   rights  WHERE  id IN (
SELECT id    FROM   rights   GROUP  BY id  HAVING Count(fldold) > 0) 
AND oldf2.VALUE IN (SELECT id  FROM   restrictions    WHERE  name IN ( 'xxx', 'yyy' )) 

6.多值字段的复制

Sub Main()
Dim sql As String
Dim db As Database
Dim rst As Recordset

sql = "select * from  rights  where id in (SELECT rights.id FROM rights group by rights.id having count(rights.fldold)>0)"
Set db = CurrentDb
Set rst = db.OpenRecordset(sql)

rst.MoveFirst
Do Until rst.EOF
    rst.Edit
    copyMVF rst!fldold, rst!fldnew
    rst.Update
    rst.MoveNext
Loop


End Sub
Function copyMVF(ByRef src As Field2, dest As Field2)
    Dim rsSRC As Recordset2
    Dim rsDest As Recordset2
    
    Set rsSRC = src.Value
    Set rsDest = dest.Value
'Delete values from dest field
    If rsDest.RecordCount > 0 Then
    rsDest.MoveFirst
    Do Until rsDest.EOF
        rsDest.Delete
        rsDest.MoveNext
    Loop
    End If
'copy from src to dest
    rsSRC.MoveFirst
    Do Until rsSRC.EOF
        With rsDest
            .AddNew
            .Fields(0) = rsSRC.Fields(0)
            .Update
        End With
        rsSRC.MoveNext
    Loop
End Function