Solved

"rowset does not support scrolling backwards" in Access2000

Posted on 2004-09-13
8
307 Views
Last Modified: 2013-12-25
I keep on getting "rowset does not support scrolling backwards" on the rst.Find.  When I hit "Debug" to get to that line in the code and F8 to run it again, it executes, but goes to EOF. Before the F8, EOF and BOF are false, and the record set is, in fact, at the first record. I know for a fact that the record searched for is there. And if it weren't, it would just go EOF. Here's the code.

      If rst Is Nothing Then Set rst = New ADODB.Recordset
      If rst.State <> adStateOpen Then
         rst.CursorLocation = adUseClient
         If Recordset!SOURCE = "COUNTY_NM" Then
            rst.Open "SELECT * FROM county_nm", CurrentProject.Connection, , adLockOptimistic
         Else
            rst.Open "SELECT * FROM permits_nm", CurrentProject.Connection, , adLockOptimistic
         End If
      End If
      If Not (rst.BOF Or rst.EOF) Then
         strFind = "ADDRESS='" & Me.ADDRESS & "'"
         rst.MoveFirst
         rst.Find strFind
         If Not rst.EOF Then
              ' record is found and load it.

0
Comment
Question by:cellorando
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 60 total points
ID: 12047446
Try:
           rst.Open "SELECT * FROM county_nm", CurrentProject.Connection, adOpenStatic, adLockOptimistic
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 120 total points
ID: 12048247
Your currect cursor is set to adOpenForwardOnly, it is a default setting, therefore you may not move through it or test the BOF and EOF status

adOpenStatic would not work if you plan to update your data later on.  You may wan to try adOpenDynamic or adOpenKeyset.

Here is an explaination of the Cursor property:

http://www.activeserverpages.ru/ADO/daprop02_7.htm

Leon

0
 

Author Comment

by:cellorando
ID: 12049126
I tried adOpenStatic, adOpenDynamic, and adOpenKeyset and got the same error.  The addresses are all correct, and the rst.Find always worked before.  rst.MoveFirst seems to work and EOF & BOF are both false before the Find.

0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 120 total points
ID: 12050677
Try:

 rst.CursorLocation = adUseServer

Leon
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Assisted Solution

by:domj
domj earned 60 total points
ID: 12051953
I reckon the error is occurring on the rst.MoveFirst. I have had this one before myself. My guess would be that it would only be when the condition rst.State <> adStateOpen is correct and the script is working from an already open recordset. I am assuming that the cursor would be wherever the previous find statement left it and therefore would need to track back to find MoveFirst. My suggestion would simply be what I did which was to make the recordset open each time whether it is currently open or not (obviously if it was currently open you would need to close it before re-opening).

Dom
0
 
LVL 5

Assisted Solution

by:KarcOrigin
KarcOrigin earned 60 total points
ID: 12052029
Hi,
I have test your sample code (with little modification) and it is working fine. I used MS Ado library 2.7 (msado15.dll)


Private Sub Command1_Click()
    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
   
    If rst.State <> adStateOpen Then
        rst.CursorLocation = adUseClient
        con.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\My_Space\db1.mdb")
        rst.Open "SELECT * FROM MyDOB", con, , adLockOptimistic
    End If
   
    Dim strFind As String
    If Not (rst.BOF Or rst.EOF) Then
        strFind = "ADDRESS='" & Me.ADDRESS & "'"
        rst.MoveFirst
        rst.Find strFind
        If Not rst.EOF Then
            Debug.Print "Record found"
        Else
            Debug.Print "Record not found"
        End If
    End If
   
    rst.Close
    Set rst = Nothing
    con.Close
    Set con = Nothing

End Sub

PS: Just make sure that your searched string is correct and add Me.ADDRESS.Text if it is a text box. By the way what is this control?

Thanks
0
 

Author Comment

by:cellorando
ID: 12058400
None of the comments helped me unfortunately.  I was hoping someone had run into the problem.  I was probably supposed to put the question into the DB/Access instead of programming. Lean's link to www.activeserverpages.ru is a useful one.

I have dupmed the code entirely and am finished a completely different module which I had already started.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 12058674
Very generous, in the curcumstances
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now