Run-time error 3265

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)

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.

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... : (

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)?

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

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

>>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...

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?)

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)

>>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

I guess you are thinking along the same lines.

:)

>>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!

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.