472,950 Members | 1,924 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,950 software developers and data experts.

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
Apr 26 '22 #1
4 23670
5,501 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. 
Apr 26 '22 #2
32,546 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.
Apr 26 '22 #3
4 Nibble
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.
Oct 28 '22 #4
32,546 Expert Mod 16PB
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.
Oct 29 '22 #5

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

Similar topics

by: deko | last post by:
I'm getting intermittent "Object Invalid or No Longer Set" errors in my Access 2002 mdb. What causes these errors? Has anyone dealt with this before? I can't trace it because it's not easy...
by: deko | last post by:
RE: "Object Invalid or No Longer Set" errors I've had an angelic visitation and the revelation is that my problem, in essence, is due to the fact that I have multiple forms open at the same time...
by: Paul | last post by:
I have an Access 2000 database with a form that is giving me some major headaches. When you open the form, it displays all records and allows editing, but has AllowAdditions set to False so that...
by: Owen Jenkins | last post by:
I have found references to this problem in several threads on this newsgroup but none provide an answer to explain this problem. So any further assistance would be very helpful... I have 2 forms...
by: (Pete Cresswell) | last post by:
We were testing a version of our app that's been running for months with no problems and it started throwing "Object no longer exists" messages on two machines in the test environment. We tried...
by: TSanders | last post by:
Hello everyone, I hope someone here can help. I created an application in MSAccess 2000 using VBA for a local telco to track their DSL orders. The database is split into a front end and a back...
by: Jozef | last post by:
HELP! Hi Folks, I'm having trouble referencing these public variables, and I'm not sure why. I use the code below to set open a connection to a database. The variables are public variables...
by: 4bex | last post by:
Hello there, I am trying to get to the bottom of an 'Object invalid or no longer set' issue, but I am not a database programmer. We have approx 20 users on MS Access 2002 front...
by: FelixSmith | last post by:
I have recently posted a new access database and its oracle tables onto a shared folder for various users. It is a search database and me and a few other users are able to pull from the file and use...
by: neelsfer | last post by:
I regularly get this error ("Object Invalid or No Longer Set") in the following code On Error GoTo Err_Form_AfterUpdate Dim MyDB As DAO.Database Dim rst As DAO.Recordset Dim strSql As...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.