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