Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

Operation is not allowed when the object is open...VB 6

First run works fine,  The second run produces an error (3705).

    .
    .    
    rs.activeconnection = cnConn
    rs.locktype = 3                                <--- Runtime error 3705: Operation is not allowed
    rs.cursortype = 1                                                                 'when the object is open
    rs.cursorlocation = 3
    rs.Open strSQL2
    If rs.RecordCount > 0 Then
    .

----------
When it is called via:
.    
FolderIDVar = fnGetIDVal("FolderID", "tblFolder", "ItemStr", cboFolder)      '<-- this line runs fine
FileIDVar = fnGetIDValLong("FileID", "tblFile", "Folder_Id", FolderIDVar)      '<-- this line, when running fnGetIDValLong has error
.
.


Note: I open stablish the connection (cnConn) to the backend in form load event and close it/ set to nothing up on closing the form.  

Thanks,

Mike
Avatar of SRigney
SRigney
Flag of United States of America image

You can only set some of the properties of a recordset when it is closed.  Since you are about to re-open the recordset you should manually close it first.

rs.activeconnection = cnConn
   ' Close the recordset if it is not already closed.
   If Not rs.State = adStateClosed Then
      rs.Close
   End If
    rs.locktype = 3                                
    rs.cursortype = 1
    rs.cursorlocation = 3
    rs.Open strSQL2
    If rs.RecordCount > 0 Then
Avatar of alros
alros

You are try to assign properties to the recordset which is open. first closed it and then assign properties and then open the recordset.

If rs.state = adstateopen then
   rs.close
end if
rs.locktype = 3
rs.cursortype=1
rs.cursorlocation=3
rs.Open sqlQuery, cnConn
If rs.RecordCount > 0 Then
...
ASKER CERTIFIED SOLUTION
Avatar of Shauli
Shauli

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...Note that if your sql statement DOES NOT contain the WHERE, then you'll have to loop through the records, and then your code would look like:...
I meant if your sql statement should bring more than one result :)

S
Avatar of Mike Eghtebas

ASKER

Thank you for the replies,

SqlStr="Select * MyTable Where ID=" & fnIdVar(cboSomething)

Both "SqlStr" and "fnIdVar(...)" was using the same connection at the same time.  Your description helped me see that.

Note: I am opening one connection to the backend in Form Load event and set it to nothing at Query Unload.

Mike
That is the way to go, Mike. You open it upon form load, and close at QueryUnload. Glag it works for you and thanks for the points :)

S
Btw, this is my first VB project ever.  It is amazing how this collective conscoius (EE) heps.  You are very helpfull.

I have been doing Access for over 10 years.  When I just started it, it was very very difficult to get some answers.

Thanks

Mike