Edit or Add Record to a MSHFLexGrid

Posted on 2000-05-02
Last Modified: 2013-12-25
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.

Question by:especht

Author Comment

ID: 2771003
Here is a screen-shot and a little more explanation if it helps.


Expert Comment

ID: 2771512
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.
LVL 14

Accepted Solution

wsh2 earned 75 total points
ID: 2771814
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.
Technology Partners: 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!


Author Comment

ID: 2773554

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

LVL 14

Expert Comment

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


Author Comment

ID: 2775682
Thank you! And thanks all for their time and input.


Expert Comment

ID: 2978560

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question