Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating data from Access | Excel Discussion (Misc queries) | |||
Error in querying data from MS Access | Excel Discussion (Misc queries) | |||
Updating data with an ODBC link to ACCESS | Excel Worksheet Functions | |||
Error updating pivot table source data | Excel Programming | |||
updating Access table with Excel data | Excel Programming |