Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string-how to reference cell/range
Hi,
The code below works great to export the data into Access but the SQL will not run I keep getting various error messages due to the reference of RANGE in Excel. Any help will be greatly apperciated. Sub Button1_Click() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long, strSQL As String Dim id Set db = OpenDatabase("\\Rtp-filer02a\wg-c\CandidateTrackingTool \Private\CTT_v1_be.mdb ") ' open the database Set rs = db.OpenRecordset("Candidate Evaluation", dbOpenTable) ' get all records in a table With rs .AddNew ' create a new record ' add values to each field in the record ..Fields("CandidateName") = Range("D5").Value ..Fields("School") = Range("D6").Value ..Fields("GradDate") = Range("D7").Value ..Fields("Major") = Range("D8").Value ..Fields("Degree") = Range("D9").Value ..Fields("gpa_4scale") = Range("D10").Value ..Fields("gpa_5scale") = Range("D11").Value ..Fields("Interviewer") = Range("B13").Value ..Fields("evalDate") = Range("H13").Value ..Fields("LocationPref") = Range("A17").Value ..Fields("Type") = Range("E17").Value ..Fields("BU") = Range("A19").Value ..Fields("JobTitle") = Range("B20").Value ..Fields("Uslegal") = Range("B23").Value ..Fields("Sponsorship") = Range("A25").Value ..Fields("legalcountries") = Range("G29").Value ..Fields("Current Immigration") = Range("G34").Value ..Fields("CiscoKnowledge_score") = Range("H41").Value ..Fields("CiscoKnowledge") = Range("F42").Value ..Fields("INITIATIVE_score") = Range("H46").Value ..Fields("INITIATIVE") = Range("F47").Value ..Fields("TECHNICALACUMEN _score") = Range("H51").Value ..Fields("TECHNICALACUMEN") = Range("F52").Value ..Fields("LEADERSHIP_score") = Range("H56").Value ..Fields("LEADERSHIP") = Range("F58").Value ..Fields("team player_score") = Range("H62").Value ..Fields("team player") = Range("F63").Value ..Fields("Communication_score") = Range("H67").Value ..Fields("Communication") = Range("F68").Value ..Fields("OverallAvg") = Range("G73").Value ..Fields("Recommendations") = Range("G74").Value ..Fields("CTT ID") = Range("B77").Value ..Fields("ImportDate") = Date ' add more fields if necessary... .Update ' stores the new record End With rs.Close strSQL = "SELECT tblcandidates_v2.ContactID, tblcandidates_v2.*" strSQL = strSQL & " FROM tblcandidates_v2 " strSQL = strSQL & " WHERE" & " ((tblcandidates_v2.ContactID)" & " =" & " & "Range( " & "B77" & ").Value & )""" Set rs = db.OpenRecordset(strSQL, dbOpenDynaset) With rs If .RecordCount 0 Then .MoveFirst .Edit !NextSteps = Range("G74").Value !Status = "Yes" .Update End If End With rs.Close Set rs = Nothing db.Close Set db = Nothing MsgBox "Complete" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string-how to reference cell/range
On Oct 23, 12:32 pm, SherryBerry wrote:
Hi, The code below works great to export the data into Access but the SQL will not run I keep getting various error messages due to the reference of RANGE in Excel. Any help will be greatly apperciated. Sub Button1_Click() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long, strSQL As String Dim id Set db = OpenDatabase("\\Rtp-filer02a\wg-c\CandidateTrackingTool \Private\CTT_v1_be.mdb ") ' open the database Set rs = db.OpenRecordset("Candidate Evaluation", dbOpenTable) ' get all records in a table With rs .AddNew ' create a new record ' add values to each field in the record .Fields("CandidateName") = Range("D5").Value .Fields("School") = Range("D6").Value .Fields("GradDate") = Range("D7").Value .Fields("Major") = Range("D8").Value .Fields("Degree") = Range("D9").Value .Fields("gpa_4scale") = Range("D10").Value .Fields("gpa_5scale") = Range("D11").Value .Fields("Interviewer") = Range("B13").Value .Fields("evalDate") = Range("H13").Value .Fields("LocationPref") = Range("A17").Value .Fields("Type") = Range("E17").Value .Fields("BU") = Range("A19").Value .Fields("JobTitle") = Range("B20").Value .Fields("Uslegal") = Range("B23").Value .Fields("Sponsorship") = Range("A25").Value .Fields("legalcountries") = Range("G29").Value .Fields("Current Immigration") = Range("G34").Value .Fields("CiscoKnowledge_score") = Range("H41").Value .Fields("CiscoKnowledge") = Range("F42").Value .Fields("INITIATIVE_score") = Range("H46").Value .Fields("INITIATIVE") = Range("F47").Value .Fields("TECHNICALACUMEN _score") = Range("H51").Value .Fields("TECHNICALACUMEN") = Range("F52").Value .Fields("LEADERSHIP_score") = Range("H56").Value .Fields("LEADERSHIP") = Range("F58").Value .Fields("team player_score") = Range("H62").Value .Fields("team player") = Range("F63").Value .Fields("Communication_score") = Range("H67").Value .Fields("Communication") = Range("F68").Value .Fields("OverallAvg") = Range("G73").Value .Fields("Recommendations") = Range("G74").Value .Fields("CTT ID") = Range("B77").Value .Fields("ImportDate") = Date ' add more fields if necessary... .Update ' stores the new record End With rs.Close strSQL = "SELECT tblcandidates_v2.ContactID, tblcandidates_v2.*" strSQL = strSQL & " FROM tblcandidates_v2 " strSQL = strSQL & " WHERE" & " ((tblcandidates_v2.ContactID)" & " =" & " & "Range( " & "B77" & ").Value & )""" Set rs = db.OpenRecordset(strSQL, dbOpenDynaset) With rs If .RecordCount 0 Then .MoveFirst .Edit !NextSteps = Range("G74").Value !Status = "Yes" .Update End If End With rs.Close Set rs = Nothing db.Close Set db = Nothing MsgBox "Complete" End Sub I've not done this exactly, but I would think you can't mix the Range.Value into your SQL. I'd set a variable to the value of B77 and use that in the SQL instead of the range reference. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String with Cell Reference | Excel Programming | |||
substitute the filename in a cell reference with a string in another cell. | Excel Discussion (Misc queries) | |||
How to get reference to a range as string? | Excel Programming | |||
reference to a cell within a string | Excel Programming | |||
Setting named range in VBA- how to set as formula/reference instead of text string? | Excel Programming |