Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: Copy a record from the same table

    Good day y'all,

    I have a very apprentice-level question

    I need to copy a record from tableA to tableA
    but I'm using this format

    Code:
    tableA
    field0key
    field1key
    field2
    field3
    
    dim var1 as int 
    dim var2 as str
    dim var3 as str
    
    var1 = dlookup("field1key","tableA","field1key = " & specificvar )
    var2 = dlookup("field2","tableA","field1key = " & specificvar )
    var3 = dlookup("field3","tableA","field1key = " & specificvar )
    
    docmd.runsql "INSERT INTO tableA (field0key,field1key,field2,field3) " & _
    "VALUES (" & diffvar & "," & var1 & ",'" & var2 & "','" & var3 & "')"
    ]


    and there's really over 10 fields in the tables
    and I'm using this many times in different forms,

    so I'm asking for a more concise way to do this

    thanks, guys

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Welcome to dbforums!

    Well...If I were do do this, I would do it this way:

    Code:
    Dim SQLstrg As String
    SQLstrg = "INSERT INTO TableA SELECT DISTINCT field0key,field1key,field2,field3 FROM " & _
              "TableA WHERE field1key=" & specificvar
    CurrentDb.Execute SQLstrg
    or shorter yet try this:

    Code:
    CurrentDb.Execute "INSERT INTO TableA SELECT DISTINCT field0key,field1key,field2,field3 FROM " & _
              "TableA WHERE field1key=" & specificvar
    Forget about all those DLookUp's. Have fun...

    .
    Last edited by CyberLynx; 07-25-06 at 02:46.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jul 2006
    Posts
    2
    thanks, i'll try this...

    but one more thing

    i'm not copying the an entire record, i still have to insert a different value for one of the primary keys( i have three for this table )

    so for some 10 fields of an existing record, i'll insert into the table, but one
    field would have to be different from the existing record

    in order to differentiate the primary key for the newly appended record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •