error regarding Private Sub cmdDelete_Click()

HI..
I need help over here... i have a delete button but is giving me error as " operation is not allowed when the object is closed"
Can anyone help me here, plz
thanks a lot


Private Sub cmdDelete_Click()
Dim X As String
Dim Rs As New ADODB.Recordset
If Rs.State = adStateOpen Then Rs.Close
'Rs.Open
X = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If X = vbYes Then
Rs.Delete
Rs.Close
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If
End Sub

re: error regarding Private Sub cmdDelete_Click()

Hi,

You are Closing the recordset object. Recordset object has to be Open, to delete the Record

remove this line :If Rs.State = adStateOpen Then Rs.Close

Regards
Veena

re: error regarding Private Sub cmdDelete_Click()

Hi Veena..
I removed tht line but stil i m getting the same error...

re: error regarding Private Sub cmdDelete_Click()

Dear Veena,
i have changed my code to this...

Private Sub cmdDelete_Click()
Dim X As String
Dim RECORD As String
Dim Rs As New ADODB.Recordset
' Rs.Open
'Rs.Open ("select * from CASEMASTER where Id = '" & txtId.Text & "'"), Db, adOpenKeyset, adLockPessimistic
X = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If X = vbYes Then
Rs.Open ("select * from CASEMASTER where Id = '" & txtId.Text & "'"), Db, adOpenKeyset
Rs.Delete
Rs.Close
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
Call Grid_Data
End If
End Sub


the selected record is deleting for the first time but when i select another record is giving me this error " Either BOF or EOF is true, or the current record has been deleted".... and the second probelm is my gird is not gettind refreshed...
plz help

re: error regarding Private Sub cmdDelete_Click()

Try this

At line 7 and 8 instead of opening the recordset try the execute command
I assume that your db is your active connection and take a look also at line 11 I added Call Grid_Data to refresh the list assuming that this is your subroutine to refresh the list.

Rey Sean

re: error regarding Private Sub cmdDelete_Click()

Hi Lotus..
Thank u very much for ur help.. i did whtever u told me.. i m not getting any error bt the record is not getting deleted from the grid or database...
my code is this now :

Private Sub cmdDelete_Click()
Dim X As String
Dim RECORD As String
Dim Rs As New ADODB.Recordset
X = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If X = vbYes Then
Db.Execute "Delete from CASEMASTER where Id = '" & txtId.Text & "'"
'Rs.Open ("Delete from CASEMASTER where Id = '" & txtId.Text & "'"), Db, adOpenKeyset
'Rs.Close
Set Rs = Nothing
MsgBox "Record Deleted!", , "Message"
Call Grid_Data
Else
MsgBox "Record Not Deleted!", , "Message"
Call Grid_Data
End If
End Sub

Need ur help here... Thankx

re: error regarding Private Sub cmdDelete_Click()

I got it.. thankx a lot :)