CODEWITHSUNDEEP
vbams-accessadodb
michi
michi

Reputation: 6625

How to move a record from rs1 to rs2 (ADODB.Recordsets) with VBA?

In Access, I have rs1 and rs2, both As ADODB.Recordset, they have the same fields. I want to move a record/row from rs1 to rs2.

I know how to do it with SQL or rs2.AddNew and iterating over the Fields, but I'd like to do it by using the ADODB.Record - Object, something like this.

Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
...

Private Sub MoveRecord (id As Long)

  Dim r As ADODB.Record

  rs1.filter = "id=" & id

  Set r = rs1(0)

  ' And now??? rs2.Addnew r is rubbish...


End Sub

I tried the Record.MoveRecord-method but it seems this is for moving files?

Record.MoveRecord (Source, Destination, UserName, Password, Options, Async)

Source and Destination are Strings, not RecordSets.

Can this be done, and if so, how?

Upvotes: 0

Views: 220

Answers (1)

Erik A
Erik A

Reputation: 32682

If you feel such a function is necessary, you can write it yourself...

Public Sub MoveCurrentRecord(rsSource, rsTarget)
   rsTarget.AddNew
   Dim fld
   For Each fld In rsSource.Fields
      rsTarget.Fields(fld.Name).Value = fld.Value
   Next
   rsTarget.Update
End If

Works with both ADO and DAO, and with both a record and a recordset as the source object. Note that you might want to implement additional logic to skip autonumber fields.

Upvotes: 2

Related Questions