ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Updating Data to Access Error (https://www.excelbanter.com/excel-programming/307356-updating-data-access-error.html)

Pete T[_2_]

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

GJones

Updating Data to Access Error
 
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
.


cmart02[_2_]

Updating Data to Access Error
 
Pete,

Try changing as follows:

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

This should do the trick.

Regards
Robert

cmart02[_2_]

Updating Data to Access Error
 
Pete,

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

Regards,
Robert


All times are GMT +1. The time now is 09:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com