Inserting recordset value into table

[Pman12]

Below is the vba code I am working with. When it gets to the insert part I get an "Enter parameter value" dialog box with the value of the variable "id" above the cursor. I have to type in that value so it is inserted in the table otherwise it insert a blank. What am I doing wrong?

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim query As String
Dim id As String
Dim begin_date As Date
Dim diff As Integer

DoCmd.SetWarnin gs False
DoCmd.RunSQL "insert into Items (item) select distinct part from [01 BO Table]"


query = "select distinct Item,Begin_Date from items"
DoCmd.SetWarnin gs True
Set cmd = New ADODB.Command
cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = query
Set rs = cmd.Execute()

While Not rs.EOF
id = rs.Fields(0)
begin_date = rs.Fields(1)
diff = DateDiff("d", begin_date, Date)

DoCmd.RunSQL "insert into [Tracking values](ItemTracked, Days_In_BO) select " + (id) + "," + CStr(diff)

rs.MoveNext
DoCmd.RunSQL "delete * from Items where Item not in (select part from [01 BO Table])"
'DoCmd.RunSQL "delete * from [Tracking Values] where ItemTracked not in (select part from [01 BO Table])"
Wend

rs.Close

[nickvans]

Which insert is asking for the parameter value, the first or the second?

I'm not especially good at VBA, but I've found it helps to throw up message boxes in the code showing what I'm dumping into SQL.

Anyway, those are my two cents.

[Pman12]

Which insert is asking for the parameter value, the first or the second?

I'm not especially good at VBA, but I've found it helps to throw up message boxes in the code showing what I'm dumping into SQL.

Anyway, those are my two cents.

It happens at the second insert when I am using the variable 'id', which contains the first field of the recordset, in the insert statement.

[nico5038]

Hmm, for inserting values use:

DoCmd.RunSQL "insert into [Tracking values] (ItemTracked, Days_In_BO) VALUES ( " & [id] & ",'" & CStr([diff]) & "')"

assuming ID is numeric. Otherwise (like the Cstr([diff]) ) the value needs to be embedded within single quotes.

Nic;o)

[Pman12]

Thanks! That did it.