access複製父窗體和子窗體的選擇的記録

2019-09-05 15:14:00
tmtony8
原創
317

在應用程序中,我們有時需要複製父子窗體選中的記録

比如複製訂單記録。包括訂單信息,訂單明細信息,詳細代碼如下:

Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    
    '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
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !訂單id = Me.訂單id
                !客戶 = Me.客戶
                !髮貨日期 = Me.髮貨日期
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !ID
            
            'Duplicate the related records: append query.
            If Me.subform.Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO 訂單明細 ( 訂單id, 訂單明細id, 數量, 商品, 單價 ) " & _
                    "SELECT 訂單id, 訂單明細id, 數量, 商品, 單價 " & _
                    "FROM 訂單明細 WHERE 訂單id = " & ID & ""
                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
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler



此時訂單和訂單明細中都能複製添加上記録


    分享