Excel range in SQL line-how to reference

[SherryBerry]

Can anyone tell why the cell range reference in the SQL line will not
run? I'm not sure how to reference cells in the active worksheet in
the SQL.

Any help would be greatly apperciated!
Thanks,
SB


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

 

[Shauna Kelly]

Hi SherryBerry

You've posted this question in a Word newsgroup. I suggest you re-post to an
Excel newsgroup.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word