Bookmark collection with DataGrid control not cleared 
Author Message
 Bookmark collection with DataGrid control not cleared

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

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.*-*-*.com/
--



Mon, 20 Dec 2010 02:01:10 GMT  
 Bookmark collection with DataGrid control not cleared



 I had to

Quote:
> 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.

This will 'clear' the collection.
Forgot who I stole it from and the details for why it has to do the checks
('0', '1', or '>1'), but it do. <g>

Dim ndx As Integer
Dim varBmk As Variant
With grdItems
    Do Until .SelBookmarks.Count = 0
        For Each varBmk In .SelBookmarks
            If .SelBookmarks.Count = 0 Then Exit Sub
            If .SelBookmarks.Count = 1 Then
                ndx = 0
            ElseIf .SelBookmarks.Count > 1 Then
                ndx = .SelBookmarks.Count - 1
            End If
            .SelBookmarks.Remove (ndx)
        Next
    Loop
End With

hth
-ralph



Tue, 21 Dec 2010 06:38:21 GMT  
 Bookmark collection with DataGrid control not cleared


Quote:



> 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.

> This will 'clear' the collection.
> Forgot who I stole it from and the details for why it has to do the checks
> ('0', '1', or '>1'), but it do. <g>

> Dim ndx As Integer
> Dim varBmk As Variant
> With grdItems
>    Do Until .SelBookmarks.Count = 0
>        For Each varBmk In .SelBookmarks
>            If .SelBookmarks.Count = 0 Then Exit Sub
>            If .SelBookmarks.Count = 1 Then
>                ndx = 0
>            ElseIf .SelBookmarks.Count > 1 Then
>                ndx = .SelBookmarks.Count - 1
>            End If
>            .SelBookmarks.Remove (ndx)
>        Next
>    Loop
> End With

> hth
> -ralph

Excellent. That seems to work. I think the key is that items in the
collection are removed from last to first.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--



Tue, 21 Dec 2010 08:21:20 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Changing Bookmark's text deletes bookmark from collection

2. Listview1.listitems.clear does not clear

3. Clearing contents of bookmark

4. How do I Clear text in bookmarks

5. Crystal Reports 6 control does not clear formulae when you change report

6. VB Collections - Accessing the collection, not the item.

7. Why VBA.Collection and Not VB.Collection?

8. How can i clear the MSHFLEXGRID without CLEAR - Como limpar a GRID sem usar CLEAR

9. Looping through the bookmarks collection

10. Help With Bookmarks Collection

11. Dup keys in collection using bookmarks

12. Clearing DAO error collection

 

 
Powered by phpBB® Forum Software