Results 1 to 3 of 3
  1. #1
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25

    Copying every field value in a record to a new record with a different primary key va

    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

  2. #2
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25
    Bump...

    Anyone?

  3. #3
    weasel7711 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    25
    Essentially I want to clone a record, but change the primary key value.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-23-2011, 10:39 AM
  2. Replies: 0
    Last Post: 02-25-2011, 09:40 AM
  3. Combo boxes both primary keys to select record
    By Alexandre Cote in forum Forms
    Replies: 3
    Last Post: 08-10-2010, 07:11 AM
  4. primary key at add new record time
    By elios115 in forum Database Design
    Replies: 1
    Last Post: 06-27-2010, 08:32 PM
  5. Copying selected fields to a new record
    By Lyle Bitikofer in forum Forms
    Replies: 0
    Last Post: 12-13-2009, 04:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums