VB Script - Access 2003 1

[ForHire]

I'm new to creating scripts - very new. Below is a current script and I've narrowed my issue down to using the CO_ID from the first query. If I change the value to a static value, it works so it's connecting the CO_ID to the Job_CoID in the insert. Any help would be appreciated.


MSODSC.CurrentSection.DataPage.Save
dim connection_string : connection_string = _
"provider=microsoft.jet.oledb.4.0;" _
& "data source=C:\Documents and Settings\My Documents\jobs.mdb"
dim conn : set conn = createobject("adodb.connection")
conn.open connection_string
dim sql : sql = "select Co_ID from Company where Co_Name in (select OnlineSourceName from OnlineSource where OnlineSourceDailyReview = Yes)"
dim rs : set rs = createobject("adodb.recordset")
rs.cursorlocation = 3 'Use a client-side cursor
rs.open sql, conn, 3, 3 'A static copy of a set of records, locked only when updating
Dim Job_id : Job_id = 0
Dim Job_RecID : Job_RecID = 0
Dim JobCo_ID : Job_CoID = Co_ID
Dim Ac_Description : Ac_Description = "Online Job Board Searched."

sql = "Insert into Activity (Job_id, Job_RecID, Job_CoID, Ac_Description) " _
& "values (" & Job_id & ", " & Job_RecID & ", " & JobCo_ID & ", '" & Ac_Description & "')"

conn.Execute sql

rs.close
conn.close

 

[PHV]

Do you really have a table named OnlineSourceName in jobs.mdb ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ForHire]

Yes - it exists and the query executes.

 

[ForHire]

I reread your question and will amend my answer.. No, there is not a table named OnlineSourceName - that's the field in the OnlineSource table. Executing that query provides the Co_ID from company based on the subset from the OnlineSource table.

 

[PHV]

OOps, sorry for the typo.
What about this ?
Dim JobCo_ID : Job_CoID = rs.Fields("Co_ID").Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ForHire]

Thank you for your suggestion. Unfortunately, that didn't solve the issue. I neglected to mention 2 things. First, the resulting error message is: Syntax Error in the Insert Into Statement. I have already taken the statement and replaced the variables with the values and it works; I also have tried the following "Dim JobCo_ID : Job_CoID = 9999" and this does work, but of course is not connected to my results from the initial select query. Secondly, I am using a Data Access Page.

 

[PHV]

What about this ?

sql = "INSERT INTO Activity (Job_id,Job_RecID,Job_CoID,Ac_Description)" _
 & " SELECT 0,0,C.Co_ID,'Online Job Board Searched.'" _
 & " FROM Company C INNER JOIN OnlineSource O ON C.Co_Name=O.OnlineSourceName" _
 & " WHERE O.OnlineSourceDailyReview=Yes"
conn.Execute sql

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ForHire]

This is working perfectly! Thank you for the different approach.