Copying records without duplicating the primary key

[Rohdem]

I have a table that has the field part_num as the primary key. I need to be able to copy the record to a new record, and just change the part number, i.e. I could copy the data for part number H4101 to a new record with a part number H4101a. You cannot just cut and paste because it would create a duplicate value in the primary key. I have 30+ fields in the table, so I don't want to just do a series of 30 assignment statements. Does anyone know of a clean way of going about this?

Mike Rohde
rohdem@marshallengines.com

 

[MichaelRed]

In effect, you do need to copy the fields. There are some shortcuts.

One method is to have a command button to copy the selectd. Behind the command button, place the code to "prompt" for the new part number (InputBox) and then call a module which would just do a loop for each field, copying the currnnt record value to a new record - except for the Key filed, which would use the new value.

Another approach would be to create a "shadow" table which has the same structure as the original table. Copy the "current record" from the real table, make alterations to the fileds as desired (especially the Key Field). Do an append from the 'shadow' table to the original table. Delete the record from the 'shadow' table.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[TheInsider]

Maybe I'm really of here but why not just create an append query to append from the same table, then choose all fields except the autonumber field, add your criteria, and then for the part number, just change the source to [Part Number] & "a".

Rob Marriott
rob@career-connections.net

 

[Rohdem]

Rob, I may not have been clear enough. There is no autonumber field. The primary key is the part number, which is assigned by the user. Adding a part number with an 'a' on the end is just an example. I need to allow the user to copy the record for a certain part number and specify exactly what the new part number should be.

Michael, I'll probably end up using the loop through the records idea.


Thanks for the input guys!!

Mike Rohde
rohdem@marshallengines.com