Updating Data to Access Error
I'm getting an Error Message at the rs.MoveFirst point of my code :
runtime 3021 - No Current Record. There is a record with the search
Social Security Number in the Field SSN. And in Stepping into the
code, FSSN is correct in capturing that SSN from the Spreadsheet.
Obviously there is something wrong with my Select Statement?
I Also attempted to Select out only three Fields, but kept getting
errors in Parameters with the code : Select [field 1], field2, field3
From Table etc. One field was two words.
Appreciate any help on this, Thanks
Sub UpdateData()
' Update Data from Daily Run to WS Database
' Created 08/13/04 by Pete Trudell
'
Dim dbs As Database
Dim rs As Recordset
Dim WSdata As String
Dim notfound As Boolean
Dim FSSN As String
Dim FIndex As String
Dim ColD As Variant
Dim R As Variant
For R = 4 To 350
ColD = Worksheets("Update").Cells(R, 4).Value
If ColD = "" Then Exit For
notfound = False
FSSN = Range("D" & R).Value
FIndex = Range("C" & R).Value
WSdata = "mydatabase.mdb"
Set dbs = OpenDatabase(WSdata)
Set rs = dbs.OpenRecordset("Select * From WS Where SSN = 'FSSN';",
dbOpenDynaset)
rs.MoveFirst
Do
If Worksheets("Update").Cells(R, 3).Value = rs.Fields("Rec
Date").Value Then
notfound = True
rs.Edit
rs.Fields("Received").Value = True
rs.Update
End If
rs.MoveNext
Loop Until rs.EOF
Next R
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing
Msg = " Update to WS Complete"
MsgBox Msg, , "WS Update"
End Sub
|