Add recordset data to local linked table.

[Starbuck]

Using MS Access VBA -- 2010:

I am trying to add a recordset to an existing table (linked).
Bkground:
I have a query that gives me a list of all records from two sources, share pt table and a spreadsheet, where the SeraialNumbers are identical.
I then display a list of matches, select specific ones from Listbox
then fill a recordset with the whole share pt record.
OK so far ...
Now I want to make a copy of the share pt record into a local table for backup,
then delete the record from share pt.

I thought I could use the recordset to copy the record directly into a local linked table.
I am stuck at coping the record.

Note: Share pt table is inventory, The spreadsheet is a salvage list .. I periodically
purge the inventory based on the salvage list.
I wanted to stream line the process.
This is what I have:
*************************************************************************************************
Private Sub cmdCpySel_Item_Click()
Dim sItem_idx As Variant
Dim sItem As Variant
Dim strSQL As String
Dim rstTable As Recordset


Set MyDB = CurentDb

Set rstTable = MyDB.OpenRecordset("Deleted Rec InventoryPC", dbOpenTable)


For Each sItem_idx In List1.ItemsSelected

sItem = List1.ItemData(sItem_idx)
MsgBox sItem, vbOKOnly, "Selected.."
'Now.. find sItem in Inventory db and get whole record...

strSQL = "SELECT * FROM Inventory WHERE (Inventory.[Service Tag] = " & Chr(34) & sItem & Chr(34) & ")"
'Set MyRec = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

MsgBox strSQL, vbOKOnly, "Query.."

'DoCmd.RunSQL strSQL
Set MyRec = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)

'Display results...

'Must first tell the list bx how many column's (fields) you have ...
List7.ColumnCount = MyRec.Fields.Count
'Now you can point the List at your source (SQL statement)
List7.RowSource = strSQL

'now .. add record to a local table .. for bkup.
rstTable.AddNew = MyRec 'Compile ERROR Expected fn or variable'


'Then delete record from share pt.


Next sItem_idx
Set MyRec = Nothing
Set rstTable = Nothing

End Sub


*************************
any ideas?

 

[LouisWatson]

'now .. add record to a local table .. for bkup.
rstTable.AddNew = MyRec 'Compile ERROR Expected fn or variable'

Pretty certain AddNew is a function so usage would be rstTable.AddNew(MyRec)

If that still fails you may have to produce a clone of the record as it does not belong to the recordset your adding it to.

Hope this helps ...