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?
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?