SQL string-how to reference cell/range

[SherryBerry]

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

 

[chip.gorman]

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.