470,632 Members | 1,511 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

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

2 Bit
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.

Expand|Select|Wrap|Line Numbers
  1.     Dim db As DAO.Database
  2.     Dim rs As DAO.Recordset
  4.     Set db = CurrentDb
  5.     Set rs = db.OpenRecordset(strSQL)
  7.     strElig = rs("Elig")  -- This works!
  9.     Do Until rs.EOF  -- error occurs here! Grrrr!
  11.         rs.movenext
  12.     loop
4 Weeks Ago #1
2 11087
5,444 Expert Mod 4TB
>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
Expand|Select|Wrap|Line Numbers
  1. AirCode
  2.  '//Even if you are not on the first record, the recordcount will return at least 1 if there are any records within the recordset
  3. if myRS.RecordCount>=1 then
  4.   ' //therefor if you have at least one record then the actionable code can be executed
  5.   '//I usually ensure that I am on the first record of the recordset by explicitly setting the pointer
  6.   myRs.MoveFirst
  7.  '//from here insert your loops...
  8. Else
  9.    ' //message to user no records found
  10. end if
  11.  '// release the memory
  12.  if not myRS is nothing then
  13.   myRS.close
  14.   set myRS = Nothing
  15. end if
  16. DO NOT CLOSE THE myDB - only set it to nothing
  17. - closing the Database can cause you errors throughout your project!
  18. if not myDB is nothing then set myDB = Nothing. 
4 Weeks Ago #2
32,301 Expert Mod 16PB
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.
4 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Owen Jenkins | last post: by
2 posts views Thread by TSanders | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.