Private Sub cmdSearch_Click()

On Error GoTo errhan

Set rst = New ADODB.Recordset

With rst

    .CursorLocation = adUseClient
    .ActiveConnection = Con
    .CursorType = adOpenDynamic
    .LockType = adLockPessimistic
    
    SQL = "Select * from profile2 where name like '" & Trim(fldSearch.Text) & "'"

End With

Set rst = Nothing

Call dload
errhan:

If Err.Description <> vbNullString Then
    MsgBox Err.Description
End If
End Sub

Private Sub cmdSearch_Click()
 
    On Error GoTo errhan
 
    Set rst = New ADODB.Recordset
 
    rst.CursorLocation = adUseClient
    SQL = "Select * from profile2 where name like '" & Trim(fldSearch.Text) & "%'"
    rst.Open SQL, con, adOpenKeyset, adLockReadOnly
 
    MSFlexGrid1.Clear
    While Not rst.EOF
        'replace field1,field2 etc with the fields u want
        MSFlexGrid1.addItem rst!field1 & vbTab & rst!field2
        rst.MoveNext
    Wend
 
    Call dload
 
exitPoint:
    If Not rst Is Nothing Then
         if rst.state then rst.close
        Set rst = Nothing
    End If
    Exit Sub
 
errhan:
    MsgBox "Err No : " & Err.Number & vbCrLf & Err.Description
    Resume exitPoint
 
End Sub

Private Sub cmdSearch_Click()
 
    On Error GoTo errhan
 
    'Close the recordset if open first before searching
    If Not rst Is Nothing Then
         if rst.state then rst.close
        Set rst = Nothing
    End If
    'Instantiate again
    Set rst = New ADODB.Recordset
 
    rst.CursorLocation = adUseClient
    SQL = "Select * from profile2 where name like '" & Trim(fldSearch.Text) & "%'"
    'Open the recordset
    rst.Open SQL, con, adOpenKeyset, adLockReadOnly
 
    'Now u can call this procedure and get the recordset to work without error
    'because it is not yet closed
    Call dload
 
exitPoint:
    Exit Sub
 
errhan:
    MsgBox "Err No : " & Err.Number & vbCrLf & Err.Description
    'Put the error control code here to close recordset if any error occurs
    If Not rst Is Nothing Then
         if rst.state then rst.close
        Set rst = Nothing
    End If
    Resume exitPoint
 
End Sub
 
 
Private Sub dlload()
 
    MSFlexGrid1.Clear
    While Not rst.EOF
        'replace field1,field2 etc with the fields u want
        MSFlexGrid1.addItem rst!field1 & vbTab & rst!field2
        rst.MoveNext
    Wend
 
End Sub

'Declare this Form-Level 
Dim Conn As New ADODB.Connection
 
'In FormLoad open the connection object:
With Conn
    .ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source = C:\MyDB.mdb"
    .Open
End With

With rst
    .CursorLocation = adUseClient
    .ActiveConnection = Con
    .CursorType = adOpenDynamic
    .LockType = adLockPessimistic    
    SQL = "Select * from profile2 where name like '%" & Trim(fldSearch.Text) & "%'"
    .Open SQL,Conn
End With