*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;
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;
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
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(?)