Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Unanswered: Copying table data with 'INSERT INTO' using VALUE+SELECT

    Hello Experts!

    I currently want to instert into a table a constant value, with a list of values selected from a table,

    I have a relationship as per below;

    #tblComponent..............#tblCardtoComponent.... ........#tblCard_Types
    ComponentID -----------Component_ref......................Card
    Notes...........................Card_ref----------------------Card_TypesID

    *Note that '----' means a relationship link.. '....' is just used to make the columns

    So I have a record with key ComponentID, I want to copy all the Card_TypesID's that are associated with that record to another record.... Not sure of the best way of doing it, whether take information from the querie that produces the list box, or just change the tblCardtoComponent table copy line with the old Component_ref, then add the new Component_ref from the new records ComponentID??

    sqlC = "INSERT INTO table tblCardtoComponent(Card_ref,Component_ref)SELECT (Card_ref FROM table tblCardtoComponent WHERE Component_ref = CompCarry"),VALUES ('" & Me.ComponentID & "')"
    CurrentDb.Execute sqlC, dbFailOnError

    the above doen't work, though gives an idea of what I need

    So say if I have table tblCardtoComponent with the below 2 columns;
    1 2
    1 3
    1 4
    9 6
    9 7
    9 8

    Where '1' is 'CompCarry' and 2,3,4 is Card_ref.... say Me.ComponentID = 5, I want the table to copy that listed against 'CompCarry'... hence the resulting table would look like this;

    1 2
    1 3
    1 4
    9 6
    9 7
    9 8
    5 2 <<<<<<<<<< additional lines added to the table
    5 3 <<<<<<<<<<
    5 4 <<<<<<<<<<

    Can this be done with a single INSERT INTO command, as there is a VALUE and a SELECT variable..??

    Any help will be appreciated!!
    Last edited by test2000; 12-03-09 at 11:06.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT 
      INTO tblCardtoComponent
         ( Card_ref
         , Component_ref )
    SELECT Card_ref 
         , Me.ComponentID
      FROM tblCardtoComponent 
     WHERE Component_ref = Me.CompCarry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    8
    Thanks a lot for that r937, though the code bombs out when I try and execute it, have I the sytax right?


    Private Sub CmdCopyCards_Click()
    Dim sqlCopy As String


    sqlCopy = "INSERT INTO tblCardtoComponent ( Card_ref, Component_ref )SELECT Card_ref, Me.ComponentID FROM tblCardtoComponent WHERE Component_ref = Me.CompCarry"
    CurrentDb.Execute sqlCopy, dbFailOnError <<<<<<<<<< code bombs out here

    Call RequeryList2

    End Sub


    Thanks,,,,,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those "Me" thingies that i highlighted in red were supposed to be actual values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    8
    sure, understand... Though Me.Component does not exist in tblCardtoComponent, will it still work if this is the case? Me.Component is a value from another table. Also CompCarry is a variable used in the code, so I dont' think 'Me.' applies here(?)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe it would make more sense if i said it this way:
    Code:
    INSERT 
      INTO tblCardtoComponent
         ( Card_ref
         , Component_ref )
    SELECT Card_ref 
         , 5
      FROM tblCardtoComponent 
     WHERE Component_ref = 1
    replace 5 with the value of the component_ref you want copied

    replace 1 with the value of the component_ref you want the rows copied from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2009
    Posts
    8
    Many thanks for your inputs Rudy they are much appreciated, the problem was that I had a data integrity link which was refusing the update... this is not needed so I removed the integrity....

    Thanks,
    lee.

Posting Permissions

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