UNDO in toolbar

given the code below, can somebody help me why my form/program generates run-time error "3521" : object/provider is not capable of performing requested operation? i would like to the UNDO button in my Toolbar1 to work. i thought using rs.bookmark would be the best way to go about it. SOMEBODY PLEASE HELP ME!!!!!!!!!


Private agents_bookmark As Variant
Private rs_agents As ADODB.Recordset
Private rs_groups As ADODB.Recordset
Private rs As ADODB.Recordset
Private current_state As String
Private sel_AGENT_ID As Integer

Private Sub Form_Activate()

Set rs = New ADODB.Recordset

cn.Open
rs.Open "SELECT * FROM AGENTS ORDER BY LNAME, FNAME", cn, adOpenDynamic, adLockOptimistic

lstAgents.Enabled = False
Do While Not rs.EOF
lstAgents.AddItem rs("LNAME") & ", " & rs("FNAME")
lstAgents.ItemData(lstAgents.NewIndex) = rs("AGENT_ID")
rs.MoveNext
Loop
lstAgents.Enabled = True
rs.Close
cn.Close
End Sub

Private Sub Form_Load()
Toolbar1.Buttons(2).Enabled = True 'new agent
Toolbar1.Buttons(3).Enabled = True 'new recruit
Toolbar1.Buttons(4).Enabled = True 'edit
Toolbar1.Buttons(5).Enabled = False 'save
Toolbar1.Buttons(6).Enabled = False 'delete
Toolbar1.Buttons(7).Enabled = False 'undo

MSHFlexGrid1.Enabled = True

For i = 100 To 0 Step -1
cmbPercentage.AddItem i
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
Set ListofAgentsForm = Nothing
End Sub

Private Sub lstAgents_DblClick()
'get the item the user clicks on and assign it
If (InStr(lstAgents, "'")) Then
gFindString = SrchReplace(lstAgents)
Else
gFindString = lstAgents
End If
Unload ListofAgentsForm
End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
Dim user_reply As Integer

Select Case Button.Caption
Case Is = "New Agent"
Toolbar1.Buttons(4).Enabled = True 'edit
Call New_Agent
current_state = "New Agent"
Case Is = "New Recruit"
ListofGroupsForm.Show
Unload Me
Set ListofAgentsForm = Nothing
Case Is = "Edit"
current_state = "Edit"
Call Edit_Agent
Case Is = "Save"
Select Case current_state
Case Is = "New Agent"
Call Save_NewAgent
Case Is = "Edit"
Call Save_EditAgent
End Select
Case Is = "Delete"
user_reply = MsgBox("Are you sure you want to delete this record?", vbYesNo, NewHospitalAdForm.Caption)
If user_reply = 6 Then
Call Delete_Agent
End If
Case Is = "Undo"
cn.Open
Set rs_agents = New ADODB.Recordset
rs_agents.Open "SELECT * FROM AGENTS WHERE AGENT_ID=" & sel_AGENT_ID, _
cn, adOpenDynamic, adLockOptimistic
If (Not rs_agents.EOF) Then
rs_agents.CancelUpdate
If (Len(agents_bookmark)) Then
rs_agents.Bookmark = agents_bookmark
End If
End If
rs_agents.Close
cn.Close
Case Is = "Close"
Unload Me
Set ListofAgentsForm = Nothing
End Select

End Sub


Private Sub Edit_Agent()
Toolbar1.Buttons(4).Enabled = False 'edit
Toolbar1.Buttons(5).Enabled = True 'save
Toolbar1.Buttons(6).Enabled = True 'delete
Toolbar1.Buttons(7).Enabled = True 'undo

Call Unlock_FindLists
Call Unlock_Fields
End Sub

Private Sub New_Agent()
Toolbar1.Buttons(4).Enabled = True 'edit
Toolbar1.Buttons(5).Enabled = True 'save
Toolbar1.Buttons(6).Enabled = False 'delete
Toolbar1.Buttons(7).Enabled = False 'undo

Call Lock_FindLists
Call Unlock_Fields
End Sub

Private Sub txtFindAgent_Change()
Dim entryNum As Long
Dim txtToFind As String
txtToFind = txtFindAgent.Text

entryNum = sendMessageByString&(lstAgents.hwnd, LB_SELECTSTRING, 0, _
txtToFind)

If (entryNum > -1) Then
Toolbar1.Buttons(6).Enabled = True 'delete
sel_AGENT_ID = lstAgents.ItemData(lstAgents.ListIndex)
current_state = "Edit"
Call display_agent_info(sel_AGENT_ID)
Else
Toolbar1.Buttons(4).Enabled = False 'edit
Toolbar1.Buttons(5).Enabled = False 'save
Toolbar1.Buttons(6).Enabled = False 'delete
Toolbar1.Buttons(7).Enabled = False 'undo
sel_AGENT_ID = 0
current_state = ""
Call Lock_Fields
End If

End Sub
Private Sub display_agent_info(sel_AGENT_ID As Integer)

Toolbar1.Buttons(4).Enabled = False 'edit
Toolbar1.Buttons(5).Enabled = True 'save
Toolbar1.Buttons(6).Enabled = True 'delete
Toolbar1.Buttons(7).Enabled = True 'undo

Call Unlock_Fields

cn.Open
Set rs_agents = New ADODB.Recordset
rs_agents.Open "SELECT * FROM AGENTS WHERE AGENT_ID=" & sel_AGENT_ID, _
cn, adOpenDynamic, adLockOptimistic
If (Not rs_agents.EOF) Then
agents_bookmark = rs_agents.Bookmark
txtFirstName.Text = rs_agents("FNAME")
txtLastName.Text = rs_agents("LNAME")
txtStreet.Text = rs_agents("STREET")
txtCity.Text = rs_agents("CITY")
txtZipCode.Text = rs_agents("ZIP_CODE")
txtContactNo.Text = rs_agents("CONTACT_NO")
cmbPercentage.ListIndex = 100 - rs_agents("PERCENTAGE")
Else
agents_bookmark = ""
End If
rs_agents.Close

MSHFlexGrid1.Enabled = True
MSHFlexGrid1.BackColor = vbWhite

Set rs_groups = New ADODB.Recordset

rs_groups.Open "SELECT * FROM GROUPS WHERE AGENT_ID=" & sel_AGENT_ID, cn, adOpenDynamic, adLockOptimistic
Set MSHFlexGrid1.DataSource = rs_groups
rs_groups.Close

cn.Close

End Sub

Public Function SrchReplace(ByVal sStringToFix As String) As String

Dim iPosition As Integer
Dim sCharToReplace As String
Dim sReplaceWith As String
Dim sTempString As String

sCharToReplace = "'"
sReplaceWith = "''"
iPosition = InStr(sStringToFix, sCharToReplace)

Do While iPosition
sTempString = ""
sTempString = sTempString & Left$(sStringToFix, iPosition - 1)
sTempString = sTempString & sReplaceWith
sTempString = sTempString & _
Mid$(sStringToFix, iPosition + 1, Len(sStringToFix))
iPosition = InStr(iPosition + 2, sStringToFix, sCharToReplace)
Loop

SrchReplace = sTempString
End Function

Private Sub Save_NewAgent()
Dim lastagentid As Long
Dim i As Integer
Dim incorrectText As String

Set rs = New ADODB.Recordset

If (IsNumeric(txtZipCode) And IsNumeric(txtContactNo)) Then
cn.Open
rs.Open "SELECT * FROM AGENTS", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs("FNAME") = UCase(txtFirstName.Text)
rs("LNAME") = UCase(txtLastName.Text)
rs("STREET") = UCase(txtStreet.Text)
rs("CITY") = UCase(txtCity.Text)
rs("ZIP_CODE") = Trim(txtZipCode.Text)
rs("CONTACT_NO") = txtContactNo.Text
rs("PERCENTAGE") = cmbPercentage.Text
rs.Update
rs.MoveLast
lastagentid = rs("AGENT_ID")
rs.Close
cn.Close
Else
If (IsNumeric(txtZipCode)) Then
incorrectText = "Zip Code"
Else
incorrectText = "Contact No"
iReply = MsgBox("Please enter numeric values in " & incorrectText, _
vbInformation, NewGroupForm.Caption)
End If
proceed = False
End If

txtFirstName.Text = ""
txtLastName.Text = ""
txtStreet.Text = ""
txtCity.Text = ""
txtZipCode.Text = ""
txtContactNo.Text = ""
cmbPercentage.ListIndex = 0

End Sub

Private Sub Save_EditAgent()
Dim lastagentid As Long
Dim i As Integer
Dim incorrectText As String

Set rs = New ADODB.Recordset

If (IsNumeric(txtZipCode) And IsNumeric(txtContactNo)) Then
cn.Open
rs.Open "SELECT * FROM AGENTS WHERE AGENT_ID=" & sel_AGENT_ID, cn, adOpenDynamic, adLockOptimistic
rs("FNAME") = UCase(txtFirstName.Text)
rs("LNAME") = UCase(txtLastName.Text)
rs("STREET") = UCase(txtStreet.Text)
rs("CITY") = UCase(txtCity.Text)
rs("ZIP_CODE") = Trim(txtZipCode.Text)
rs("CONTACT_NO") = txtContactNo.Text
rs("PERCENTAGE") = cmbPercentage.Text
rs.Update
rs.MoveLast
lastagentid = rs("AGENT_ID")
rs.Close
cn.Close
Else
If (IsNumeric(txtZipCode)) Then
incorrectText = "Zip Code"
Else
incorrectText = "Contact No"
iReply = MsgBox("Please enter numeric values in " & incorrectText, _
vbInformation, NewGroupForm.Caption)
End If
proceed = False
End If

txtFirstName.Text = ""
txtLastName.Text = ""
txtStreet.Text = ""
txtCity.Text = ""
txtZipCode.Text = ""
txtContactNo.Text = ""
cmbPercentage.ListIndex = 0
End Sub

Private Sub Delete_Agent()

Set rs = New ADODB.Recordset
cn.Open
rs.Open "SELECT * FROM AGENTS WHERE AGENT_ID=" & sel_AGENT_ID, cn, adOpenDynamic, adLockOptimistic
rs.Delete
rs.Close
cn.Close

txtFirstName.Text = ""
txtLastName.Text = ""
txtStreet.Text = ""
txtCity.Text = ""
txtZipCode.Text = ""
txtContactNo.Text = ""
cmbPercentage.ListIndex = 0
End Sub

Are you wanting the UNDO action to revert the delete action from the database?

hi, i would like the UNDO action for the DELETE and SAVE action.
do you know what's wrong with my code above?

One way to perform an `undo` in database terms is to rollback the transaction. If you are using a relational database you can accomplish this by issuing the `rollback` command if you have not issued a commit command. Does the database you are using support this feature?

hi! i am using sql server 7 but i don't know if it has a rollback feature. if it does, how do i incorporate it in my code? thank u so much for your patience

I am assuming you mean MS SQL Server 7.0. You control transactions by issuing
BEGIN TRANS <to start a transaction>
...
ROLLBACK <to rollback transactions>
...
COMMIT TRANS <writes all transactions to disk>