Delete DataGrid List

[abelingaw]

Ok, i have this form which contains a Datagrid control, i have fix the 1st problem i encountered which is how to connect it to my Dbase to display the content of one of my tables.

Now my problem is, i cant delete records in my Dbase thru selecting it in the Datagrid directly then pressing my Delete command button.

I get the error "OPERATION IS NOT ALLOWED WHEN THE OBJECT IS CLOSED".
I know it has something to do with my Dbase connection code/s, but cant really figure it out.

Heres my form pic:

http://img818.imageshack.us/img818/200/sysuser.jpg

And heres my code for the Delete Command Button:

Private Sub cmdDelete_Click()


  On Error GoTo err
  
    
    Dim intYN
    
    Set rs = New ADODB.Recordset
    
    With rs
    
        If .State = adStateOpen Then .Close
    
    intYN = MsgBox("You are about to delete a record." & vbCrLf & _
        "If you click Yes, you won't be able to undo this delete operation." & _
        vbCrLf & vbCrLf & _
        "Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Confirm Delete")
        
    If intYN = vbNo Then Exit Sub

    
    Dbconn.Execute "Delete From Users where UserName = DataGrid1.Text"

    Call GetUsers
    
    txtName.Text = ""
    txtPass.Text = ""
    txtConfirm.Text = ""
    cboPriv.Text = ""
    MsgBox "The Record has been deleted.", vbInformation

    Exit Sub
    
err:
    MsgBox err.Description, vbCritical
    
End With

End Sub

I think the problem is in Line 24 (i think)..

Can u check it? Thanks

[kb.net]

You should use:
"Delete From Users where UserName ='" & DataGrid1.Text & "'"
Even though am not sure what you mean by DataGrid1.Text

[AndreRet]

If .State = adStateOpen Then .Close

- You have closed the connection of the recordset. Re-open the recordset.

[abelingaw]

I have fix the problem.

But why is it that the record that is deleted is the first one (top) and not the one i selected (highlighted)..

On Error GoTo err
  
  Call con
    
    Dim intYN
    
    Set rs = New ADODB.Recordset
    
    With rs
    
        If .State = adStateOpen Then .Open
        
        rs.Open "Select * from Users", Dbconn, adOpenKeyset, adLockPessimistic
    
    intYN = MsgBox("You are about to delete a record." & vbCrLf & _
        "If you click Yes, you won't be able to undo this delete operation." & _
        vbCrLf & vbCrLf & _
        "Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Confirm Delete")
        
    If intYN = vbNo Then Exit Sub

    
    Dbconn.Execute "Delete From Users where UserName ='" & DataGrid1.Text & "'"


    .Delete
    
    Call GetUsers
    
    txtName.Text = ""
    txtPass.Text = ""
    txtConfirm.Text = ""
    cboPriv.Text = ""
    MsgBox "The Record has been deleted.", vbInformation
    
        .Close
        
    Exit Sub
    
err:
    MsgBox err.Description, vbCritical
    
End With

Any code for that..?

[AndreRet]

Try to use the following -

.Delete

.Delete adAffectCurrent