Append to same table 1

[ssatech]

Using 1 table called BOM with the following fields:
Parent Text
Parts Text
Qty Number
Ref Memo

Need to add/append Parts, Qty, Reference from one record to a another record in the same table (BOM).

Add the Part, Qty, Ref from this record 1
Parent Part Qty Ref
08-100 70010 2 T1
08-100 70012 1 T2
08-100 70013 4 T3

to this record 2
05-100 50005 1 Labor

to get this result in record 2
05-100 50005 1 Labor
05-100 08-100 2 T1
05-100 08-100 1 T2
05-100 08-100 4 T3

Trying to run this code behind a cmdButton on a Continous form with an Unbound textbox named NewBOM. I am using the recordset method but this just appends 1 record onto the same table. I want to add the all records on this continous form.

Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset

Set DB = CurrentDb()
Set rs = DB.OpenRecordset("BOM", dbOpenDynaset)
rs.AddNew
rs![Parent] = Me![NewBOM]
rs![Parts] = Me![Parent]
rs![Qty] = Me![Qty]
rs![Ref] = Me![Ref]
rs.Update
rs.Close
DB.Close
Set rs = Nothing
Set DB = Nothing

Any thoughts would be appreciated.

 

[Remou]

You can iterate through the recordsetclone for the subform, or you can create an append query if you have a unique id for the subform records, for example:

strSQL="INSERT INTO BOM (Parent,Parts,Qty,Ref) " _
& "SELECT '" & Me![NewBOM] & "' As New,Parts,Qty,Ref " _
& "FROM Bom WHERE Parent='" & Me!Parent & "'"
CurrentDB.Execute strSQL

 

[ssatech]

As usual Remou,
You make it so easy....

Your code did the job!!!
Many thanks once again.....