Creating a new record in the same recordset

[gknierim]

I have an existing recordset open. I want to take the record that I am currently sitting at and copy or clone it as a new record. Do I need two recordsets or just one? How do I go about doing this? I thought about using the Clone method but I can't find much documentation on it. Any help would be great!

[Aaron Young]

You could do something like this:
private Sub Command1_Click()
Dim oDB as Database
Dim oRS as Recordset
Dim oCL as Recordset
Dim iField as Long

set oDB = OpenDatabase("C:\Test.mdb")
set oRS = oDB.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
set oCL = oRS.Clone
oCL.Bookmark = oRS.Bookmark
oRS.AddNew
for iField = 0 to oRS.Fields.Count - 1
oRS(iField) = oCL(iField)
next
oRS.Update
oRS.Close
oCL.Close
oDB.Close
End Sub




Aaron Young
Analyst Programmer
[email protected]
[email protected]
Certified AllExperts Expert: http://www.allexperts.com/displayExp...p?Expert=11884

[Johnny101]

couldn't you use the .AddNew method of the recordset? Then when you're done with it, maybe try the .Update method to save those records to the db. As for populating it, I don't think you could have just one recordset, add a new row and then get values from a row in the middle. For the Clone method, I think the following will work:

dim rs as recordset
dim rs2 as recordset

rs.open sql...

'move the record you want to copy
rs.move x

'i think the new recordset will be on the same record
set rs2 = rs.clone

rs.addnew
rs(0) = rs2(0)
rs(1) = rs2(1)
'etc...




This is just a suggestion - but it sounds good.

Hope this helps (a little, anyway),
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

[gknierim]

This works excellent. I was playing around with the clone method but I forgot all about those Bookmark. They come in very handy!!! Thanks!!