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

[WannaKatana2]

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

[zmbd]

>Post the exact error number and error message - usually generic but I've seen a ton of them over the years and they will help us narrow down what has happened.

Are you sure the SQL in line 5 is returning at least one record?
Insert a Debug.print strSQL just before line 5 to capture the resolved SQL string in the immediate window ([Ctrl][G]), select the resolved string and copy ([Ctrl][C]) to the clipboard
Open a new query, switch to SQL view and paste the captured string into the query - run it. We want to make sure that nothing has damaged the string before use.

Normally I will wrap loops like yours in a simple check

AirCode
 '//Even if you are not on the first record, the recordcount will return at least 1 if there are any records within the recordset
if myRS.RecordCount>=1 then
  ' //therefor if you have at least one record then the actionable code can be executed
  '//I usually ensure that I am on the first record of the recordset by explicitly setting the pointer
  myRs.MoveFirst
 '//from here insert your loops...
Else
   ' //message to user no records found
end if
 '// release the memory
 if not myRS is nothing then
  myRS.close
  set myRS = Nothing
end if
 DO NOT CLOSE THE myDB - only set it to nothing
 - closing the Database can cause you errors throughout your project!
if not myDB is nothing then set myDB = Nothing. 

[NeoPa]

As Zmbd says, please try to include the basics of the question so we have something to work with. I get so frustrated by questions claiming something doesn't work without indicating what was expected (and why) and what was actually perceived when it was run.

It's clear you have tried to include helpful information so I can see this is simple inexperience, but do make a note for future requests.

Avoid relying on information (such as the contents of strSQL) that isn't shared in the question. Always include relevant error messages. Numbers are fine too, but always as well as & never instead of. When using code only EVER use code that has been copied & pasted from your project. I see rs.movenext on line #11 & immediately recognise this is either not from your actual code or you have created something else, within that project, called movenext that includes no capitals.

As I say, it's clear you're inexperienced in using forums, so just see this as helpful advice from an old-timer :-)

Otherwise, quite well explained and all we really need now is the stuff I explained above. I have to say, nothing jumps out immediately. If line #7 works, as you say, then it would appear that rs is set up appropriately. You don't explain what 'works' means in this context, which is important because not crashing and assigning a visible value could both be meant, and they have vastly different implications.

In spite of that, not crashing would imply a recordset were correctly set up - even if it might be an empty one. As such I would certainly expect line #9 to compile and run - unless you interpret the failure to proceed to line #11 as an error of course. Your explanation isn't exactly clear so we will probably have to wait for your full information and hope something shows that may explain your situation.

I can say that, assuming our assumptions of what you mean are accurate, I would not expect to see errors with the code shown.

[jameswalter]

Sure looks like an Access VBA exception, though I suppose it might be some DAO error. Those are poorly documented, DAO being incredibly obsolete. I doubt it is any kind of normal collection access exception. It has the smell of a cursor positioning error, but I'd expect it to fail for every field.

[NeoPa]

Hi James.

While saying very little more than that the VBA problem described may be a VBA issue, you manage to make a misleading (wrong) statement about DAO (Data Access Objects) being "incredibly obsolete". While this was mooted many versions ago it is not, nor ever has been, deprecated (or made obsolete). It is actually the basis upon which all data communications are managed from within Access objects (Tables, Queries, Forms, Reports etc). ADODB can be used from VBA code, and may even be possible to set Form & Report RecordSources to using code, but it is in no way a default in Access.

I can easily be convinced otherwise, but my suspicion is that you may be trolling for effect. Disabuse me.