473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Inserting recordset value into table

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
4 9632
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
Pman12
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
3,080 Expert 2GB
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
Pman12
3
Thanks! That did it.
Oct 11 '07 #5

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

Similar topics

9
by: Hamster | last post by:
I need to display data like this: CompanyName1 Company | Date| Transactions Count -- headers CompanyName1 | Date| 1 -- data CompanyName2 | Date| 5 -- data...
2
by: J.D. Buehls | last post by:
I am displaying some values on a report. I am opening the .asp page with a Response.ContentType = "application/msword" and these particular values appear in a table. One column shows percentages...
2
by: Chris | last post by:
I am new to these forums and hope I am in the right place! I am working on an ASP (3.0) page that displays hotel data from a recordset in a table. In the last column of each row, I want to...
1
by: ing42 | last post by:
I have a problem with inserting records into table when an indexed view is based on it. Table has text field (without it there is no problem, but I need it). Here is a sample code: USE test GO...
11
by: altreed | last post by:
Hi, I am new to ASP, HTML and iis. I have my asp code working so that I can retrieve my desired record from the database. I can place the data on the screen in table form. All works fine. ...
2
by: PraveenKadkol | last post by:
I have 25000 Rows, that needs to be inserted into Oralce table from MsAccess When i run below code, i am not getting any error message, Still records are not inserting into destination table. ...
2
by: Rashmigs84 | last post by:
What is the code for inserting a value into a Access Database..plz help me with this problem
2
by: Eraser | last post by:
Hello, I'm just starting to learn PL/SQL. To get my feet wet, I'm trying to write a simple stored procedure that takes some values as parameters, and inserts those values into a table. For...
1
by: Chandan Kr Sah | last post by:
How to use like command in code behind page to compare with recordset value ? As I want to do comparision like below Dim rs As DAO.Recordset If rs1!.Value Not Like & '*Prep*'" Then '...do...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.