Recordset not longer a valid object (Error 3420) 1

[ViAn]

I have a list box that is linked to a specified query in the QueryDefs of my CurrentDB. This list shows always the correct items. Further I use the function below to extract the information from the list. This always succeeds the first time. But after having manipulated the query that the list is based on (the list is showing the right result), I often get error 3420: "Object no longer set" or error 3219: "invalid operation". Any ideas?

YS. ViAn

See the code below:

Private Function ID_string() As String
'Making a string on format: "ID IN (2, 4, 5) "
    Dim rst As DAO.Recordset
    Dim nrOfRecords As Long
    Dim idx As Long
    Dim dbs As Database
    Dim tmpString As String
    
    Set dbs = CurrentDb
    Set rst = Forms!myForm!myList.Recordset
    
    rst.MoveFirst ''' OOPS! Here error 3420 occurs
    tmpString = "ID IN ("
    
    nrOfRecords = rst.RecordCount ''' OOPS! Here error 3219 occurs
    For ii = 0 To (nrOfRecords - 1)
        tmpString = tmpString & rst.Fields("ID").Value
        If ii < (nrOfRecords - 1) Then
            tmpString = tmpString & &quot;, &quot;
            rst.MoveNext
        End If
    Next ii
    tmpString = tmpString & &quot;)&quot;
    ID_string = tmpString
End Function

 

[scriverb]

Make this change first of all:
Dim dbs As DAO.Database

Now without knowing which line the code stops on(yellow line in code window) I would suggest that you perform the following at the end of the Function:

rst.CLOSE
dbs.CLOSE
End Function[

Post the line of code that is causing the error and we can take a closer look at the problem.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

I am sorry I can see in your post where you identified the line. I will now take a look at it.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

I think you should change this line to set the recordset to the query that is populating the listbox.

Set rst = dbs.OpenRecordset(Me!myList.RowSource, dbOpenDynaset)

When you modify the RowSource query for the listbox each time then this Set command will be set with the records identified in the query used by the lst Box.

Let me know if this helps out.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ViAn]

Thank you for your tip!
Now it seems like the error has gone away, BUT there is one big problem! Only the first record is found, that says the "numberOfRecords"-variable always get the value "1", while the query and the list shows 2 records or more.

(Since my code is in a separate module I had to write "Forms!myList.RowSource". I also tried rst = tmpQuery.OpenRecordset(dbOpenDynaset) with the same result ).

I hope that the error is now quite obvious, even though it doesn't seem obvious to me

YS
ViAn

PS Do you know about one place on the web where I can find something like "The complete reference on DAO.recordset?"

 

[scriverb]

When you run the query that is in the RowSource of the lstBox how many records are showing? Also, your are missing a dimension statement for ii.
Dim ii as integer

If this doesn't fix it then I would suggest putting in a stop just prior to this command.
nrOfRecords = rst.RecordCount
Then using your F8 key step through the commands. Examine the values of each after the line executes one at a time. Hold your cursor over the nrOfRecords to see how many records the code think are in the recordset. See how many times the loop goes through the recordset.

This is how I would do this. Just analyze each value to see if there is something that you are not expecting.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ViAn]

Hello!
Now it seems like everything is working. I just added the line:
"rst.MoveLast" before "nrOfRecords = rst.RecordCount" and put the MoveFirst-statement after these statements.

Thanks for all help. A star to you!

-ViAn-

 

[scriverb]

That did it I am sure. I should have spotted that. You must be on the last record to get the recordcount in the recordset. Thanks for the star.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???