why operation is not allowed when the object is open

[sonunclejalil]

Private Sub cmdfind_Click()
Dim search As String
search = txtfind.Text
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\HOSTEL STUDENT.mdb;Persist Security Info=False"
Rs.Open "SELECT * FROM STUDENTS WHERE [REG] LIKE" & "'" & search & "'", Con, adOpenStatic, adLockOptimistic
Do Until Rs.EOF
    Rs.MoveNext
    Loop

If Rs.EOF = True Or Rs.BOF = True Then
    MsgBox "PLEASE REGISTER YOUR NAME TO WARDEN"
        Else
txtNAME = Rs.Fields("NAME")
txtMATRIX = Rs.Fields("MATRIX NO")
txtIC = Rs.Fields("IC NO")
txtCONT = Rs.Fields("CONTACT NO")
txtROOM = Rs.Fields("ROOM NO")
End If
Rs.Close
Con.Close
End Sub

[tinstaafl]

what object and what error message?

[sonunclejalil]

the error message is 3705, that operation is not allowed when the object is open , i don't what the problem of my coding.
when i delete this part Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\HOSTEL STUDENT.mdb;Persist Security Info=False"
Rs.Open "SELECT * FROM STUDENTS WHERE [REG] LIKE" & "'" & search & "'", Con, adOpenStatic, adLockOptimistic
then i put the what i want to find , it not appear what i want but appear the other info, my project is to make an student outing systems

[sonunclejalil]

can you check what the problem of my coding ?
*

Dim Con As New ADODB.Connection
Dim Rs As New ADODB.Recordset

Private Sub cmdfind_Click()
Dim search As String
search = txtfind.Text
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\HOSTEL STUDENT.mdb;Persist Security Info=False"
Rs.Open "SELECT * FROM STUDENTS WHERE [REG] LIKE" & "'" & search & "'", Con, adOpenStatic, adLockOptimistic
Do Until Rs.EOF
    Rs.MoveNext
    Loop

If Rs.EOF = True Or Rs.BOF = True Then
    MsgBox "PLEASE REGISTER YOUR NAME TO WARDEN"
        Else
txtNAME = Rs.Fields("NAME")
txtMATRIX = Rs.Fields("MATRIX NO")
txtIC = Rs.Fields("IC NO")
txtCONT = Rs.Fields("CONTACT NO")
txtROOM = Rs.Fields("ROOM NO")
End If
Rs.Close
Con.Close
End Sub

Private Sub Form_Load()
Call OpenConnection
With Rs
    .ActiveConnection = Con
    .CursorType = adOpenDynamic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open "SELECT * FROM STUDENTS"
End With

End Sub

Private Sub OpenConnection()
If Con.State = 1 Then
    Con.Close
End If
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Fairus\Documents\HOSTEL STUDENT.mdb;Persist Security Info=False"
Con.Open

End Sub*

[rishif2]

this is because you open a connection which is already opened.First you open the connection inside OpenConnection() procedure and then again you open the connection in cmdfind_Click() procedure.

so remove line 7 from cmdfind_Click() procedure.

you should also consider followings:-

when you done a database task then don't forget to close that object so also add .close inside Form_Load().However i don't understand why you put The code to retrieve students data inside Form_Load() Because you are not using recordset object further.

here i'm not not sure what you wanna do with the following code

Do Until Rs.EOF
Rs.MoveNext
Loop

finally loop terminated when it reach to EOF and that's your if condition will be executed , else part will never get executed.

if you want to retrieve data on the basis of REG then i think that code from line 9 to 12 should be remove as it will give no sence.

at last, It's best practise to set noting to these objects(rs,con) so , also add the following after line 23 :-

Set Rs=Nothing
Set Con=Nothing

hope these little things help you . . .

[sonunclejalil]

to rishif2, thanks a lot buddy, your advice really helping me :)
my problem solve,

[rishif2]

its pleasurable
happy coding . . .

[sonunclejalil]

to rishif2, may u teach me how to save data from vb 6 to ms access 2007,