Hi Experts,

I need to copy information from a table into the same table as per below

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??


I would have thought that it could be something like this, though I get an error when it tries to execute;

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

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


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!!