Attribute VB_Name = "basADOmoveData"
Option Compare Database
Option Explicit

Sub ADOmoveData()

'Declare your connection and recordsets
Dim cnConn As ADODB.Connection
Dim rsMoveFrom As ADODB.Recordset
Dim rsMoveTo As ADODB.Recordset

'Set your connection to the current project
'Set your table to a new ADO recordset
Set cnConn = CurrentProject.Connection
Set rsMoveFrom = New ADODB.Recordset
Set rsMoveTo = New ADODB.Recordset

'Open your tables
rsMoveFrom.Open "tblNames", cnConn, adOpenDynamic, adLockOptimistic
rsMoveTo.Open "tblNewNames", cnConn, adOpenDynamic, adLockOptimistic

Dim i As Integer    'record counter
i = 0   'initialize it as 0

'Move to the first record of the table your Moving data from
'and loop through them to the end
With rsMoveFrom
    .MoveFirst
    
Do Until rsMoveFrom.EOF

If !strLastName = "Jane" Then   'Your criteria to move
    
    'Move the data if it meets your criteria
    With rsMoveTo
        .AddNew
        !strLastName = rsMoveFrom!strLastName
        !strFirstName = rsMoveFrom!strFirstName
        i = i + 1   'add one everytime a record is changed.
        .Update
    End With

End If
    
    rsMoveFrom.MoveNext
Loop

End With

MsgBox "Number of records Moved: " & i, vbInformation, "Edit"

'Clean up your variables
rsMoveFrom.Close
rsMoveTo.Close
cnConn.Close
Set rsMoveFrom = Nothing
Set rsMoveTo = Nothing
Set cnConn = Nothing

End Sub