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

[WannaKatana]

This is such a simple basic thing I've done for decades yet now a problem? I'm sure it's something silly I'm missing. A search provides no answer for my situation.
Library reference is to Microsoft Office 16.0 Access database engine object library. I removed that and changed it to DAO 3.6 library. Both compiled. Both gave me the same error.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
   
    strElig = rs("Elig")  -- This works!
   
    Do Until rs.EOF  -- error occurs here! Grrrr!
   
        rs.movenext
    loop

Thanks!

Joel

 

[MajP]

Is that really your code or just a snap shot? What is the real strSql since you do not show that?

 

[MajP]

If this is in fact real code then you need to put 'Option Explicit' at the top of all modules and declare all variables. If not you are just wasting your time and everyone else's.

 

[The_Doc_Man]

I would put a breakpoint on that "strElig =" line and then check for the states of rs.EOF and rs.BOF before you enter the loop.

Does the "strElig=" line actually return a value? Or is it null?

 

[WannaKatana]

Yes it passes that line and the value is assigned.
EOF is false before that value is accessed and assigned to the variable.
After the value is assigned, the variable has the correct value "BS".
Only after it assigns this value do I get the error at "Do Until rs.eof".
I get a record count from the RS object before the error.
I can movelast, movefirst.

I've never seen anything like this. I decompiled just in case. Repaired and compacted.

I'm about ready to convert it to an ADO recordset just for fun.

Thanks!

Joel

 

[The_Doc_Man]

I was checking for some obvious simple cases, but none of them seem to apply.

You say you get the error that tells you that some object is no longer defined (probably RS), so you are using the Access "default" handler. I.e. you are getting the generic style of error notification.

If you single-step with the Locals Window open, you would be able to see the exact step at which RS changes from something to Nothing. (Literally.)

You question why we would need to see the SQL. Seeing it would allow us to rule out issues with sub-queries, looking for incorrect SQL Aggregation, etc. Since you are providing a query to the OpenRecordset call but no type, the default is a Dynaset. Since a Dynaset can go either forwards or backwards, hitting .EOF shouldn't destroy the object.

You said originally, "This is such a simple basic thing I've done for decades yet now a problem?" Are you just saying you've done it many times before in other circumstances, OR are you saying that this code has worked for decades but suddenly has started failing?

One last thing, perhaps only useful to rule out something: If you change

strElig = rs("Elig")

to

strElig = rs![Elig]

does it make a difference? Yes, I know it shouldn't... but does it?

 

[NauticalGent]

Maybe I am missing something, but it appears you are setting your recordset to something (strSQL) that has not been defined yet?

 

[arnelgp]

you probably need to "test" if first before doing anything:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
        strElig = rs("Elig")  -- This works!
        
    End If
  
    Do Until rs.EOF  -- error occurs here! Grrrr!
  
        rs.movenext
    loop

 

[Mike Krailo]

@NauticalGent ,it's missing because the OP didn't feel it was relevant. See post #5

 

[MajP]

I did. Relax cowboy

Look Sweetheart. My point is you need to show real code, if you want people to spend their time guessing at your problem.
You have undeclared variables for StrSql and strElig. You say it is working, but I doubt it. So either this is a snapshot of code or you have some global variable somewhere. But chasing problems with a partial example is a waste of time. So show the real code.

 

[NauticalGent]

"Cowboy", "Sweetheart"....I wanna be special too! Who can I be!?!?

 

[CJ_London]

how about pooh bear?

 

[NauticalGent]

how about pooh bear?

I'll take it!

 

[moke123]

I wanna be special too!

ok, Special Ed

 

[NauticalGent]

That's my brother...

 

[LarryE]

This is such a simple basic thing I've done for decades yet now a problem? I'm sure it's something silly I'm missing. A search provides no answer for my situation.
Library reference is to Microsoft Office 16.0 Access database engine object library. I removed that and changed it to DAO 3.6 library. Both compiled. Both gave me the same error.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
 
    strElig = rs("Elig")  -- This works!
 
    Do Until rs.EOF  -- error occurs here! Grrrr!
 
        rs.movenext
    loop

Thanks!

Joel

1. This is not a Library problem so re-reference the 16.0 library
2. As others have said, you have not defined any Recordsets yet, so you need to define strSQL if you are going to open it. ACCESS does not know what strSQL refers to, so ACCESS cannot open it as a recordset.
3. I don't know what you think is working, but this code is not opening a recordset and moving through it.

Try:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL="Put your SQL here;"
Set rs = db.OpenRecordset(strSQL)
.MoveFirst
Do Until rs.EOF
    MsgBox rs.Fields("Elig")
    rs.MoveNext
Loop
rs.Close

This should loop through your defined strSQL recordset and show each value of "Elig" in a Message Box and then close the recordset.

 

[Mike Krailo]

Since the recordset opens and is accessible, why would the SQL be relevant? I am able to retrieve the value of the field referenced and only then does the error occur. Like as soon as I access it, it closes.

See what kind of confusion you have caused. I had to repost your comment to remind everyone what you said.

 

[WannaKatana]

Look Sweetheart. My point is you need to show real code, if you want people to spend their time guessing at your problem.
You have undeclared variables for StrSql and strElig. You say it is working, but I doubt it. So either this is a snapshot of code or you have some global variable somewhere. But chasing problems with a partial example is a waste of time. So show the real code.

I already said it's working. The easy variables are assumed. Dim strSQL, ok there ya go.

It opens the recordset, gets the value of the first record's data (one field), then tries to loop. Only after the field value is retrieved does it fail, at the start of the loop. The recordset had EOF = false and the first record was accessible. So one should see that the recordset was instantiated correctly or how could I get the value of the first record?

 

[WannaKatana]

1. This is not a Library problem so re-reference the 16.0 library
2. As others have said, you have not defined any Recordsets yet, so you need to define strSQL if you are going to open it. ACCESS does not know what strSQL refers to, so ACCESS cannot open it as a recordset.
3. I don't know what you think is working, but this code is not opening a recordset and moving through it.

Try:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL="Put your SQL here;"
Set rs = db.OpenRecordset(strSQL)
.MoveFirst
Do Until rs.EOF
    MsgBox rs.Fields("Elig")
    rs.MoveNext
Loop
rs.Close

This should loop through your defined strSQL recordset and show each value of "Elig" in a Message Box and then close the recordset.

The recordset was successfully opened. I retrieved the value of a field from the first record (for group break purposes later). Only when it hits the "Do Until rs.eof" does it fail. If you look at my original post, you see where I noted success and where I noted failure.

 

[WannaKatana]

See what kind of confusion you have caused. I had to repost your comment to remind everyone what you said.

I'm sorry. I was just trying to only put the relevant code. I thought the obvious things would be assumed like dim'ing and setting the variable strSQL. Option Explicit. My apologies. I thought that was enough to see the issue.

I opened the recordset. I grabbed a field value from the first record. EOF was false. Record count was 48 or so. Everything was fine. Only when I hit the "Do Until rs.eof" did the rs object "disappear".

I've been doing this for many years, used that exact same code structure many, many times. I just don't get it. There is no reason that I can fathom that the object would suddenly become invalid. If I step through the code, it does work sometimes, I get past the "Do Until" but if I let it fly, it errors or if I try to get to "Do Until" a 2nd time while stepping through the code it again errors out.