SQL Insert Variable?

[Gremmel]

I am stuck. I have a parent-child-grandchild applet I am working on. The user wants to be able to duplicate items at the child level and have it create the grandchild records as well.

The parent record is keyed on Quote_No and Quote_rev
The Child Records would be keyed on QuoteID, Quote_rev and ComponentID

I have my code working to create the record if the record being copied is the last record as:

1 strSQL = "insert into tblComponents (CompID, QuoteID, Quote_rev, MoreStuff)" _
2 & "Select CompID + 1, QuoteID, Quote_rev, MoreStuff FROM tblComponents " _
3 & "Where QuoteID = " & tmpQuote_num & " and Quote_rev = " & tmpQuote_rev & " and CompID = " & tmpCompID
4 CurrentDb.Execute strSQL

My issue is how to create the record if I am duplicating a record in the middle of the component children. So if I have 5 components numbered 1,2,3,4,5 and I am copying record 3 (perhaps a few times) to make records 6 (perhaps, 7 and 8). Is there a way to fix my select statement (Line 2) to use a variable?

As a point of interest since folks always seem to ask: Why are you doing that? The user is building a list of steps in a manufacturing process. They may have several very similar parts and all the related information for that step is the same but the part is slightly different. (1.5" rod vs 2.0" rod)

Thanks,
Grem

 

[Gremmel]

Bah, Figured it out.

2 & "Select " & newCompID & " As CompID, QuoteID, Quote_rev, MoreStuff FROM tblComponents " _

Thanks for stopping by.

Grem