How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017
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.
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 Grid.SetFocus Case vbKeyEscape Grid.SetFocus Case vbKeyReturn Grid.Text = List.Text Grid.SetFocus 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 Grid.SetFocus 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 Grid.SetFocus 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:
- Create a checkbox on the form.
- 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:
Dim i With Grid If (ChkboxArray.UBound <> .VisibleRows) Then For i = ChkboxArray.UBound + 1 To .VisibleRows - 1 Load ChkboxArray(i) Next For i = .VisibleRows To ChkboxArray.UBound Unload ChkboxArray(i) Next 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.)