[RESOLVED] Best method for moving record to another table?

[WarrenW]

I have some records I need to move from one table to another. But in the second table it has some extra fields that need to be filled in also. For example, if I sell an item, I want to move the item record to the sales table but also include the selling price, date, etc. in the extra fields.

1. I can load all the fields from the inventory table to variables and then insert them in the Sales table with the extra fields.

or

2. I can use an Insert command like below and then do an Update command to update the extra fields. I wish I could do it all in one sql statement.

rs.Open "INSERT INTO Sales SELECT * FROM StoreInv WHERE ItemNo = '" & ItemNo2 & "'", cn

Thanks!