具有新主键(VBA)的重复记录 [英] Duplicate Record with New Primary Key (VBA)

问题描述

我有一个很大的记录要复制，然后用新版本的表单打开一个带有新主键ID的表单.可以在Access VBA中完成此操作，而不必遍历所有字段来复制数据吗?

I have a very large record that I'm trying to duplicate, then open a form with the new version with a new primary key ID. Can this be done in Access VBA without having to iterate through all the fields to copy the data?

谢谢！

推荐答案

最快和最简单的方法是使用DAO和以下形式的 RecordsetClone :

The fastest and simplest way is to use DAO and the RecordsetClone of the form:

Private Sub cmdDuplicate_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field

  If Me.NewRecord = True Then Exit Sub

  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "SomeFieldToPreset" Then
                rstInsert.Fields(.Name).Value = SomeValue
              ElseIf .Name = "SomeFieldToExclude" Then
                ' Leave blank
              Else
                ' All other fields.
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

这会将表单从当前记录移到新记录.您可以轻松地对其进行修改以选择新的ID，然后使用新记录打开其他表单.

This moves the form from the current record to the new record. You can easily modify that to pick the new ID and open the other form with the new record.

这篇关于具有新主键(VBA)的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！