Solved

Edit or Add Record to a MSHFLexGrid

Posted on 2000-05-02
7
2,169 Views
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.

especht
0
Comment
Question by:especht
7 Comments
 

Author Comment

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

http://www.geocities.com/jvwd/screen_shot.html

TIA,
especht
0
 
LVL 6

Expert Comment

by:Marine
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
    Combo3.Clear
    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.
0
 
LVL 14

Accepted Solution

by:
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
   Next
   For i = Fg2.FixedCols To Fg2.Cols - 1
      Fg2.TextArray(fgi(0, i)) = i
   Next

   ' 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.
   Edt.SetFocus
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
      MSHFlexGrid.SetFocus

   Case 13   ' ENTER return focus to MSHFlexGrid.
      MSHFlexGrid.SetFocus

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

   Case 40      ' Down.
      MSHFlexGrid.SetFocus
      DoEvents
      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.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:especht
ID: 2773554
wsh2,

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

especht
0
 
LVL 14

Expert Comment

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


0
 

Author Comment

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

especht
0
 
LVL 2

Expert Comment

by:mflam
ID: 2978560
thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now