Updating Data to Access Error

  • Thread starter Thread starter Pete T
  • Start date Start date
P

Pete T

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
 
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
 
Back
Top