Append query to copy a row?

M

Maury Markowitz

I'd like to duplicate almost all the information in a record (on SQL Server
backdend) into a new row. I tried doing this with recordsetclone, but this
appears to make Access die.

So I was reading through here and someone suggested an append query for a
similar role. This sounds like the solution I need. The only question I have
is how to say "copy this row" instead of "copy all rows". There aren't any
examples in the help file!
 
M

Mike Labosh

You may have to monkey with this a little, I live in SQL Server and the
syntax for Access might be a little different. :-/

INSERT INTO Table2 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM Table1
WHERE Table1.KeyCol = xyz

Where Col1 - Col3 are the columns you want to copy, Table1 is the
destination, Table2 is the source, KeyCol is the column in the source that
you want to search on to get "just this row", and xyz is the value of the
key in that row.
--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!
 
M

Maury Markowitz

INSERT INTO Table2 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM Table1
WHERE Table1.KeyCol = xyz
Click to expand...

Sadly that's the easy part, what I can't figure out is how to pass in "the
current record" as the key. At least not using the query designer in Access,
and it's Access that knows that answer.

I can ask Access what the key on the current record is (orderId in this
case), but I still can't figure out how to pass that into a stored proc. I
know it's something simple, but there's no examples!
 
M

Mike Labosh

Sadly that's the easy part, what I can't figure out is how to pass in "the
current record" as the key. At least not using the query designer in
Access,
and it's Access that knows that answer.
Click to expand...

in the query designer, in the column for your orderid field, on the criteria
row, Access lets you refer to a control on a form. You could put something
like this in there:

Forms![frmOrders]![OrderID]


--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!
 
M

Maury Markowitz

:

Ok, that worked!

Next question - the duplicated row then has to be modified (slighly).
However the recordset in question didn't return a value. It would be easy
enough to do a select at the end to return the new row.

Buuuut, in my newbieness, I don't know how to find that row. The pkey is a
autonumber, so I don't know what it will be until the INSERT runs. Is there
some way to find the new record in these cases?

Thanks!
 
Top