RUN TIME ERROR: 3705 VB+Ms-Access

[ankush.mukherje]

Private Sub cmdgetdata_Click()
  

    For i = 0 To lstsoftware.ListCount - 1
      If lstsoftware.Selected(i) Then
         sSQL = "select * from Master_final"
        With adorsRAM
      .ActiveConnection = cn
      '.CursorLocation = adUseClient
      .CursorType = adOpenDynamic
      .LockType = adLockOptimistic
      .Open sSQL
      End With
      If Not adorsRAM.EOF Then
      Set msh.DataSource = adorsRAM
      msh.Refresh
      Else
      MsgBox "No Records Found in DataGrid!"
      msh.Clear
      End If
       End If
        Next i
           
End Sub

PLEASE CHECK THIS CODE AND TELL ME THE ERRORR IN THE CODE
I AM GETTING THE ERROR:
OPERATION IS NOT ALLOWED WHEN OBJECT IS OPEN
PLEASE REPLY ASAP IT IS URGENT

[QVeen72]

Hi,

With the above code, what you get is : Grid is populated with data for the last selected list item.. dont put in a loop, just build sql string with where condition for the selected listitems and refresh the grid once.. something like this :

If lstsoftware.SelCount = 0 Then 
    MsgBox "No Softwares Selected"
    Exit Sub
End If
'
Dim MyStr As String
MyStr = ""
For i = 0 To lstsoftware.ListCount - 1      
If lstsoftware.Selected(i) Then         
    MyStr = MyStr & ", '" & lstSoftware.List(i) & "' "
End If
MyStr = Mid(MyStr, 2)
Mystr = "(" & MyStr  & ") 
sSQL = "select * from Master_final Where MyFieldName In " & MyStr
With adorsRAM      
    .ActiveConnection = cn      
    .CursorType = adOpenDynamic      
    .LockType = adLockOptimistic      
    .Open sSQL      
End With      
If Not adorsRAM.EOF Then      
    Set msh.DataSource = adorsRAM      
    msh.Refresh      
Else      
    MsgBox "No Records Found in DataGrid!"      
    msh.Clear      
End If

Change MyFieldName Accordingly..

Regards
Veena

[ankush.mukherje]

thanks a lot for your help
tell me one thing can I use

select distinct(MyStr) from Master_final

if not what should I use then?
please reply ASAP
thanks again in advance

[ankush.mukherje]

Hey guys check this out:

Private Sub cmdgetdata_Click()
Dim Criteria As String, Cnt As Long
Criteria = ""
    For Cnt = 0 To lstwindows.ListCount - 1
        If lstwindows.Selected(Cnt) Then
            If Len(Criteria) > 0 Then Criteria = Criteria & ","
            Criteria = Criteria & "'" & lstwindows.List(Cnt) & "'"
        End If
    Next
    
    If Len(Criteria) = 0 Then
        MsgBox "You must select at least one item.", vbInformation, "Error"
        Exit Sub
    End If
    sSQL = "SELECT * FROM Master_final WHERE Windows IN (" & Criteria & ")"
    sSQL = sSQL + "or Office IN (" & Criteria & ")"
    sSQL = sSQL + "OR AUTOCAD IN (" & Criteria & ")"
    sSQL = sSQL + "OR PDF_Writer IN (" & Criteria & ")"
    sSQL = sSQL + "OR Primavera IN (" & Criteria & ")"
    sSQL = sSQL + "OR Etap IN (" & Criteria & ")"
    sSQL = sSQL + "OR Tally IN (" & Criteria & ")"
    sSQL = sSQL + "OR Caesar IN (" & Criteria & ")"
    sSQL = sSQL + "OR Staad IN (" & Criteria & ") "
    Set rs = cn.Execute(sSQL)
    
    If Not rs.EOF Then
    Set msh.DataSource = rs
msh.Refresh
Else
MsgBox "No Records Found in DataGrid!"
msh.Clear
End If
   
    rs.Close
    cn.Close
    
End Sub

This code is working perfectly all right.
Now my next question is if after selecting an item and checking its output i again want to select another item and check its out put then an error is produced
please help me to solve this problem
Thanks in advance

[ankush.mukherje]

Thanks a lot to all of you
The code is running perfectly ok after I removed the following lines

rs.Close
cn.Close

thumbs up to all of you

[sidnei]

It's NOT a good practice to create variables inplicit...

Consider using

Option Explicit

in ALL your forms, and you avoid getting errors like this one.

Notice that lines

rs.Close
cn.Close

respectively closes the dataset and the connection.
But seems the connection have opened in other sub or function. So, closing it here cause the error you mentioned.

The "rs" variable is not declared anywhere.
But, if you make a mistake and declare a public variable named rs in other sub or function, you may get unexpected errors because:
1. when set rs = ... you are replacing any value by a new dataset, losing any data you previously read;
2. the "rs" variable remains open in memory, and you couldn't be some operations in this variable.

I recommend that you go to TOOLS > OPTIONS menu, and turn ON (checking option) REQUIRE VARIABLE DECLARATION.

Success!
Sidnei

Thanks a lot to all of you
The code is running perfectly ok after I removed the following lines

rs.Close
cn.Close

thumbs up to all of you

[ankush.mukherje]

Thanks dear...thanks a lot