<div>
You need two append queries. &nbsp;One to copy the main form record. &nbsp;The second to copy the subform record. &nbsp;The first query uses as criteria just the primary key of the record to select, the second query will use the FK (which is the PK of the parent record) and also criteria that ignores &nbsp;rejects. &nbsp;This issue is that you need to obtain the PK of the copy of the parent record before you copy the child records so I would do the process using &nbsp;a DAO recordset. 

<pre><code class="code-10 nolinks" id="code-20-42346237-1">Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim SavePK as Long
Dim strSQL as String

Set db = CurrentDB()
strSQL = &quot;SELECT * From YourMainFormTable WHERE thePK = &quot; &amp; Me.thePK
set rs = td.OpenRecordset (strSQL, dbOpenDynaset, dbSeeChanges)
 rs.AddNew
 rs!fld1 = Me.fld1
 rs!fld2 = Me.fld2
 rs!fld3 = Me.fld3
 ...
 ...
 SavePK = rs!PK
 rs.Update

rs.Close
strSQL = &quot;INSERT INTO yourSubformTable(FK, fld1, fld2, fld3) &quot;
strSQL = strSQL &amp; &quot; SELECT &quot; &amp; SavePK &amp; &quot;, fld1, fld2, fld3 From yourSubformTable&quot;
strSQL = strSQL &amp; &quot; WHERE FK = &quot; &amp; Me.thePK

db.Execute strSQL
</code></pre>
</div>

<div>
As you already have the record and child records retrieved, there's no need to run queries to retrieve them once more. 
Use the RecordsetClone which is extremely fast and requires no updates of the form(s): 
 

<pre><code class="code-10 nolinks" id="code-20-42346919-1">Private Sub CopyButton_Click()

 Dim rst As DAO.Recordset
 Dim rstAdd As DAO.Recordset
 Dim fld As DAO.Field
 Dim Count As Integer
 Dim Item As Integer
 Dim Bookmark As Variant
 Dim NewId As Long
 
 ' Copy parent record.
 Set rstAdd = Me.RecordsetClone
 Set rst = rstAdd.Clone
 
 ' Move to current record.
 rst.Bookmark = Me.Bookmark
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 ' Pick Id of the new record.
 .MoveLast
 NewId = !Id.Value
 End With
 ' Store location of new record.
 Bookmark = rstAdd.Bookmark
 
 ' Copy child records.
 Set rstAdd = Me!subChild.Form.RecordsetClone
 Set rst = rstAdd.Clone

 Count = rstAdd.RecordCount
 For Item = 1 To Count
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 ElseIf .Name = &quot;FK&quot; Then ' Adjust name of field holding the foreign key. 
 ' Use the new foreign key.
 .Value = NewId
 ElseIf .Name = &quot;Rejected&quot; And rst.Fields(.Name).Value = True Then
 ' Skip rejected record.
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 End With
 rst.MoveNext
 Next
 rst.Close
 rstAdd.Close
 
 ' Move to the new recordcopy.
 Me.Bookmark = Bookmark
 
 Set fld = Nothing
 Set rstAdd = Nothing
 Set rst = Nothing

End Sub
</code></pre>
/gustav
</div>

<div>
Gustav, The code executes with minor changes I made to correct the real field name to &quot;Reject&quot; instead of &quot;Rejected&quot;. &nbsp; The header record is copied perfectly. &nbsp; 
 
But the child records are all copied, not just the one flagged as &quot;Reject&quot; and also they are all getting the PK value from the original header record instead of the new PK value. 
 
Here is my altered code: 
 

<pre><code class="code-10 nolinks" id="code-20-42347065-1">Private Sub cmdCopyRejectItemsToNewRecord_Click()

 Dim rst As DAO.Recordset
 Dim rstAdd As DAO.Recordset
 Dim fld As DAO.Field
 Dim Count As Integer
 Dim Item As Integer
 Dim Bookmark As Variant
 Dim NewId As Long
 
 ' Copy parent record.
 Set rstAdd = Me.RecordsetClone
 Set rst = rstAdd.Clone
 
 ' Move to current record.
 rst.Bookmark = Me.Bookmark
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 ' Pick Id of the new record.
 .MoveLast
 NewId = !LoadRecordID.Value
 End With
 ' Store location of new record.
 Bookmark = rstAdd.Bookmark
 
 ' Copy child records.
 Set rstAdd = Me!subfrmLoadDetail.Form.RecordsetClone
 Set rst = rstAdd.Clone

 Count = rstAdd.RecordCount
 For Item = 1 To Count
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 ElseIf .Name = &quot;FK&quot; Then ' Adjust name of field holding the foreign key.
 ' Use the new foreign key.
 .Value = NewId
 ElseIf .Name = &quot;Reject&quot; And rst.Fields(.Name).Value = True Then
 ' Skip rejected record.
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 End With
 rst.MoveNext
 Next
 rst.Close
 rstAdd.Close
 
 ' Move to the new recordcopy.
 Me.Bookmark = Bookmark
 
 Set fld = Nothing
 Set rstAdd = Nothing
 Set rst = Nothing

End Sub
</code></pre>
</div>

<div>
Yes, you need to completely skip the rejected records. Here is one method: 
 

<pre><code class="code-10 nolinks" id="code-20-42347071-1">For Item = 1 To Count
 If rst!Reject.Value = False Then
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 ElseIf .Name = &quot;FK&quot; Then ' Adjust name of field holding the foreign key.
 ' Use the new foreign key.
 .Value = NewId
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 End With
 End If
 rst.MoveNext
Next
</code></pre>
 
As for the FK, I guess you forgot to adjust this line to hold your actual field name: 
 

<pre><code class="code-1 nolinks" id="code-20-42347071-2">ElseIf .Name = &quot;FK&quot; Then ' Adjust name of field holding the foreign key.
</code></pre>
/gustav
</div>

<div>
Is still coping the child records that AREN'T flagged as &quot;Reject&quot; and is copying them with the original PK value. &nbsp;I'm sure I'm missing something. &nbsp; Should be copying the &quot;Reject&quot; record and assigning the PK as the new PK. &nbsp;My code: 
 

<pre><code class="code-10 nolinks" id="code-20-42347099-1"> Dim rst As dao.Recordset
 Dim rstAdd As dao.Recordset
 Dim fld As dao.Field
 Dim Count As Integer
 Dim Item As Integer
 Dim Bookmark As Variant
 Dim NewId As Long
 
 ' Copy parent record.
 Set rstAdd = Me.RecordsetClone
 Set rst = rstAdd.Clone
 
 ' Move to current record.
 rst.Bookmark = Me.Bookmark
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 ' Pick Id of the new record.
 .MoveLast
 NewId = !LoadRecordID.Value
 End With
 ' Store location of new record.
 Bookmark = rstAdd.Bookmark
 
 ' Copy child records.
 Set rstAdd = Me!subfrmLoadDetail.Form.RecordsetClone
 Set rst = rstAdd.Clone

 Count = rstAdd.RecordCount
 For Item = 1 To Count
 If rst!Reject.Value = False Then
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 ElseIf .Name = &quot;LoadsHeaderID&quot; Then ' Adjust name of field holding the foreign key.
 ' Use the new foreign key.
 .Value = NewId
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 End With
 End If
 rst.MoveNext
 Next
 
 rst.Close
 rstAdd.Close
 
 ' Move to the new recordcopy.
 Me.Bookmark = Bookmark
 
 Set fld = Nothing
 Set rstAdd = Nothing
 Set rst = Nothing
</code></pre>
</div>

<div>
First: 

<blockquote>
copy a record from a main form's header section AND the sub-forms section with the exception of records in the sub-form section that have been marked as &quot;Reject&quot;
</blockquote>
 
Now: 

<blockquote>
Should be copying the &quot;Reject&quot; record
</blockquote>
 
If so, change False to True. 
 
Can't tell about the FK. Debug more carefully. The original code was tested successfully here. 
 
/gustav
</div>

<div>
Changing to True worked. &nbsp;And I found my error with the PK value. &nbsp;But now, the copied child record has Reject as true because it was in the original but I need the copied record to be false. 
 
??
</div>

<div>
Ah Steve, you should be a bit more creative ... 
 
In my original code: 
 

<pre><code class="code-1 nolinks" id="code-20-42347252-1"> ElseIf .Name = &quot;Rejected&quot; And rst.Fields(.Name).Value = True Then
 ' Skip rejected record.
</code></pre>
 
Modify to: 
 

<pre><code class="code-1 nolinks" id="code-20-42347252-2"> ElseIf .Name = &quot;Reject&quot; Then
 ' Always set to False.
 .Value = False
</code></pre>
/gustav
</div>

<div>
I think my creativity has reach a breaking point. &nbsp;Using the following code I am getting ALL child records copied and just want the one(s) flagged as &quot;Reject&quot; to be copied. &nbsp;The good news is that the ones originally flagged as &quot;Reject&quot; are no longer flagged as &quot;Reject&quot; in the copies. 
 
So, only thing left to do is NOT copy the ones not flagged as &quot;Reject&quot;. 
 
Sorry to be a pain. 
 

<pre><code class="code-10 nolinks" id="code-20-42348441-1"> Dim rst As DAO.Recordset
 Dim rstAdd As DAO.Recordset
 Dim fld As DAO.Field
 Dim Count As Integer
 Dim Item As Integer
 Dim Bookmark As Variant
 Dim NewId As Long
 
 ' Copy parent record.
 Set rstAdd = Me.RecordsetClone
 Set rst = rstAdd.Clone
 
 ' Move to current record.
 rst.Bookmark = Me.Bookmark
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 ' Pick Id of the new record.
 .MoveLast
 NewId = !LoadRecordID.Value
 End With
 ' Store location of new record.
 Bookmark = rstAdd.Bookmark
 
 ' Copy child records.
 Set rstAdd = Me!subfrmLoadDetail.Form.RecordsetClone
 Set rst = rstAdd.Clone

 Count = rstAdd.RecordCount
 For Item = 1 To Count
 With rstAdd
 .AddNew
 For Each fld In .Fields
 With fld
 If .Attributes And dbAutoIncrField Then
 ' Skip Autonumber or GUID field.
 ElseIf .Name = &quot;LoadRecordID&quot; Then ' Adjust name of field holding the foreign key.
 ' Use the new foreign key.
 .Value = NewId
 
 ElseIf .Name = &quot;Reject&quot; Then
 ' Always set to False.
 .Value = False
 ' Skip rejected record.
 
 Else
 .Value = rst.Fields(.Name).Value
 End If
 End With
 Next
 .Update
 End With
 rst.MoveNext
 Next
 rst.Close
 rstAdd.Close
 
 ' Move to the new recordcopy.
 Me.Bookmark = Bookmark
 
 Set fld = Nothing
 Set rstAdd = Nothing
 Set rst = Nothing
</code></pre>
</div>

<div>
I thank you so much. &nbsp;I now have a new challenge with this whole thing but am awarding the points for this one. &nbsp;I'll be posting a new topic once I figure out how to word it.
</div>

<div>
You are welcome! 
 
/gustav
</div>

<div>
<div class="content wysiwyg-content">
I'm trying to figure out how to copy a record from a main form's header section AND the sub-forms section with the exception of records in the sub-form section that have been marked as &quot;Reject&quot; (a check-box in the sub-form section). 
 
Any help much appreciated.
</div>
</div>

I'm trying to figure out how to copy a record from a main form's header section AND the sub-forms section with the exception of records in the sub-form section that have been marked as "Reject" (a check-box in the sub-form section).

Any help much appreciated.

How copy record from form header section and sub-form detail section

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.