DataGrid Advanced Techniques

Grid Enhancements

There are some grid-related features users have come to expect, that the DataGrid stops well short of making easy to implement. The most notable of these would be Access-style lookup columns, boolean columns that are represented graphically as checkboxes, and the use of context menus.

The application that accompanies this article demonstrates all of these, and this section will hopefully explain the mechanics of each.

Context Menus

The grid itself does not support a ContextMenu event, as do most other VB controls. It does support a Click event, but it's useless for this purpose because it does not identify which mouse button was clicked. The MouseUp event is suitable for this purpose, but there are two little quirks you'll need to know about.

Hocus Focus

The Grid will not fire MouseUp for the right button unless it has focus. The net effect is the user would be forced to left-click the Grid prior to right-clicking it, unless it already had focus—it makes the app look unresponsive. The Grid does fire the MouseDown event when either button is clicked, so the work-around is to explicitly set focus to the Grid in the Grid_MouseDown handler. (It does this by default for the left button.)

Menus Gone Mad

The other quirk involves the call to the VB function PopupMenu. There are two valid ways to call this: It can be called as a method of an explicitly specified object, or it can be called without an object, in which case the active form is implied. So, in a form, the two statements below would seem to be equivilent:

PopupMenu mnuFile
Me.PopupMenu mnuFile

They are not the same, though. If your menu item causes some other object to receive focus, the first form can cause the menu to be spuriously redisplayed. Apparently, the call loses track of where it originated. It fails to retain a reference to the object on which it was implicitly called, somehow causing it to "forget" that it has already displayed the menu. (I squandered many hours chasing this most irritating little flaw.)

The Lookup Column

The Column object exposes a boolean property named Button. When set to true for a given column, the cells in that column are shown with a graphical "button" drawn on the right edge, much like the element of a combobox that's clicked to make it display its drop-down list. That's where the similarity ends, though. There is no functionality associated with this element by default; clicking it merely fires the "ButtonClick" event. It's up to your app to do anything with it.

To create this effect, the ButtonClick event handler must dynamically position a listbox just below the active cell. The code in the figure below accurately calculates the correct position, as long as the list and the grid are both immediate children of their form, or in the same container:

With Grid
   List.Top = .Top + .RowTop(.Row) + (.RowHeight)
   List.Left = .Left + .Columns(ColIndex).Left
End With

If the grid happens to be inside of a control that is a container of other controls (for instance, a PictureBox or Frame control), we have a bit more work to do: We must walk up the container chain until we find an object that either has no container property, has no parent property, has container and parent that are the same object, or has a container of type Form/MdiForm.

To perform this fixup, I wrote the CalcContainerOffset function (implemented in the MainForm object of the demo project), which assumes the list is a direct descendant of its form. I suppose it would be possible to look for the list's container if it were a different container within the same chain of nested containers, but, quite frankly, the need for this exercise escaped me. The code in this example merely checks to see that the Grid's container and the List's container are not the same object, and then assumes the list is directly contained by the form.

Once we've gotten the list to display properly, we're half-way home. If the list's contents will be changing based on a value in the current row, you can refill it in the ButtonClick event before making it visible. All that's left to do is process a few keyboard and mouse events to fill out the effect, as explained below.

The List_Click event fires when the user selects an item using any means, including browsing the list with up/down arrows and entry matching with alpha-numeric input. So, we copy the value to the Grid, but hiding it at this point would be premature. (MouseUp indirectly hides the list when a real mouse click happens.)

Private Sub List_Click()
   Grid.Text = List.Text
   Exit Sub
End Sub

Processing the keystrokes as shown below gives the effect a familiar look and feel. Escape cancels the list without copying a value. (Hitting escape a second time after the list has been hidden reverts the value in the grid if it has been changed by other events.) Enter and tab both accept the current selection and [indirectly] hide the list. Note that we don't process the arrow keys because they already cause the Click event to fire; processing them here would be redundant.

Private Sub List_KeyDown(KeyCode As Integer, Shift As Integer)
On Error Resume Next
   Select Case KeyCode
      Case vbKeyTab
         Grid.Text = List.Text
      Case vbKeyEscape
      Case vbKeyReturn
         Grid.Text = List.Text
   End Select
   Exit Sub
End Sub

A MouseUp event means the user has clicked somewhere in the list. The Click event will already have fired, so no need to copy values here.

Private Sub List_Mouseup(Button As Integer, Shift As Integer, _
                         X As Single, Y As Single)
On Error Resume Next
   Exit Sub
End Sub

All of the events above that hide the list do so indirectly by taking focus away from it; this event does the actual work. We set focus to the grid to prevent the tab key from taking the user out of the Grid and to the next control.

Private Sub List_LostFocus()
' close/hide the list if it loses focus
On Error Resume Next
   List.Visible = False
   Exit Sub
End Sub

That's basically it for this one. It may seem like a lot of trouble, but it is a worthy effect.

The Checkbox Column

Implementing checkboes for boolean columns involves many of the same constructs as the lookup column, except that we must have one checkbox for every visible row, and we must maintain synchronization between the visible states of the checkboxes and the data that underlies them.

Checkboxes in VB aren't actually boolean, they are tristate; a value property of 1 indicates the check mark is set, 0 means it is clear, and 2 means it is grayed to symbolize an indeterminate value. Values 1 and 0 map perfectly to SQL Server bit columns but some adjustment is needed for to support [VB-native] variant boolean values, which represent "True" with the numeric value -1. You can use the Abs function to convert a True expression to set a checkbox; for example,

checkbox1.value = Abs(str = "Yes")

This will evaluate to 1 if the value of str is "Yes", and 0 otherwise.

To dynamically supply each visible row with a checkbox at runtime, we must create a control array with one element at design time—at index 0. This can be done in several ways, the easiest of which to explain being:

  1. Create a checkbox on the form.
  2. Set the Index property of that checkbox to 0 (it will be empty by default.)

A non-empty Index property converts a regular control to a control array. Once done, you can create additional elements in that array, using the Load statement in your code. The pair of loops below will adjust the number of array elements up or down to suit the size of the Grid:

i With
Grid If
   (ChkboxArray.UBound <>  .VisibleRows) Then
      For i = ChkboxArray.UBound + 1 To .VisibleRows - 1
         Load ChkboxArray(i)
      For i = .VisibleRows To ChkboxArray.UBound
         Unload ChkboxArray(i)
   End If
End With

Syncing changes made by a user clicking a checkbox is best accomplished using a clone of the recordset that's bound to the Grid—we don't want to use the same recordset as the Grid uses its record position internally, and altering it causes negative behavior to say the least. A clone is created by calling the Recordset.Clone method (see the ADO documentation for a full discussion of recordset cloning.) Assume for purposes of this handler that a valid clone has been created at class scope or above.

Private Sub cb_Click(index As Integer)
On Error Resume Next
   With Grid
      rsClone.Bookmark = .RowBookmark(index)
      rsClone.Fields(1).Value = cb(index).Value * -1
   End With
End Sub

If something other than the grid could change the underlying data while it's displayed in the grid, we must declare the recordset object WithEvents, and process the RecordChangeComplete event. Note the use of a global boolean flag (bInSetCheckboxes) to prevent a recursive event storm.

Private Sub rsClone_RecordChangeComplete _
   ( _
      ByVal adReason As ADODB.EventReasonEnum, _
      ByVal cRecords As Long, _
      ByVal pError As ADODB.Error, _
      adStatus As ADODB.EventStatusEnum, _
      ByVal pRecordset As ADODB.Recordset _
   If bInSetCheckboxes = True Then Exit Sub
   SetCheckboxes 1, cb
End Sub

That's basically it! The sample application that accompanies this article demonstrates implementation of these technique. (It also includes a utility form called GridSpy; that's explained in a companion article.)

About the Author

Mark McGinty

Programming professionally for 15 years I've used a wide range of languages, including C/C++, x86 assembler, JScript, numerous forms of VB, SmallTalk and T-SQL, to name a few. Much of what I do these days involves databases to some degree, mostly back-end database/application architecture for data-driven websites.



  • Database password

    Posted by huong75 on 11/11/2005 04:45am

    I'm using Access2003 and have a query: "INSERT INTO Manager_Exercises SELECT ID_GroupEx AS ID_GroupEx, ID_TypeEx AS ID_TypeEx, Exercises AS Exercises, Develop_Muscles AS Develop_Muscles, Customize AS Customize, Path_File AS Path_File, Training AS Training, Path_Pic AS Path_Pic, Path_Pic1 AS Path_Pic1 FROM Manager_Exercises IN 'c:\Workout\Import_Export\import.mdb' WHERE ID_Exercises=203;". import.mdb file had set password. when i run query then have error " Not a valid Password" Could you help me to run that query. Thanks Huong

    • Life is too short to waste time using Jet

      Posted by mmcginty on 11/12/2005 04:26pm

      The best advice I can offer you is to use a more capable DBMS, especially now that SQL Server Express has been released. It's free, and infinitely more usable than is MS Jet (the engine that comes with Access.) -MM

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds