appending records in a recordset

[donaldg]

Hi, I've been going at this for hours, apologies for my cluelessness.

I'm trying to extract records from a table in MSA 2003, alter them and
append them as new records, which is the point where it's breaking. I pasted
my latest attempt below, (>> where it breaks). I'm also quite open to
learning a better way to skin this cat overall, as this is my first venture
into recordsets. Thanks!

strSQL = "SELECT a002t_titling.* FROM a002t_titling WHERE a001t_el_id =
" & varElPreFillID

Set rsPrefill = New ADODB.Recordset
rsPrefill.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

strSQL = "a002t_titling"

Set rsNew = New ADODB.Recordset
Set rec = New ADODB.Record
rsNew.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

While Not rsPrefill.EOFrsNew.AddNew rec
rsNew!a001t_el_id = varNewElID
rsNew.Update
rsPrefill.MoveNext
rec.Close
Wend

 

[donaldg]

Can anyone tell me what the syntax is for adding records to a recordset? I
think the rest of the procedure below works OK, this is the part I'm not
finding how to do:

 

[Larry Daugherty]

Try something like this:

Set mydb = CurrentDB()
Set rst = mydb.OpenRecordset("BASE")
rst.AddNew
rst!SHOP_NAME = NewData
rst.Update
rst.Close
mydb.Close

HTH

 

[donaldg]

Thanks Larry,

Does this add the whole record from the other recordset, or just update the
SHOP_NAME field in the new record. I'm trying to go through a recordset, add
each record to the underlying table as a new record, then update the foreign
key of each record to a new id. Thanks again!

 

[Larry Daugherty]

It adds a complete record - you cannot add just a part of a record.
However all fields in the new record that are not specified are null.

HTH