Using the FlexGrid control to manage your data

Using the FlexGrid Control to Manage your Data. (2/3)

Populating the FlexGrid

There are two ways to populate Flexgrids: through data-binding and manually.

The data-bound FlexGrid is the easiest but the least flexible approach. Add a Data Control to your form, set its DataBaseName property to an appropriate database and its RecordSource property to a table or to your required SQL query. Then set the FlexGrid's DataSource to the Data Control and when the application is run, the FlexGrid will automatically format the number of columns to the number of fields in your table or returned recordset and the number of rows to the number of records.

However if you wish to display data with greater control, e.g. blank columns and rows separating grouped data, text colour and cell colour dependent upon +ve or -ve values etc., then manually, unbound populating of the FlexGrid is the best option.

Once you have a recordset, data can be directed at any cell within the FlexGrid, which gives the developer much greater control. The following code example takes a recordset which contains two fields one which hold share prices, shareprice, and one for share movements, movement, and colour codes grid cells depending on the status of the data:

With rsExample
    .MoveLast
    'set number of grid rows and columns
    'to fit recordset
    MSFlexGrid1.Rows = .RecordCount
    MSFlexGrid1.Cols = 3
    .MoveFirst

    Do
        If !movement < 0 Then
            'reference cell to set cell text colour
            'to Red for shareprice
            MSFlexGrid1.row = .AbsolutePosition + 1
            MSFlexGrid1.col = 1
            MSFlexGrid1.CellForeColor = vbRed
            MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 1) = _
              !shareprice
            MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 2) = _
              !movement
        ElseIf !movement = 0 Then
            'reference cell to set cell background
            'colour to Yellow for shareprice
            MSFlexGrid1.row = .AbsolutePosition + 1
            MSFlexGrid1.col = 1
            MSFlexGrid1.CellBackColor = vbYellow
            MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 1) = _
              !shareprice
            MSFlexGrid.TextMatrix(.AbsolutePosition + 1, 2) = _
              !movement
        Else
            MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 1) = _
              !shareprice
            MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 2) = _
              !movement
        End If
        .MoveNext
    Loop Until .EOF
End With

Using this approach the FlexGrid can be formatted in any fashion for any recordset.

Using a Dynamic TextBox to enter Data into the FlexGrid

One disadvantage of the FlexGrid is the absence of a simple method of data entry. Date-bound FlexGrids accept data keyed directly into cells, but these amendments automatically update the recordset which populated the grid and hence the database. So again, data-binding represents a lack of flexibility.

By contrast, unbound FlexGrids do not directly accept data entry, but can accept data through the FlexGrid's KeyPress event or via a Textbox. The Textbox is the favoured method as it lends itself well to validation procedures and is camouflaged well against the FlexGrid. The trick is the set the TextBox's BorderStyle property to '0 -None' and reposition the TextBox over the cell to edited and resize it's width and height to match the cell width and height. This repositioning and resizing can be achieved in one line of code using the TextBoxes Move method in the FlexGrid's Click event or KeyPress event as follows:

Private Sub MSFlexGrid1_Click()
    With MSFlexGrid1
        txtDataEntry.Text = .TextMatrix(.row, .col)
        txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _
          .CellWidth, .CellHeight
        txtDataEntry.Visible = True
        DoEvents
        txtDataEntry.SetFocus
    End With
End Sub

Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
    Case 45, 46, 47, 48 To 57, 65 To 90, 97 To 122
        txtDataEntry.Text = Chr$(KeyAscii)
        txtDataEntry.SelStart = 1
    End Select
    With MSFlexGrid1
        txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _
          .CellWidth, .CellHeight
        txtDataEntry.Visible = True
        DoEvents
        txtDataEntry.SetFocus
    End With
End Sub

The FlexGrid Click event copies the contents of the selected cell to the TextBox ready for editing and snaps the TextBox invisibly to the cell. Each subsequent keypress is then handled by the TextBox KeyPress event which appends to the original cell text.
The FlexGrid KeyPress event operates in the same way as the Click event except no text is copied to the TextBox and new text can then be entered.

Private Sub txtDataEntry_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
    Case 45, 46, 47, 48 To 57, 65 To 90, 97 To 122
        ' These are acceptable keystrokes
    Case Else
        ' These are unacceptable, and should be ignored
        KeyAscii = 0
    End Select
End Sub

The TextBox's KeyPress event can be used to validate data entry as show above.

Once the required text is entered into the TextBox and another cell is selected or return is hit, then the changes are copied back to the FlexGrid cell and a command button is enabled to alert the user the save the new data.

Private Sub MSFlexGrid1_LeaveCell()
    Call SaveEdits    
End Sub

Private Sub MSFlexGrid1_GotFocus()
    Call SaveEdits    
End Sub

Private Sub SaveEdits()
    If Not txtDataEntry.Visible Then Exit Sub
    MSFlexGrid1.TextMatrix(MSFlexGrid1.row, MSFlexGrid1.col) = _
      txtDataEntry.Text
    txtDataEntry.Visible = False
    'enable Save cmdButton to indicate new data to be saved
    cmdSave.Enable = True    
End Sub

With these six procedures above, data entry and validation will be enabled for the FlexGrid.
The txtDataEntry TextBox used with the FlexGrid should appear as shown in Figure 4.

Figure 4: TextBox used for Data Entry with the FlexGrid

Using Arrays behind FlexGrids

Most of the time, holding data in FlexGrid is more then satisfactory, but occasions arise where a temporary datastore behind the FlexGrid is required. Consider the situation where numeric values are displayed in the FlexGrid, say millions of pounds/dollars. While you would like the grid to hold these values right down to pence/cents, you only want to display say 3 most significant figures. That is, the grid show £2.02m, but holds £2,019,995.52. For convenience, when the cell is clicked to edit the value, the full value should appear, then return to 3 s.f.'s after amendment, rather like MS Excel.
This is only possible with an array as an intermediate data store between the data source and the FlexGrid. The array should be declared as a datatype appropriate to that of the data intended to be stored within it and dimension to the size of the FlexGrid. Once the data is loaded into the array, the array's contents can be transferred to the FlexGrid using the Format function (or Round function in VB6) to truncate values. Likewise edited values should be retrieved from the array to the txtDataEntry TextBox in the example above, and returned to the array upon completion of editing. A function should be called to dump the array to the FlexGrid upon data retrieval or amendment.
Saving the FlexGrids data should then be a data dump from the array to the database or other data source.

Adding Totals to FlexGrids

Arithmetic operations upon numeric data in FlexGrids can be handled in 2 ways:

by directly addressing individual grid cells, shown in the code below

    Dim intRow As Integer
    Dim dblTotal As Double

    With MSFlexGrid1
        For intRow = 1 To 5
            dblTotal = dblTotal + _
              Format(.TextMatrix(intRow, 1), "General Number")
        Next intRow

        .TextMatrix(7, 1) = Format(dblTotal, "£#,##0.00")
    End With

This code sums to values in the first 5 rows of the first column in the FlexGrid and places the total in the row 7, column 1 in a monetary format.

by addressing array elements, shown in the code below

    Dim intArrayRow As Integer
    Dim dblTotal As Double
    Dim arrGridData() as Double

    ReDim arrGridData(10, 10)

    For intArrayRow = 1 To 5
        dblTotal = dblTotal + arrGridData(intArrayRow , 1)
    Next intArrayRow 

    arrGridData(7, 1) = dblTotal 

    Call subArray2FlxGrd

Again this example sums the first 5 rows and writes them the the seventh row in the first column of the array. The advantage of the array is the data is held as the correct datatype and arrays are faster in terms of read/write access than FlexGrids. Remember to include a call to the procedure to transfer the array data to the FlexGrid.

Trapping Restricted Cell Rows and Columns

Using the code examples above for the FlexGrid events we can use addition code to restrict data entry to specific cells or regions of the FlexGrid. Here data entry is only permitted in row 6 and between columns 4 and 8.

Private Sub MSFlexGrid1_Click()
    With MSFlexGrid1
        If (.col < 4 or .col > 8) Or (.row <> 6) then Exit Sub
        txtDataEntry.Text = .TextMatrix(.row, .col)
        txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _
          .CellWidth, .CellHeight
        txtDataEntry.Visible = True
        DoEvents
        txtDataEntry.SetFocus
    End With
End Sub

Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
    If (.col < 4 or .col > 8) Or (.row <> 6) then Exit Sub
    Select Case KeyAscii
    Case 45, 46, 47, 48 To 57, 65 To 90, 97 To 122
        txtDataEntry.Text = Chr$(KeyAscii)
        txtDataEntry.SelStart = 1
    End Select
    With MSFlexGrid1
        txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _
          .CellWidth, .CellHeight
        txtDataEntry.Visible = True
        DoEvents
        txtDataEntry.SetFocus
    End With
End Sub

On a related topic of trapping keypresses, certain keypresses can be useful, such as arrow keypresses to navigate around the FlexGrid, & Return and Escape keypresses. These can be trapped in the TextBox KeyDown event (see below) which fires before the TextBox KeyPress event coded above, and processed to carried out their required action. The Return keypress commits the TextBox contents to the FlexGrid cell, whereas Escape rolls back to the original contents of the FlexGrid cell.

Private Sub txtDataEntry_KeyDown(KeyCode As Integer, _
  Shift As Integer)
    'calls subroutine below
    Call EditKeyCode(MSFlexGrid1, txtDataEntry, KeyCode, Shift)
End Sub

Private Sub EditKeyCode(grdGrid As MSFlexGrid, _
  txtEdit As TextBox, KeyCode As Integer, Shift As Integer)
Select Case KeyCode
    Case 27 'ESC
        txtEdit = ""
        txtEdit.Visible = False
        grdGrid.SetFocus
    Case 13 'Return
        grdGrid.SetFocus
    Case 37 'Left Arrow
        grdGrid.SetFocus
        DoEvents
        If grdGrid.col > grdGrid.FixedCols Then
            grdGrid.col = grdGrid.col - 1
        End If
    Case 38 'Up Arrow
        grdGrid.SetFocus
        DoEvents
        If grdGrid.row > grdGrid.FixedRows Then
            grdGrid.row = grdGrid.row - 1
        End If
    Case 39 'Right Arrow
        grdGrid.SetFocus
        DoEvents
        If grdGrid.col < grdGrid.Cols - 1 Then
            grdGrid.col = grdGrid.col + 1
        End If
    Case 40 'Down Arrow
        grdGrid.SetFocus
        DoEvents
        If grdGrid.row < grdGrid.Rows - 1 Then
            grdGrid.row = grdGrid.row + 1
        End If
    End Select
End Sub

Continue with FlexGrid Article 3.