Solved

Run-time error 3265

Posted on 2004-08-17
15
21,464 Views
Last Modified: 2013-12-25
"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?
0
Comment
Question by:sirbounty
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 67

Author Comment

by:sirbounty
Comment Utility
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
 
LVL 6

Expert Comment

by:bkthompson2112
Comment Utility
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
 
LVL 67

Author Comment

by:sirbounty
Comment Utility
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
 
LVL 6

Expert Comment

by:bkthompson2112
Comment Utility
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
 
LVL 8

Expert Comment

by:mladenovicz
Comment Utility
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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
 
LVL 18

Accepted Solution

by:
JR2003 earned 250 total points
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 67

Author Comment

by:sirbounty
Comment Utility
>>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
 
LVL 67

Author Comment

by:sirbounty
Comment Utility
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
 
LVL 67

Author Comment

by:sirbounty
Comment Utility
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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
>>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
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
I guess you are thinking along the same lines.

:)
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 250 total points
Comment Utility
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
 
LVL 67

Author Comment

by:sirbounty
Comment Utility
>>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
 
LVL 18

Expert Comment

by:JR2003
Comment Utility
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now