MSFlexGrid edit and update to recordset
Jamie
I am performing the following task:
1. Make an MSFlexGrid editable
2. When the allowed cell is changed, then update a new recordset (or if
lower overhead, update existing recordset used to populate the Grid).
The completed parts:
MSFlexGrid is editable (with exception of primary keys).
The Delema:
On the same row as the cell that was changed, I cannot grab the value or
text of the cell of the primary key(s) for that row from the MSFlexGrid. I
require this data to call my query (through the DataEnvironment) and create
a new recordset to be updated with the changes.
Please Help...
Thank you
Ken Halter
of the grid with that...
"Jamie" <apj...@hotmail.com> wrote in message
news:#h2#WpbTBHA.380@tkmsftngp03...
Keith Lubell
Frank Sommer
Microsoft Regional Director, Northern California
October 1997
From Regional Director Magazine
Introduction
Microsoft® Visual Basic® version 5.0 comes with not one, not two, but three
grid controls in the box. The three grids are Grid, DBGrid, and MSFlexGrid.
While I'm tempted to say it took them three tries to get it right, the truth
is that there are good reasons for the inclusion of all three. After briefly
exploring these reasons, and when to use each grid, this article will take
an in-depth look at the newest of the three: MSFlexGrid.
Grid is the original Visual Basic grid. It was shipped with the Professional
Toolkit for Visual Basic 1.0 back in 1991. DBGrid is a version of the Apex
TrueGrid control, and was first shipped with Visual Basic 4.0 in 1995. The
newest of the three is MSFlexGrid, a version of the VideoSoft VSFLEX
control, and was shipped with Visual Basic 5.0.
In the two-grid world of Grid and DBGrid, Grid was favored for its
simplicity, and at one-sixth the size of DBGrid, its small footprint. While
you will occasionally find a fan of this minimalist grid, for the most part
Grid has been ignored by programmers. Its presence in today's toolbox is
largely justifiable only for backwards compatibility. When MSFlexGrid is
added to the mix, even this reason goes away, as MSFlexGrid supports nearly
all of the properties, methods and events of Grid. At only twice the size,
its greatly enhanced feature set makes an excellent replacement for the Grid
control in existing projects.
The real question as to which grid to use boils down to a choice between
DBGrid and MSFlexGrid. Both grids are full-featured and highly customizable.
There are a few obvious points of departure. If you need full data binding
choose DBGrid, as the binding in MSFlexGrid is read-only binding. On the
other hand, if you need cell merging or data pivoting, MSFlexGrid is your
only choice. Beyond that, each grid has its own look and feel, though
MSFlexGrid has a clear advantage in its smaller size.
MSFlexGrid is very flexible (hence its name) in the ways you can use it.
What follows is a discussion, with some code examples, of various techniques
you can use with MSFlexGrid.
Getting Data into the Grid
There are numerous ways to get data into the MSFlexGrid. There is no
"officially" preferred method or clear speed differences, so the proper one
to use depends on the source of the data and personal taste. For data
contained in a database, the obvious choice is to simply bind the grid to a
Data control. Once you set the DatabaseName and RecordSource properties of
the Data control, simply use the DataSource property of the MSFlexGrid to
bind the grid to the Data control. The grid will populate automatically with
data, one row for each record in the database. The first row will contain
column headings derived from the field names in the database. If you don't
want the automatically created header row, use the RemoveItem method to
delete it. Since MSFlexGrid won't allow you to remove a header (fixed) row,
you will have to first set the FixedRows property to zero.
In its unbound mode, MSFlexGrid is a data container: you put the data in
once, and it remains there for the life of the application. There are four
properties and one method to help you do this: the AddItem method, and the
Clip, Text, TextArray, and TextMatrix properties.
If you are using the grid as a listbox, or prefer to think of it as a
listbox, use the AddItem method to populate it. The following code will
create a two-column grid with the letters A through Z, and their ASCII
codes.
MSFlexGrid1.Rows = 0
For I = Asc("A") To Asc("Z")
MyData = Chr$(I) & vbTab & I
MSFlexGrid1.AddItem MyData
Next
The AddItem method will add 26 rows to those already in the grid. We set the
rows property to zero before starting so that the finished grid will have
only 26 rows total. Note how the embedded tab character (vbTab) is used to
separate the data for the second column. The AddItem method also has an
optional index parameter that allows you to add the row at a specific
location.
The Clip property is useful to quickly populate a grid with a range of data
from any data source that has a compatible Clip method. An rdoResultset is
just such a source, and the GetClipString method does the trick. The code
sample below will take the first four rows of the Customers table and copy
them into rows five through eight of a grid:
Dim rs As rdoResultset
Set rs = MyConnection.OpenResultset("Customers")
'select the target range
MSFlexGrid1.Row = 5
MSFlexGrid1.Col = 1
MSFlexGrid1.RowSel = 8
MSFlexGrid1.ColSel = rs.rdoColumns.Count
'get the data
MSFlexGrid1.Clip = rs.GetClipString(4) 'returns 4 rows
Notice that we did not have to deal with the data at the Field level, the
Clip property did all of that for us. The Clip property returns data as well
as setting it, so you can use it to cut and paste from one area of a grid to
another.
The Text property is frequently used to set the contents of a single cell.
That cell however must be the current cell, so you must first use the Row
and Col properties to select the desired cell. To set a range of cells to
the same value, use the FillStyle property in conjunction with the Text
property. The code below puts an "X" in each cell of a grid:
MSFlexGrid1.Row = 0
MSFlexGrid1.Col = 0
MSFlexGrid1.RowSel = MSFlexGrid1.Rows - 1
MSFlexGrid1.ColSel = MSFlexGrid1.Cols - 1
MSFlexGrid1.FillStyle = flexFillRepeat
MSFlexGrid1.Text = "X"
If you don't want the current cell to change when you load data as it does
with the Text property, you should use the TextArray or TextMatrix property.
These properties set the contents of an arbitrary cell without changing the
Row or Col properties. TextMatrix addresses a cell using the familiar row
and column notation. TextArray uses an index to identify a cell. The index
is simply a zero-based enumeration of the cells from left to right, top to
bottom. In a 2 x 2 grid the following lines both address the lower
right-hand cell:
MSFlexGrid1.TextArray(3) = "X"
MSFlexGrid1.TextMatrix(1,1) = "X"
As you can see, there is a great variety of ways to place data into the
MSFlexGrid. Use the one that makes the most sense to you for the occasion.
Sorting Techniques
MSFlexGrid has a built-in sorting capability that is fairly flexible, and
also very fast. In most cases it is faster to use the built-in sorting than
it is to use an ORDER BY clause in your SQL statement. The sorting options
allow you to sort ascending or descending with a numeric sort or a string
sort. The string sort can be set to ignore case, if desired. The Sort
property specifies which type of sort should be done. The Sort property can
also be thought of as a method because the sorting action occurs as soon as
the property is set. You can sort on multiple columns to give the effect of
a multikey sort.
There are two techniques that can be used for more sorting flexibility.
Setting the Sort property to flexSortCustom causes a special event, Custom,
to fire whenever the grid is sorted. You can use this event to implement any
type of sort you wish. The second method uses a hidden column and lets the
grid do the work. Suppose you have a database field that returns a date in
the Short Date format. Neither a numeric sort nor a string sort would order
this column correctly. To sort by date you need to add an extra column to
the grid and set its width to zero. Populate the column with values obtained
by converting the date to a number, and sort on that column as demonstrated
below (assumes the date field is in column 2):
Dim Ro As Integer
Dim SortCol As Integer
Dim SortDate As Double
'add a column to hold the sort key
MSFlexGrid1.Cols = MSFlexGrid1.Cols + 1
SortCol = MSFlexGrid1.Cols - 1
MSFlexGrid1.ColWidth(SortCol) = 0 'invisible
'calculate key values & populate grid
For Ro = 1 To MSFlexGrid1.Rows - 1
SortDate = DateValue(MSFlexGrid1.TextMatrix(Ro, 2))
MSFlexGrid1.TextMatrix(Ro, SortCol) = SortDate
Next Ro
'do the sort
MSFlexGrid1.Col = SortCol 'set the key
MSFlexGrid1.Sort = flexSortNumericAscending
Editing Grid Data
MSFlexGrid does not have a built-in cell editing capability, but it provides
the hooks to make it easy for you to add that capability programmatically.
The advantage of this approach is that you can tailor editing behavior to
your taste. The basic technique involves smoke and mirrors: the editing
occurs not in MSFlexGrid at all, but in a standard Textbox control that is
positioned precisely over the cell being edited.
In this example, we will give the user two ways to get into the edit mode,
either by double-clicking on a cell, or by simply starting to type in the
current cell. The following two routines implement this:
Private Sub MSFlexGrid1_DblClick()
GridEdit Asc(" ")
End Sub
Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
GridEdit KeyAscii
End Sub
In each case we call a grid edit subroutine and pass it a keystroke. In the
case of double-clicking, we pass the space character as a flag. The GridEdit
routine initializes the edit box and moves it into position:
Sub GridEdit(KeyAscii As Integer)
'use correct font
Text1.FontName = MSFlexGrid1.FontName
Text1.FontSize = MSFlexGrid1.FontSize
Select Case KeyAscii
Case 0 To Asc(" ")
Text1 = MSFlexGrid1
Text1.SelStart = 1000
Case Else
Text1 = Chr(KeyAscii)
Text1.SelStart = 1
End Select
'position the edit box
Text1.Left = MSFlexGrid1.CellLeft + MSFlexGrid1.Left
Text1.Top = MSFlexGrid1.CellTop + MSFlexGrid1.Top
Text1.Width = MSFlexGrid1.CellWidth
Text1.Height = MSFlexGrid1.CellHeight
Text1.Visible = True
Text1.SetFocus
End Sub
For demonstration purposes, the Case statement in the GridEdit routine shows
two different behaviors when entering the edit mode. In practice you would
probably only use one of them, or a different one of your own creation. If
the edit mode is entered by virtue of a double-click or a control key press,
we copy the contents of the grid cell to the exit box and place the cursor
at the end of the string. If the edit mode is entered by pressing a normal
key, we ignore the original cell contents and insert the pressed key into
the edit box. The positioning of the exit box could be done on one line with
the Move method. Here we have used four lines so that it reads more easily
in this article. Notice that MSFlexGrid conveniently gives us all the
coordinate information we need.
Next, we need a couple of routines that handle housekeeping when the user
moves to a different cell or moves focus back to the grid from another
control. The LeaveCell event is also the place where you would put any data
validation code that might be applicable.
Private Sub MSFlexGrid1_LeaveCell()
If Text1.Visible Then
MSFlexGrid1 = Text1
Text1.Visible = False
End If
End Sub
Private Sub MSFlexGrid1_GotFocus()
If Text1.Visible Then
MSFlexGrid1 = Text1
Text1.Visible = False
End If
End Sub
Next we place some navigation code in the KeyDown event of the edit box so
that, for instance, the user can leave the edit mode by pressing ESC, and
move to a different row by pressing an arrow key:
Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case vbKeyEscape
Text1.Visible = False
MSFlexGrid1.SetFocus
Case vbKeyReturn
MSFlexGrid1.SetFocus
Case vbKeyDown
MSFlexGrid1.SetFocus
DoEvents
If MSFlexGrid1.Row < MSFlexGrid1.Rows - 1 Then
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
End If
Case vbKeyUp
MSFlexGrid1.SetFocus
DoEvents
If MSFlexGrid1.Row > MSFlexGrid1.FixedRows Then
MSFlexGrid1.Row = MSFlexGrid1.Row - 1
End If
End Select
End Sub
Finally we need a line of code to suppress the Beep that occurs when ENTER
is pressed in a Textbox:
Private Sub Text1_KeyPress(KeyAscii As Integer)
'noise suppression
If KeyAscii = vbKeyReturn Then KeyAscii = 0
End Sub
In order for the edit box to merge seamlessly into the grid, you need to set
several Textbox properties at design-time: set Appearance = 0 (flat), and
BorderStyle = 0 (none). Also set Visible = False so that the edit box is not
initially visible. To really fine-tune this code, the edit box needs a
slight additional offset to the southeast (with a corresponding reduction in
size) so that the text in it lines up exactly with the text in the cell
beneath. You would probably also want to write some code behind the scroll
event of the grid since clicking on the grid's scroll bar will not cause the
edit box to loose focus.
Note that this technique is not limited to using a Textbox as your edit box.
You could modify the sample code to use a ComboBox, a CheckBox, or even a
calendar control for editing, based on the column being edited.
MSFlexGrid is a very flexible control indeed, and this article just touches
on some of the things you can do with it. As you gain familiarity with it,
it will become a more regular part of your toolbox. Cell merging and
pivoting are two more unique features of the MSFlexGrid that give it
tremendous power and bear investigation.
----------------------------------------------------------------------------
----
Send feedback to MSDN.Look here for MSDN Online resources.
"Jamie" <apj...@hotmail.com> wrote in message
news:#h2#WpbTBHA.380@tkmsftngp03...