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

Hi Pete;

I would do a test using MoveLast to see if there are any
record sets. If you are already at the first record and
you do the MoveFirst call it will generate an error.

You might want to look at using ADO instead of DAO.

Thanks,

Greg


-----Original Message-----
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
.

Pete,

Try changing as follows:

Set rs = dbs.OpenRecordset("Select * From WS Where SSN =" & FSSN & ";",
dbOpenDynaset)

This should do the trick.

Regards
Robert

Pete,

I just noticed you declare FSSN as a String. If it is a string, keep the
apostrophe in the SQL statement.

Regards,
Robert