Run-time error 3265

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Using ADO against an Access 2002 database.
I am reading fine from table Config, then I reset my sqlstr/rs to read from tasks table and when I try to read from table engineer (or any other table in the db) I get this error.

It occurs on the adoRS.Open if that is relavent (or not obvious)

Any clues?
LVL 67
sirbountyAsked:
Who is Participating?
 
JR2003Commented:
I'm assuming you have a comboBox with a control array index of 2
I have changed the code to:
 set adoRS to a new Recordset and use the .Value of the field. I've also added & "" to the end of the value so it won't fall over if the column has a Null value. I've also added some error trapping to give you more info and put a resume button on the error message so you can press ctrl+Break and resume to see the exact line it falls over on.  - all good tips-

    On Error Goto Trap
   
    Dim adoRS  as ADODB.Recordset
    strSQL = "SELECT * FROM Admin"
    set adoRS = New ADODB.Recordset
    With adoRS
        .Open strSQL, adoConn, adOpenStatic, adLockPessimistic
        While not .EOF
            cboInfo(2).AddItem .Fields(0).Value & ""
            .MoveNext
        Wend
        .Close
    End With
    Set adoRS = Nothing

    GoTo SkipTrap
   
Trap:
   
    Dim iResult As VbMsgBoxResult
    Screen.MousePointer = vbDefault
   
    With Err
        iResult = MsgBox("Error: " & .Number & vbNewLine & _
                "Description " & .Description & vbNewLine & _
                "Source " & .Source, vbRetryCancel Or vbExclamation Or vbDefaultButton2)
        If iResult = vbRetry Then
            Resume
        End If
    End With

SkipTrap:
   
End Sub
0
 
sirbountyAuthor Commented:
I can debug and set it back to tasks and it still works - but if I set it back to config it doesn't (I've read from this one before)
0
 
bkthompson2112Commented:
I usually get this error when I'm trying to get data from a field and have misspelled the field name, or didn't include it in the query.

You may need to post some code and table definitions.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
sirbountyAuthor Commented:
I've got tables Tasks, Location, Config, Admin
All I'm doing in the code is opening a new recordset:

 strSQL = "SELECT * FROM Admin"
 adoRS.Source = strSQL
 adoRS.Open
 Do While Not adoRS.EOF
   cboInfo(2).AddItem adoRS.Fields(0)
 Loop

I've triple-checked the spelling.  At first I thought it was because one of my tables that was failing was blank, but that's not it - and I'd think the eof should be true in that case anyway...

I've even tried renaming the admin table - still no luck... : (
0
 
bkthompson2112Commented:
Is cboInfo a control array?

What happens when you set a break point on the cboInfo(2).AddItem adoRS.Fields(0) line and watch adoRS.Fields(0)?
0
 
mladenoviczCommented:
I think that you can not get this error on asoRS.Open. It is Field name problem, or cboInfo control array. Try to debug and go line by line (f8) in oder to locate error
0
 
leonstrykerCommented:
Try this:

strSQL = "SELECT * FROM Admin"
adoRS.Open strSQL, adoConn, adOpenStatic, adLockPessimistic
For intCount = 0 to adoRS.RecordCount -1
   cboInfo(2).AddItem adoRS.Fields(0)
   If Not adoRS.EOF Then adoRS.MoveNext
Next

Leon
0
 
sirbountyAuthor Commented:
>>Is cboInfo a control array?<<    "Yes"

>>What happens when you set a break point on the cboInfo(2).AddItem adoRS.Fields(0) line and watch adoRS.Fields(0)?<<

Doesn't make it that far - fails with the error on the .open

>>I think that you can not get this error on asoRS.Open. It is Field name problem, or cboInfo control array. Try to debug and go line by line (f8) in oder to locate error<<

Yes - the error is occuring on the rs open.  I am already using debug and finding that if I readjust my sqlstr to state to look at another table, it works fine (only with the tasks table apparently).

leonstryker & JR2003 - trying it now...
0
 
sirbountyAuthor Commented:
leonstryker
>>adoRS.Open strSQL, adoConn, adOpenStatic, adLockPessimistic<< generates the same error

JR2003
>>Dim adoRS  As ADODB.Recordset<< already declaring this publicly
But setting it as a new recordset seems to work.

Now, can you explain why this is?

In the order of processing, I do the following:

1) Inside formload, I call a function to open the database which sets up the filename, connection, cursortype, cursorlocation and then opens the connection (I can post this if necessary)

2) Then, I run a sub that sets the sqlstr to read config table and pull out some preliminary info.
    Dim strSQL As String, strData() As String, X As Integer
    strSQL = "SELECT * FROM Config"
    adoRS.Source = strSQL
    adoRS.Open
    'processing here
    adoRS.Close

3) I run another sub that pulls in my taskcategory data from my Tasks table

 Dim strSQL As String
 strSQL = "SELECT DISTINCT TaskCategory FROM Tasks"
 adoRS.Source = strSQL
 adoRS.Open
 Do While Not adoRS.EOF
    cboInfo(0).AddItem adoRS.Fields(0)
    adoRS.MoveNext
 Loop
 adoRS.Close

4) The remaining database calls are based upon what the user chooses.  If he chooses one option, I need to pull info from the admin table, or location table, etc - but this is where it fails...

I'm just not sure why I have to create a new recordset to get it to work the third time around (and then why does it still work if I reset my sqlstr back to the tasks table?)
0
 
sirbountyAuthor Commented:
Hmm - should I be setting adoRS to nothing at the end of each query to the db?
I hadn't been doing that but simply adoRS.close (although it still confuses me why i was able to do it three times without incident)
0
 
leonstrykerCommented:
>>adoRS.Open strSQL, adoConn, adOpenStatic, adLockPessimistic<< generates the same error

What is the exact error text of the message on this line?

Try assing Set adoRS = Nothing after adoRS.Close and Set adoRS = New ADODB.Recordset before using it again.  I have a feeling it never gets cleared in your code.

Leon
0
 
leonstrykerCommented:
I guess you are thinking along the same lines.

:)
0
 
JR2003Commented:
If you set it to nothing then you will definitely have to set it to a 'new' one afterwards.
Or it will just be an empty variable - the equivalent of a pointer pointing to nothing in C.

Set adoRS = Nothing
Set adoRS = New ADODB.Recordset

Is the same as
Set adoRS = New ADODB.Recordset

So although you it's good practice to set objects to nothing as you go to clean up after yourself.


Is it possible the error is caused by the previous sql not Closing the recordset after is has used it?

I ALWAYS 'Close' and then 'set to Nothing' my recordsets when I have finished with them.

Here's a copy of the general template I use for processing a recordset:

    Dim MySet As ADODB.Recordset
    Dim sSql As String
   
    sSql = "select FIELD1, FIELD2 " & vbNewLine & _
            "from TABLE1"
   
    Set MySet = CreateRecordset(sSql)
    With MySet
        While Not .EOF
            'Add record processing code here
            .MoveNext
        Wend
        .Close
    End With
    Set MySet = Nothing


0
 
sirbountyAuthor Commented:
>>Is it possible the error is caused by the previous sql not Closing the recordset after is has used it?<<

Not really sure there.  The first rs pulls all tables, the second pulls one column (distinct).  The third - in my cboInfo_click is within a select case statement - if case 0 then 'select all from tasks' - if case 1 'select  * from admin'.
Everything is set up within it's own case statement (nothing preceding them that would cause this).

In closing, what's best - should I alter my code to close and then set to nothing or simply use a new rs each time (or both).
Basically, the form load is going to open the db and I won't close it until form_unload...

Thanx!
0
 
JR2003Commented:
I close and set to nothing when I have finished with a rs and always start with setting it to new.
It acts as a self-documenting marker in the code so I know I have started and finished with it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.