Insert multiple records into child table using VBA (1 Viewer)

[Raddle]

HI - my first post so please be gentle! I am in a similar situation to brharrii. I have a hardware tble (servers) and software tbl (items installed). On a split form, I have hardware items set out and a subform datasheet of the related software items.

I want to duplicated the server record WITH all its related child records.

Hardware_ID is the primary key of the Hardware table and is autonumber.
Software_ID is the primray key of the Software table and is autonumber.
Hardware_ID is the Foreig key of the software table, linking the server to the software items.

This code successfully duplicates the main hardware record AND create a new Primary key and create ONE software recored with the correct foreign key but does not INSERT the related software items.

Any hints?

Option Compare Database
Private Sub Command603_Click()
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim Hardware_ID 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

!VM = Me.VM
!CPU = Me.CPU
!RAM = Me.RAM
!Stream = Me.Stream
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
Hardware_ID = !Hardware_ID

'Duplicate the related records: append query.
If Me.[dsSoftwareInventory].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Software] ( Hardware_ID, Software_Name, Version, License_Required, License_Type ) " & _
"SELECT " & Hardware_ID & " As NewHardware_ID, Software_Name, Version, License_Required, License_Type " & _
"FROM [Software] WHERE Software_ID = " & Me.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
End Sub