• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2258
  • Last Modified:

Edit or Add Record to a MSHFLexGrid

I currently have a form that has fields, plus a mshflexgrid using VB 6. The fields are controlled by one ado recordsource and the mshflexgrid has another ado recordsource that is based on current values in the fields. (For example, the first field is a text box that holds the id of 1; therefore, the flexgrid displays the record(s) in that source which are equal to 1.

Here is my problem
How can I add a new value or edit a value on the mshflexgrid? Currently when I click on the flexgrid, it just highlights the current cell.

I hope I have provided enough information. Please let me know if you need anything specific.

1 Solution
espechtAuthor Commented:
Here is a screen-shot and a little more explanation if it helps.


The Problem is that MsFlexGrid is Read-Only. There are ways to get around this and to edit your data. One way you can do is this. In the keydown event of FlexGrid you can write code that will enter new data into that cell , this will require you additional processing for charecters like backspace key and others so they won't be represented in another way. And after you enter that data you would issue and update of the recordset or execute an sql command or you can have a floating textbox which you can assign a value to. Then Update The Grid. I'll post the code for that.
With FlexGrid
If .MouseRow = 0 Then Exit Sub
    iRow = .Row: iCol = .Col
    Combo3.Left = .CellLeft + .Left
    Combo3.Top = .CellTop + .Top
    Combo3.Width = .CellWidth
    Combo3.Visible = True
end With
now you have that value in a textbox all you have to do is issue and update.
From MSDN:
Editing Cells in a Hierarchical FlexGrid Spreadsheet

In this scenario, you can edit cells in a Hierarchical FlexGrid spreadsheet. This scenario illustrates some of the capabilities of the Hierarchical FlexGrid's control events and containers, and shows how this control can be used to create a spreadsheet with in-cell editing using standard Visual Basic controls.

Note   The sample application (Flex.vbp) demonstrates the functionality necessary for navigating around and selecting ranges of cells.

To create this data display

Create Hierarchical FlexGrid and TextBox controls.

Set the properties of these controls.

Add row and column headings to the Hierarchical FlexGrid.

Add in-cell editing to the Hierarchical FlexGrid.

Add functionality to the TextBox control (the "edit" box) for updating the data.

Copy the data from the text box to the Hierarchical FlexGrid.

To complete the scenario, follow the procedures in this section in the order shown.

Creating the Controls:
Add a Hierarchical FlexGrid to your project, and then add a TextBox control to it to create a parent-child relationship, as shown:

Setting the Properties of the Controls:
Set the properties of the Hierarchical FlexGrid and TextBox controls as follows:

MSHFlexGrid control

Property Setting
Name Fg2
Cols 6
Rows 20
FillStyle 1 – Repeat
FocusRect 2 – Heavy
FontName Arial
FontSize 9

TextBox control

Property Setting
Name TxtEdit
FontName Arial
FontSize 9
BorderSize 0 – None
Visible False

Editing Cells in a Spreadsheet:
Use the following procedures to edit cells in your Hierarchical FlexGrid.

To edit cells in a spreadsheet

Modify your Hierarchical FlexGrid, so it resembles a spreadsheet, by adding the following code to the Form_Load procedure in the Code Editor window:
Sub Form_Load ()
   Dim i As Integer

   ' Make first column narrow.
   Fg2.ColWidth(0) = Fg2.ColWidth(0) / 2
   Fg2.ColAlignment(0) = 1   ' Center center.

   ' Label rows and columns.
   For i = Fg2.FixedRows To Fg2.Rows - 1
      Fg2.TextArray(fgi(i, 0)) = i
   For i = Fg2.FixedCols To Fg2.Cols - 1
      Fg2.TextArray(fgi(0, i)) = i

   ' Initialize edit box (so it loads now).
   txtEdit = ""
End Sub

Create a function to calculate an index for the TextArray property as follows:

Function Fgi (r As Integer, c As Integer) As Integer
   Fgi = c + Fg2.Cols * r
End Function

Add the following code to the Hierarchical FlexGrid's KeyPress and DblClick events:

Sub Fg2_KeyPress (KeyAscii As Integer)
   MSHFlexGridEdit Fg2, txtEdit, KeyAscii
End Sub

Sub Fg2_DblClick ()
   MSHFlexGridEdit Fg2, txtEdit, 32 ' Simulate a space.
End Sub

Add the following procedure to initialize the text box and pass the focus from the Hierarchical FlexGrid to the TextBox control:

Sub MSHFlexGridEdit (MSHFlexGrid As Control, _
Edt As Control, KeyAscii As Integer)

   ' Use the character that was typed.
   Select Case keyascii

   ' A space means edit the current text.
   Case 0 To 32
      Edt = MSHFlexGrid
      Edt.SelStart = 1000

   ' Anything else means replace the current text.
   Case Else
      Edt = Chr(keyascii)
      Edt.SelStart = 1
   End Select

   ' Show Edt at the right place.
   Edt.Move MSHFlexGrid.Left + MSHFlexGrid.CellLeft, _
      MSHFlexGrid.Top + MSHFlexGrid.CellTop, _
      MSHFlexGrid.CellWidth - 8, _
      MSHFlexGrid.CellHeight - 8
   Edt.Visible = True

   ' And make it work.
End Sub

Add updating data functionality to the text box by adding the following procedures to its KeyPress and DblClick events:

Sub txtEdit_KeyPress (KeyAscii As Integer)
   ' Delete returns to get rid of beep.
   If KeyAscii = Asc(vbCr) Then KeyAscii = 0
End Sub

Sub txtEdit_KeyDown (KeyCode As Integer, _
Shift As Integer)
   EditKeyCode Fg2, txtEdit, KeyCode, Shift
End Sub

Sub EditKeyCode (MSHFlexGrid As Control, Edt As _
Control, KeyCode As Integer, Shift As Integer)

   ' Standard edit control processing.
   Select Case KeyCode

   Case 27   ' ESC: hide, return focus to MSHFlexGrid.
      Edt.Visible = False

   Case 13   ' ENTER return focus to MSHFlexGrid.

   Case 38      ' Up.
      If MSHFlexGrid.Row > MSHFlexGrid.FixedRows Then
         MSHFlexGrid.Row = MSHFlexGrid.Row - 1
      End If

   Case 40      ' Down.
      If MSHFlexGrid.Row < MSHFlexGrid.Rows - 1 Then
         MSHFlexGrid.Row = MSHFlexGrid.Row + 1
      End If
   End Select
End Sub

Next, you need to instruct the Hierarchical FlexGrid what to do with the data when it is entered into the text box. The focus returns to the control after the user enters the data and either presses enter or clicks a different cell in the Hierarchical FlexGrid. Therefore, you must copy the data from the text box into the active cell; to do this, continue with the following steps.

Copy the data from the text box to the Hierarchical FlexGrid by adding the following code to the GotFocus and LeaveCell event procedures:

Sub Fg2_GotFocus ()
   If txtEdit.Visible = False Then Exit Sub
   Fg2 = txtEdit
   txtEdit.Visible = False
End Sub

Sub Fg2_LeaveCell ()
   If txtEdit.Visible = False Then Exit Sub
   Fg2 = txtEdit
   txtEdit.Visible = False
End Sub

Once the procedures in this scenario are complete, data can be entered into individual cells at run time.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

espechtAuthor Commented:

Thanks for the info. That works great, but how can I get this to work with a recordset displayed in the grid?

In the FG2_LeaveCell procedure add code to update your Recordset.. <smile>.

espechtAuthor Commented:
Thank you! And thanks all for their time and input.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now