By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,379 Members | 3,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,379 IT Pros & Developers. It's quick & easy.

Inserting recordset value into table

P: 3
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.SetWarnings False
DoCmd.RunSQL "insert into Items (item) select distinct part from [01 BO Table]"


query = "select distinct Item,Begin_Date from items"
DoCmd.SetWarnings True
Set cmd = New ADODB.Command
cmd.ActiveConnection = 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
Oct 10 '07 #1
Share this Question
Share on Google+
4 Replies


P: 62
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.
Oct 10 '07 #2

P: 3
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.
Oct 10 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 11 '07 #4

P: 3
Thanks! That did it.
Oct 11 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.