Access中VBA多维数组结果如何导入表单控件（含子窗体数据集）

方法一：直接绑定原记录集（推荐） #

你当前代码已获取到记录集rs4，无需转成数组，直接将记录集绑定给子窗体是最高效的方式，修改代码如下：

' 获取参与人员数据
strSQL4 = "SELECT tblPerson.Person_FName, tblPerson.Person_LName, tblPerson.Email, tblORD_Division.ORD_Div, tblEngParRole.Role FROM tblORD_Division INNER JOIN ((tblEngagements INNER JOIN tblEngParRole ON tblEngagements.ENG_ID = tblEngParRole.Eng_ID) INNER JOIN tblPerson ON tblEngParRole.Person_ID = tblPerson.Person_ID) ON tblORD_Division.ORD_DIv_ID = tblPerson.ORD_Div_ID WHERE tblEngagements.Eng_ID = " & Me.Eng_ID
Set rs4 = CurrentDb.OpenRecordset(strSQL4)

' 绑定记录集到子窗体
With Me.子窗体控件名称.Form
    .Recordset = rs4
    ' 可选：匹配列标题（替换为实际控件名）
    .Controls("txtFirstName").Caption = "FirstName"
    .Controls("txtLastName").Caption = "LastName"
    .Controls("txtEmail").Caption = "Email"
    .Controls("txtDiv").Caption = "ORD_Div"
    .Controls("txtRole").Caption = "Role"
End With

rs4.Close
Set rs4 = Nothing

注意替换代码中的子窗体控件名称和对应列控件名，确保子窗体控件绑定的字段名与SQL查询字段一致。

方法二：将多维数组转为临时记录集再绑定 #

如果必须使用已生成的varParticipants数组，可先将数组转换为ADO临时记录集，再绑定给子窗体：

' 保留原数组生成代码
strSQL4 = "SELECT tblPerson.Person_FName, tblPerson.Person_LName, tblPerson.Email, tblORD_Division.ORD_Div, tblEngParRole.Role FROM tblORD_Division INNER JOIN ((tblEngagements INNER JOIN tblEngParRole ON tblEngagements.ENG_ID = tblEngParRole.Eng_ID) INNER JOIN tblPerson ON tblEngParRole.Person_ID = tblPerson.Person_ID) ON tblORD_Division.ORD_DIv_ID = tblPerson.ORD_Div_ID WHERE tblEngagements.Eng_ID = " & Me.Eng_ID
Set rs4 = CurrentDb.OpenRecordset(strSQL4)
rs4.MoveLast
rs4.MoveFirst
varParticipants = rs4.GetRows(rs4.RecordCount)
rs4.Close
Set rs4 = Nothing

' 转换数组为临时ADO记录集
Dim tempRS As Object
Set tempRS = CreateObject("ADODB.Recordset")

' 添加对应字段（顺序与数组列一致）
With tempRS
    .Fields.Append "Person_FName", 200, 50 ' 200对应adVarChar类型，50为字段长度
    .Fields.Append "Person_LName", 200, 50
    .Fields.Append "Email", 200, 100
    .Fields.Append "ORD_Div", 200, 50
    .Fields.Append "Role", 200, 50
    .Open
    
    ' 遍历数组添加记录
    Dim intRowNum As Integer
    For intRowNum = 0 To UBound(varParticipants, 2)
        .AddNew
        .Fields(0).Value = varParticipants(0, intRowNum)
        .Fields(1).Value = varParticipants(1, intRowNum)
        .Fields(2).Value = varParticipants(2, intRowNum)
        .Fields(3).Value = varParticipants(3, intRowNum)
        .Fields(4).Value = varParticipants(4, intRowNum)
        .Update
    Next
End With

' 绑定临时记录集到子窗体
With Me.子窗体控件名称.Form
    .Recordset = tempRS
End With

tempRS.Close
Set tempRS = Nothing

方法三：手动遍历数组填充未绑定子窗体 #

如果子窗体为未绑定状态，可手动遍历数组逐行添加记录：

' 保留原数组生成代码
strSQL4 = "SELECT tblPerson.Person_FName, tblPerson.Person_LName, tblPerson.Email, tblORD_Division.ORD_Div, tblEngParRole.Role FROM tblORD_Division INNER JOIN ((tblEngagements INNER JOIN tblEngParRole ON tblEngagements.ENG_ID = tblEngParRole.Eng_ID) INNER JOIN tblPerson ON tblEngParRole.Person_ID = tblPerson.Person_ID) ON tblORD_Division.ORD_DIv_ID = tblPerson.ORD_Div_ID WHERE tblEngagements.Eng_ID = " & Me.Eng_ID
Set rs4 = CurrentDb.OpenRecordset(strSQL4)
rs4.MoveLast
rs4.MoveFirst
varParticipants = rs4.GetRows(rs4.RecordCount)
rs4.Close
Set rs4 = Nothing

' 清空子窗体现有记录
Me.子窗体控件名称.Form.RecordsetClone.MoveFirst
Do While Not Me.子窗体控件名称.Form.RecordsetClone.EOF
    Me.子窗体控件名称.Form.RecordsetClone.Delete
    Me.子窗体控件名称.Form.RecordsetClone.MoveNext
Loop

' 遍历数组填充子窗体
Dim intRowNum As Integer
For intRowNum = 0 To UBound(varParticipants, 2)
    With Me.子窗体控件名称.Form.RecordsetClone
        .AddNew
        !Person_FName = varParticipants(0, intRowNum)
        !Person_LName = varParticipants(1, intRowNum)
        !Email = varParticipants(2, intRowNum)
        !ORD_Div = varParticipants(3, intRowNum)
        !Role = varParticipants(4, intRowNum)
        .Update
        .MoveLast
    End With
    Me.子窗体控件名称.Requery ' 刷新显示
Next

内容的提问来源于stack exchange，提问作者Bruce Sorge