Adding multiple records

[meldrape]

Hi there,

One of my user's forms needs a "copy" function where they can create a new record, choose another record to copy from then copy only certain data from that record to the new record. I have two recordsets. Should I do a loop of some sort to loop through the selected records from source and copy to the target? I don't k now how to do this...
(strCurrent is the target ID and strPlan is the Source they chose from the list. THis is all working fine)

Dim dbs As DAO.Database, rstFrom As DAO.Recordset, rstTo As DAO.Recordset
Set dbs = CurrentDb
Set rstFrom = dbs.OpenRecordset("SELECT * FROM PDTL WHERE CPLANID = '" & strPlan & "'&quot
Set rstTo = dbs.OpenRecordset("SELECT * FROM PDTL WHERE CPLANID = '" & strCurrent & "'&quot

rstTo.AddNew
rstTo!CPLANID = strCurrent
rstTo!CCATGID = rstFrom!CCATGID
rstTo!CPARTID = rstFrom!CPARTID
rstTo!NCATGQTY = rstFrom!NCATGQTY
rstTo.Update

This works great for the first record, no prob. But there can be as many as 100 that need to be copied. When I do a DO LOOP it adds an infinite number of the first record and I have to stop it CTRL-BREAK. How do I make it loop each record from the Source and add it to the targer? Thoughts would be appreciated. Thanks.

 

[Steve101]

Meldrape,

You're very close to doing what you want. Try wrapping the following additional code around your rstTo.AddNew ... rstTo.Update lines:

rstFrom.MoveFirst '<---
While not rstFrom.EOF '<---
rstTo.addNew
...
rstTo.Update
rstFrom.MoveNext '<---
Wend '<---

Also remember to close and set the recordsets to nothing, when you have finished with them; ie.

rstFrom.Close
rstTo.Close
SET rstFrom = Nothing
SET rstTo = Nothing

Hope this helps,
Steve

 

[meldrape]

Great, Steve, it helps immensely because it WORKED. Thanks so much.