Recordset Updating Table

[Telsa]

Is it possible to update a recordset then append it to a table. Or does SQL work on tables and queries only?

What I'm needing to do is duplicate certain entries based on one field's value. So if I have a partno and scanner field, I want to have all partno of sj500 records and copy the scanners to a new partno in same table.

I'm thinking that if I create a recordset to select only sj500 records, update the recordset with new partno, then append that to the table the recordset came from. Or am I thinking about this all wrong?

Thoughts??

Mary )

 

[tlbroadbent]

You can do it all with one APPEND query.

Insert tbl (PartNo, Scanner, Col3, Col4, Col5, ...)
Select "SJ550", Scanner, Col3, Col4, Col5, ...
From tbl
Where PartNo = 'SJ500' Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Telsa]

Okay Thanks! I have done this but it doesn't seem to be capturing the data to copy and append.


strSQLScanner = "INSERT INTO tblScannerPartJoin(PartNoJoin, Scanners) SELECT '" _
& strProdNo & "', Scanners FROM tblScannerPartJoin WHERE " _
& "PartNo = '" & strOldProdNo & "';"

DoCmd.RunSQL strSQLScanner


Mary X-)