Hi All,
i'm running a vba application using Ms Access.
in my database,Employee table, i'm having department ID same for 3 employees
i need to get that id in an array
i gave the code as

value = recordset.Fields(0).value
'value will record the count of Deptid coulmn whic have the same ID
    Dim i As Integer
    Dim data(4) As Integer
    recordset.Close
    Set recordset = New ADODB.Recordset

    For i = 1 To value

        sql_check = "select Deptid from Employee where deptName = '" & Name & "'"
        recordset.Open sql_check, connection, adOpenDynamic, adLockOptimistic
        connection.Execute sql_check
        data(i) = recordset.Fields(0).value
    Next i

but i' getting a runtime error that operation not allowed when the object is open
Please help...
regards
Rachel

Hi Rachel,
Looks like you are confusing a "Connection" with a "Command".
There are 2 ways to open a Recordset :-
Directly - as you have with the line "recordset.Open sql_check, connection, adOpenDynamic, adLockOptimistic"

Or to run a "Command" which is what you are doing with the line - "connection.Execute sql_check"
Except that you have provided a "Connection" object in that line - not a "Command" Object.

Delete that second line "connection.Execute sql_check" and it should work for you.

Just a point on the naming of variables - "Value" is a Property of many objects so using this could cause confusion in your code. Try renaming it in a way that avoids this and also gives a clue to you when you read the code later as to what Type of Value you are expecting to get - e.g. If you expect a "Long" Number type then name it "lngValue".
Also, "Connection" is a an ADODB Object type so call that "cnn" and "Recordset" call "rst" followed by what it contains e.g. "rstDeptIDs"
These are just tips - but it is a good idea to follow a good "naming convention " - type that into VBA Help or on the MSDN site and you should find some more advice.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.