VB Forum / General / July 2008

I'm using the "Microsoft DataGrid 6.0" component (msdatgrd.ocx) and allowing
the user to delete rows in the grid by clicking a Delete command button. The
DataSource of the grid is a disconnected recordset. I loop through the
selBookmark collection and delete each selected row. This works, but at the
end the selBookmark collection is only empty if the user deleted one row. If
the user selects several rows, all but one bookmark remains in the
collection. I cannot find a way to clear the selBookmark collection. Does
someone know how to clear the selBookmark collection after the corresponding
rows in the grid have been deleted?

For example, my delete botton click event sub could be:
=========
Private Sub cmdDelete_Click()
   Dim varBmk As Variant

   Call MsgBox("Before delete count = " & grdItems.SelBookmarks.Count)
   For Each varBmk In grdItems.SelBookmarks
       m_adoItems.Bookmark = varBmk
       m_adoItems.Delete adAffectCurrent
   Next
   m_adoItems.MoveFirst
   Call MsgBox("After delete count = " & grdItems.SelBookmarks.Count)

End Sub
=======
If you select 3 rows and click cmdDelete the 3 rows are deleted. But if you
click the cmdDelete button again without selecting anything, another 2 rows
are deleted.

I know you can work around this behavior in most cases, but it seems like a
bug. I wanted to allow the user to delete the last row added to the grid
without having to select it. This meant the cmdDelete button is always
enabled. If nothing is selected, the last item added to the grid is deleted.
If one or more items are selected, the selected items are deleted. I had to
abandon this behavior and only delete rows if they are selected because I
could not clear the selBookmark collection at the end of the event sub. As
it is I cannot always tell when to disable the cmdDelete button.

The Sub Form_Load I use to associate the recordset with the DataGrid is
similar to:
=========
Option Explicit
Private m_adoItems As ADODB.Recordset

Private Sub Form_Load()

   ' Create recordset for items.
   Set m_adoItems = New ADODB.Recordset
   m_adoItems.CursorLocation = adUseClient
   m_adoItems.CursorType = adOpenStatic
   m_adoItems.LockType = adLockBatchOptimistic

   m_adoItems.Fields.Append "Item", adVarChar, 50
   m_adoItems.Fields.Append "Amount", adVarChar, 20
   m_adoItems.Fields.Append "ID", adInteger

   m_adoItems.Open

   grdItems.ClearFields
   Set grdItems.DataSource = m_adoItems
   grdItems.Columns("Item").Width = 2250
   grdItems.Columns("Amount").Width = 930
   grdItems.Columns("ID").Width = 0

   m_adoItems.AddNew
   m_adoItems.Fields("Item").Value = "Hammer"
   m_adoItems.Fields("Amount").Value = Format(1.5, "$##0.00")
   m_adoItems.Fields("ID").Value = 11
   m_adoItems.Update

   m_adoItems.AddNew
   m_adoItems.Fields("Item").Value = "File"
   m_adoItems.Fields("Amount").Value = Format(0.6, "$##0.00")
   m_adoItems.Fields("ID").Value = 5
   m_adoItems.Update

   m_adoItems.AddNew
   m_adoItems.Fields("Item").Value = "Plane"
   m_adoItems.Fields("Amount").Value = Format(1.1, "$##0.00")
   m_adoItems.Fields("ID").Value = 22
   m_adoItems.Update

   m_adoItems.AddNew
   m_adoItems.Fields("Item").Value = "Drill"
   m_adoItems.Fields("Amount").Value = Format(2.3, "$##0.00")
   m_adoItems.Fields("ID").Value = 11
   m_adoItems.Update

   m_adoItems.AddNew
   m_adoItems.Fields("Item").Value = "Square"
   m_adoItems.Fields("Amount").Value = Format(0.7, "$##0.00")
   m_adoItems.Fields("ID").Value = 5
   m_adoItems.Update

   m_adoItems.MoveFirst
   grdItems.Refresh

End Sub