Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Run-time error 3265

Posted on 2004-08-17
15
Medium Priority
?
21,511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 67

Author Comment

by:sirbounty
ID: 11823246
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
ID: 11823334
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
ID: 11823402
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Expert Comment

by:bkthompson2112
ID: 11823524
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
ID: 11823727
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
ID: 11824084
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 1000 total points
ID: 11825437
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
 
LVL 67

Author Comment

by:sirbounty
ID: 11829922
>>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
ID: 11830269
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
ID: 11830545
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
ID: 11830578
>>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
ID: 11830586
I guess you are thinking along the same lines.

:)
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 1000 total points
ID: 11830726
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
ID: 11831721
>>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
ID: 11832299
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

636 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