Solved RS Object invalid or no longer set (hair pulling out) (1 Viewer)

LarryE

Active member
Local time
Yesterday, 16:18
Joined
Aug 18, 2021
Messages
216
You come in here and tell us you have an error out problem. You give us partial code and just assume we know things we can't possibly know and call one of us "cowboy". Not a good start. People come into this forum with every level of experience you can imagine. We can't possibly know what level of experience everyone has. That's the reason we ask for ALL the code to begin with. OK?

When you open the recordset try moving to the last record and then the first record BEFORE the Do Until rs.EOF statement. So:
Code:
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF

That gives ACCESS the recordset number apparently. I heard you needed to do this many years ago. I don't know if that's the problem, but you can try it.
 
Last edited:

Mike Krailo

Active member
Local time
Yesterday, 19:18
Joined
Mar 28, 2020
Messages
625
Never assume anything. That could mean there is a timing issue. What everyone is wondering is how complicated your SQL code is. If you have demo of the problem code, some of us can look at it and see what is going on. Those types of problems can be very frustrating, but if it works when you step through and pukes when you run it, it could be something else that is only visible if you set up a watch to monitor the parameters.

Your original code plus some debugging lines to help and the needed dim statements to make it run. You of course can change to suit what you would like to examine. I wonder if it is even going in the loop at all. I added a debug.assert line to at least go through ten records before it will stop. The code runs and works fine with any query or table I run it with. That suggests that your query has some strange bug in it. It would have been easier if you had posted it as requested. Edit: What Larry said about MoveLast and MoveFirst is good to try as well.
Code:
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strSQL As String
   Dim Cnt As Long
   Dim strElig As String  '--Added this because it has to be defined
   Cnt = 0

   Set db = CurrentDb
   Set rs = db.OpenRecordset("strSQL")

   Debug.Print strSQL

   strElig = rs("Elig")  '-- This works!

   Debug.Print "RecordCount: " & rs.RecordCount
   Debug.Print "OutOfLoop: " & rs.EOF

   If Not rs.EOF Then rs.MoveFirst
   Do Until rs.EOF  '-- error occurs here! Grrrr!
      Cnt = Cnt + 1
      Debug.Print "InLoop: EOF is " & rs.EOF & " AND Record: " & Cnt
      Debug.Assert Cnt < 10
      ' Your Code Here
      Debug.Print rs!Term
      rs.MoveNext
   Loop
   rs.Close
   Set rs = Nothing
 

moke123

AWF VIP
Local time
Yesterday, 19:18
Joined
Jan 11, 2013
Messages
2,907
Code:
strElig = rs("Elig")  -- This works!

    Do Until rs.EOF  -- error occurs here! Grrrr!

        rs.movenext

Even though the Do Until line is being highlighted it may not be where the error lies. You omitted the next lines of code so your error may be there.

Is strElig used within the loop?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:18
Joined
May 21, 2018
Messages
6,414
Based on your posted code stub we do not know if it is failing on the first record, the second or the last. My guess it is not the first record, but enters the do loop and fails on the second iteration. For some reason within the loop (for code you refuse to post) the recordset goes out of scope. I am guessing it is the second pass of the loop. Again, with only a code stub we can only guess.
Code:
    Do Until rs.EOF  -- error occurs here! Grrrr!  Probably on the second pass of the loop since it is in scope initially
                rs.movenext
    loop
 

NauticalGent

CopyPaster of the First Order
Local time
Yesterday, 19:18
Joined
Apr 27, 2015
Messages
5,117
Again, maybe I am dense, but your recordset as written is a ZLS. Am I missing something?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:18
Joined
Feb 28, 2001
Messages
23,115
Joel, you are aggravating some of the responders. You want to get us to move on? Show us the SQL for strSQL. In the code snippet, you showed us NOTHING about its origins. If that was a copy/paste en bloc, then strSQL must be an externally declared variable, which has implications for the scope of things that may be in the query.

You want help? Think of this like going to a doctor's office. So you come in with symptoms and a rash. The doc looks at it, suggests maybe it is some type of contact dermatitis. But that doesn't pan out. You know what happens next? The doc asks for more tests. Why? Because s/he doesn't have all the information needed to resolve the problem. So more data is necessary. Well, we find OURSELVES in the same situation. From what we see, we don't have enough information to solve the problem. SOMETHING is wrong. The error message tells us that much. But you deny any of the things we suggest. So... we have to order up more tests.

Don't get frustrated with us. Just give us more data regarding this problem.
 

Users who are viewing this thread

Top Bottom