Copy one or Multiple Records

  • Thread starter Thread starter Dave Couch
  • Start date Start date
D

Dave Couch

I want to copy a single record to a new record in the same table. The copy
would be based on TransID (an index field in the table) = [a given value].
The given value would be equal to a form input value. I want to copy all of
the fields of the table and create a new reord. How do I do this?? I tried
working with the append query, but couldn't figure it out.

Dave Couch
 
I want to copy a single record to a new record in the same table. The copy
would be based on TransID (an index field in the table) = [a given value].
The given value would be equal to a form input value. I want to copy all of
the fields of the table and create a new reord. How do I do this?? I tried
working with the append query, but couldn't figure it out.

Dave Couch
Click to expand...

An append query (from the table into itself) would indeed be the correct
approach. It would be something like

INSERT INTO yourtable (field, field, field, field)
SELECT (field, field, field, field)
FROM yourtable WHERE TransID = [Forms]![YourFormName]![TransID];

If TransID is not an autonumber you'll need some provision to populate it with
a valid value; if it is, just leave it out of the append query and it will
assign a new autonumber value.
 
John W. Vinson said:
I want to copy a single record to a new record in the same table. The copy
would be based on TransID (an index field in the table) = [a given value].
The given value would be equal to a form input value. I want to copy all of
the fields of the table and create a new reord. How do I do this?? I tried
working with the append query, but couldn't figure it out.

Dave Couch
Click to expand...

An append query (from the table into itself) would indeed be the correct
approach. It would be something like

INSERT INTO yourtable (field, field, field, field)
SELECT (field, field, field, field)
FROM yourtable WHERE TransID = [Forms]![YourFormName]![TransID];

If TransID is not an autonumber you'll need some provision to populate it with
a valid value; if it is, just leave it out of the append query and it will
assign a new autonumber value.
Click to expand...
 
John W. Vinson said:
I want to copy a single record to a new record in the same table. The copy
would be based on TransID (an index field in the table) = [a given value].
The given value would be equal to a form input value. I want to copy all of
the fields of the table and create a new reord. How do I do this?? I tried
working with the append query, but couldn't figure it out.

Dave Couch
Click to expand...

An append query (from the table into itself) would indeed be the correct
approach. It would be something like

INSERT INTO yourtable (field, field, field, field)
SELECT (field, field, field, field)
FROM yourtable WHERE TransID = [Forms]![YourFormName]![TransID];

If TransID is not an autonumber you'll need some provision to populate it with
a valid value; if it is, just leave it out of the append query and it will
assign a new autonumber value.
Click to expand...
Click to expand...

Did you have an answer, or a further question, Dave?
 
John, I got my program working. I also needed to do something like Rikki's
issue. After the append I needed to update the new records with a new ID
number. However, the ID number had been copied from the old record. I
created a new field that would default to "0". I ran an update query on
those records that had a "0" in the new field, updated the ID field to the
new value and then updated the new field to "1", so it wouldn't update later.
Maybe a little crude, but it works for me. I am using Access 2003.

Rikki Ward said:
Hi John

I'm looking to do something very similar, copying single records.
I also include PrevID and NextID fields.

When I make a copy the TransID of the original record is copied to the
PrevID of the new record.
I would also like to back fill the NextID field or the original record with
the newly created TransID

TransID NextID PrevID Rest of fields
~ ~ ~ ~~~~~~~~~~
0054 0076 0 ~~~~~~~~~~ (orig record)
~ ~ ~ ~~~~~~~~~~
0076 0 0054 ~~~~~~~~~~ (copied record)

I am using access 2000
My problem is backfilling the NextID field of the original record.
To do that I tried
UPDATE TransData SET NextID = (select Transid from Transdata where
previd =54) WHERE Transid=54;
but the Paperclip said Operation must an updateable query.

Help

Many thanks
Rikki
Click to expand...
 
Back
Top