子窗体中的记录重复到新记录 [英] Duplicate records in Subform to New record

问题描述

我在制造过程[Shedmodels]中有一个装配表.组件在单独的表[ShedModelsComponents]中列出. [ShedModels]中的主键是[ModelNumber]. [ShedModelsComponents]中有一个字段，也称为[ModelNumber].因此，表[ShedModels]中的每个组件都分配给某个程序集.接下来，我为[ShedModels]创建了一个表单，并嵌入了[ShedModelsComponents]的子表单.程序集的所有组件看起来都像我想要的.到目前为止，一切都很好.现在，我的许多程序集都使用几乎相同的组件，因此我想将一个组件中的所有组件复制或追加到[Shed Models]中的新记录中.我已经在MS网站上找到了此代码.

I have a table for my assemblies in a manufacturing process [Shedmodels]. The components are listed in a separate table [ShedModelsComponents]. The primary key in [ShedModels] is [ModelNumber]. There is a field in [ShedModelsComponents] also called [ModelNumber]. Each component is thus assigned to a certain Assembly in table [ShedModels]. Next I have created a form for [ShedModels] with a subform for [ShedModelsComponents] embedded. All the components for the assembly appear like I want. So far, so good. Now many of my assemblies use almost the same components, so I would like to copy or perhaps append all the components from one assembly to a new record in [Shed Models]. I have found this code on MS website.

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![ModelNumber]

' Add new record to end of Recordset object.
With Rst
   .AddNew
      !ModelNumber = Me!ModelNumber
      !ModelDesc = Me!ModelDesc
      !ModelSalePrice = Me!ModelSalePrice
   .Update                     ' Save changes.
   .Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.

DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Shed Models Components"
DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
Me![Shed_Models_Query].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:

End Sub

但是它返回的错误是，它将创建重复的型号，这是我毫无疑问的.如何将带有所有零部件的装配体复制到新记录，但更改型号(将由用户输入)?

but it returns the error that this would create duplicate Model numbers, which I don't doubt. How can I copy my assembly with all the components to a new record, but change the Model Number (which would be user entered)?

推荐答案

首先，复制父记录(就像您一样).但是不要让表单移至新记录.

First, copy the parent record (like you do). But don't let the form move to the new record.

下一步，获取新的PK.

Next, obtain the new PK.

然后，使用新的PK复制子记录.这是一个骨架-您将使用子窗体的RecordsetClone.见下文.

Then, copy the child records using the new PK. This is a skeleton - you would use the RecordsetClone of the subform. See below.

最后，将父表单移动到新记录.子表单将自动更新.

Finally, move the parent form to the new record. The subform will automatically update.

Public Sub CopyRecords()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim strSQL      As String
  Dim lngLoop     As Long
  Dim lngCount    As Long

  strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _
                "DEFx" & "' Order by Total"

  ' Change this to the RecordsetClone of the subform.
  Set rstInsert = CurrentDb.OpenRecordset(strSQL)  

  Set rstSource = rstInsert.Clone
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "ParentID"  ' Name of FK.
                  rstInsert.Fields(.Name).Value = NewID  ' The new ID of the parent.
              ElseIf .Name = "Total" Then
                ' Insert some default value.
                rstInsert.Fields(.Name).Value = 0
              ElseIf .Name = "PROCESSED_IND" Then
                ' Empty a field.
                rstInsert.Fields(.Name).Value = Null
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

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