具有多值欄位和子表單的重復表單按鈕

請問你能幫忙嗎？我正在嘗試使用 vba 從按鈕復制??表單。多年來，使用 Allen Browne 的“復制表單和子表單中的記錄”一直有效。http://allenbrowne.com/ser-57.html

現在我想將其中一個欄位更改為多值。我理解多值欄位的困難，但這是一個已有 10 年歷史的資料庫，我需要做的就是讓這個欄位能夠存盤多個值，所以認為這比創建新的連接表和更新所有相關內容更容易。

我目前在 rstmv = rstmv.Value 行得到Invalid use of Property。

我嘗試了許多版本并得到不同的錯誤。

我想我應該將多值欄位的值作為單獨的記錄集打開，更新它然后回圈遍歷這些值，但我很困惑，因為我不確定我在做什么。

任何幫助都會得到很大的幫助。

謝謝

這是我一直在使用的代碼：

'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    Dim rst As Recordset
    Dim rstmv  As Recordset2
    
        'Save and 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
        '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
                
     Set rst = Me.RecordsetClone
       Set rstmv = rst!Staff.Value
                
                Do While Not rstmv.EOF
    
   rsp.Edit
    rstmv.Edit
    
    
    rstmv.AddNew ' Add a new record to the asp Recordset
    rstmv = rstmv.Value
    rstmv.Update ' Commit the changes to the asp Recordset
    imt.MoveNext
Loop
    .Update
               
                !O2 = Me.O2
                !First_Aid = Me.First_Aid
        !Spares = Me.Spares

'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !Dive_Number
            
            '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:
    Exit Sub

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





Private Sub Form_Load()
    Dim varID As Variant
    Dim strDelim As String
    'Note: If CustomerID field is a Text field (not a Number field), remove single quote at start of next line.
    'strDelim = """"

    varID = DLookup("Value", "tblSys", "[Variable] = 'DiveIDLast'")
    If IsNumeric(varID) Then
        With Me.RecordsetClone
            .FindFirst "[dive_number] = " & strDelim & varID & strDelim
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Dim rs As DAO.Recordset

    If Not IsNull(Me.Dive_Number) Then
        Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
        With rs
            .FindFirst "[Variable] = 'DiveIDLast'"
            If .NoMatch Then
                .AddNew        'Create the entry if not found.
                    ![Variable] = "DiveIDLast"
                    ![Value] = Me.Dive_Number
                    ![Description] = "Last DiveID, for form Dive Planner" & Me.Name
                .Update
            Else
                .Edit          'Save the current record's primary key.
                    ![Value] = Me.Dive_Number
                .Update
            End If
        End With
        rs.Close
    End If
    Set rs = Nothing
End Sub

uj5u.com熱心網友回復：

需要源資料記錄集和目標記錄集。還應該將記錄集型別明確宣告為 DAO。考慮：

    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    Dim rstF As DAO.Recordset
    Dim rstT As DAO.Recordset
    Dim rstmvF As DAO.Recordset2
    Dim rstmvT 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
        Set rstF = CurrentDb.OpenRecordset("SELECT * FROM Dives WHERE Dive_Number = " & Me.Dive_number)
        Set rstmvF = rstF!Staff.Value

        '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 of new record.
            .Bookmark = .LastModified
            lngID = !Dive_number
            Set rstT = CurrentDb.OpenRecordset("SELECT * FROM Dives WHERE Dive_Number = " & lngID)
            Set rstmvT = rstT!Staff.Value
            rstT.Edit
            Do While Not rstmvF.EOF
                rstmvT.AddNew ' Add a new record to the asp Recordset
                rstmvT!Value = rstmvF!Value
                rstmvT.Update ' Commit the changes to the asp Recordset
                rstmvF.MoveNext
            Loop
            rstT.Update

標籤：vba ms-access 多值

上一篇：我與MSAccess資料庫系結的datagridview顯示錯誤的時間格式

下一篇：重繪所有VBA代碼相當于從另一個表單重繪表單中的資料？