Copying every field value in a record to a new record with a different primary key value.
A little pre-information so that my question will make more sense:
I have a database that tracks our company's repairs with two main tables (there are other tables but they basically hold lookup information for data entry).
These two tables are called RMA and Product.
[tblRMA] contains all the information for each of the repairs. It's primary key is the .[RMA] field which is a unique number to each entry (obviously). The [tblProduct] contains an .[RMA] field, a .[Serial] number, and a .[Model] number.
The two tables have a many to one relationship. That is, there can be many [tblProduct] entries for one [tblRMA]. They are related on the .[RMA] field.
So I am writing code to allow the user to safely change the primary key value of an entry in tblRMA. This code will verify the inputs and check to make sure the new value is not already in the table. After validation it will copy the record matching the original primary key value to a new instance of that record, but with the new primary key value. So there will be two entries in the tblRMA that look like:
tblRMA:
RMA | Customer | DateIn | etc..
0251 | CompanyA | 1/3/11 | ..
0256 | CompanyA | 1/3/11 | ..
There is/are a (few) matching entries in tblProduct with that same RMA number:
tblProduct:
RMA | Model | Serial
0251 | A-097 | XHY2
0251 | H-876 | JU4H
So once there is an instance of the new RMA value in tblRMA, the old entries in tblProduct can be updated with the new RMA values to result in something like this:
tblProduct:
RMA | Model | Serial
0256 | A-097 | XHY2
0256 | H-876 | JU4H
Finally, the old RMA entry is deleted
tblRMA:
RMA | Customer | DateIn | etc..
---- | --------- |------- |-----
0256 | CompanyA | 1/3/11 | ..
So, with VBA I am not sure what is the easiest way to copy the old RMA entry into a new record with the new RMA value?
Or if there is a better algorithm to do this please suggest so.
Thank you,
Weasel7711