Access VBA复制含多值字段与子窗体的表单记录报错求助

报错核心原因 #

• 语法错误：rstmv = rstmv.Value 属于非法赋值，rstmv 是多值字段对应的Recordset2对象，不能直接赋值，你需要遍历源记录多值字段的每一项值，再写入新记录的多值字段中
• 逻辑顺序错误：你在新主记录还未执行Update保存的阶段，就尝试操作多值字段，且错误读取了正在新增的记录的多值字段，而非源记录的多值字段
• 存在未声明的无效变量：代码中rsp、imt均未定义，属于无效调用

修正后的复制按钮完整代码 #

Private Sub Duplicate_Click()
On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform, support multi-value field
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    Dim rstSource As DAO.Recordset
    Dim rstmvSource As DAO.Recordset2
    Dim rstmvNew As DAO.Recordset2
    
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'First get the multi-value field from source record
        Set rstSource = Me.RecordsetClone
        rstSource.Bookmark = Me.Bookmark '定位到当前选中的源记录
        Set rstmvSource = rstSource!Staff.Value '读取源记录的Staff多值集合
        
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !Site_Name = Me.Site_Name
                !Date_of_Dive = Me.Date_of_Dive
                !Time_of_Dive = Me.Time
                !O2 = Me.O2
                !First_Aid = Me.First_Aid
                !Spares = Me.Spares
                '其他普通字段按原有逻辑补充
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !Dive_Number
            
            '处理多值字段复制：写入新记录的Staff多值集合
            Set rstmvNew = !Staff.Value
            Do While Not rstmvSource.EOF
                rstmvNew.AddNew
                rstmvNew!Value = rstmvSource!Value '逐行复制源多值项的值
                rstmvNew.Update
                rstmvSource.MoveNext
            Loop
            rstmvSource.Close
            rstmvNew.Close
            
            'Duplicate the related records: append query.
            If Me.[DiveDetailssubform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [DiveDetails] (Dive_Number, CustDateID, Type, Price) " & _
                    "SELECT " & lngID & " As NewID, CustDateID, Type, Price " & _
                    "FROM [DiveDetails] WHERE Dive_Number = " & Me.Dive_Number & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
            MsgBox "Dive Sucessfully Duplicated. DONT FORGET TO CHANGE THE SITE NAME."
        
        End With
    End If

Exit_Handler:
    '释放对象
    If Not rstmvSource Is Nothing Then Set rstmvSource = Nothing
    If Not rstmvNew Is Nothing Then Set rstmvNew = Nothing
    If Not rstSource Is Nothing Then Set rstSource = Nothing
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "Duplicate_Click"
    Resume Exit_Handler
End Sub

你原有代码中的Form_Load和Form_Unload逻辑无需修改，可直接保留使用。

内容的提问来源于stack exchange，提问作者Oliver Watkins