Table or Grid control

[mmforrester]

Is there a control that allows the user to enter data into a scrollable table. The MSFlexGrid control looks okay, but I can't seem to enter any data into it at run time. I can't see a property assocaited with this such as Editable, or Locked, so why can't I enter any data (Enabled is set to true). I can see that if I had other data entry fields and then a button which added and deleted items it would work, but really I just want the user to enter data directlry into the table. Maybe I am using the wrong control for what I want to do - please help!!!
Margaret

[Dr_Michael]

Add a DBGrid to your form, set it's property to unbound mode and add this code:

' for simplicity sake, i use an array called HY to store
' the data for the grid.
private Const MAXCOLS = 9 ' Maximum number of fields in record set.

' When the user clicks on the Add icon, this subroutine adds a new
' row to the RowBuf variable and a bookmark to the NewRowBookmark
' variable
private Sub DBGrid1_UnboundAddData(byval RowBuf as RowBuffer, NewRowBookmark as Variant)
Dim iCol as Integer
mTotalRows = mTotalRows + 1
ReDim Preserve HY(MAXCOLS - 1, mTotalRows - 1)
NewRowBookmark = mTotalRows - 1 'Sets the bookmark to the last row.
' The following loop adds a new record to the database.
for iCol = 0 to UBound(HY, 1)
If Not IsNull(RowBuf.Value(0, iCol)) then
HY(iCol, mTotalRows - 1) = RowBuf.Value(0, iCol)
else
' If no value set for column, then use the DefaultValue
HY(iCol, mTotalRows - 1) = DBGrid1.Columns(iCol).DefaultValue
End If
next iCol
End Sub

' This subroutine deletes a row based on it's bookmark.
private Sub DBGrid1_UnboundDeleteRow(Bookmark as Variant)
Dim iCol as Integer, iRow as Integer
' Move all rows above the deleted row down in the
' array.
for iRow = Bookmark + 1 to mTotalRows - 1
for iCol = 0 to MAXCOLS - 1
HY(iCol, iRow - 1) = HY(iCol, iRow)
next iCol
next iRow
mTotalRows = mTotalRows - 1
End Sub

' This subroutine is called, any time the DBGrid wants to display
' new data.
private Sub DBGrid1_UnboundReadData(byval RowBuf as RowBuffer, StartLocation as Variant, byval ReadPriorRows as Boolean)
Dim CurRow&, iRow as Integer, iCol as Integer, iRowsFetched as Integer, iIncr as Integer
' DBGrid is requesting rows so give them to it
If ReadPriorRows then
iIncr = -1
else
iIncr = 1
End If
' If StartLocation is null then start reading at the end
' or beginning of the data set.
If IsNull(StartLocation) then
If ReadPriorRows then
CurRow& = RowBuf.RowCount - 1
else
CurRow& = 0
End If
else
' Find the position to start reading based on the
' StartLocation bookmark and the iIncr variable
CurRow& = CLng(StartLocation) + iIncr
End If
' Transfer data from our data set array to the RowBuf object
' which DBGrid uses to display the data
for iRow = 0 to RowBuf.RowCount - 1
If CurRow& < 0 Or CurRow& >= mTotalRows& then Exit for
for iCol = 0 to UBound(HY, 1)
RowBuf.Value(iRow, iCol) = HY(iCol, CurRow&)
next iCol
' set bookmark using CurRow& which is also our
' array index
RowBuf.Bookmark(iRow) = CStr(CurRow&)
CurRow& = CurRow& + iIncr
iRowsFetched = iRowsFetched + 1
next iRow
RowBuf.RowCount = iRowsFetched
End Sub

' This subroutine updates the data in the array after it has
' been edited.
private Sub DBGrid1_UnboundWriteData(byval RowBuf as RowBuffer, WriteLocation as Variant)
Dim iCol as Integer
' Data is being updated
' Update each column in the data set array
for iCol = 0 to MAXCOLS - 1
If Not IsNull(RowBuf.Value(0, iCol)) then
HY(iCol, WriteLocation) = RowBuf.Value(0, iCol)
End If
next iCol
End Sub

private Sub Form_Load()

' 9 Columns, 10 Rows of Data
ReDim HY(0 to 8, 0 to 9)

'run test
'column 0
HY(0, 0) = 0.5
HY(0, 1) = 1
HY(0, 2) = 2
HY(0, 3) = 5
HY(0, 4) = 14
HY(0, 5) = 30
HY(0, 6) = 60
HY(0, 7) = 120
HY(0, 8) = 480
HY(0, 9) = 1440

'column 1
Dim j as Integer
for j = 0 to 9
HY(1, j) = 21
next j

'column 2
HY(2, 0) = 36
HY(2, 1) = 32.5
HY(2, 2) = 29
HY(2, 3) = 24
HY(2, 4) = 20.2
HY(2, 5) = 17.3
HY(2, 6) = 15
HY(2, 7) = 13.2
HY(2, 8) = 10.7
HY(2, 9) = 9
'end of run test

mTotalRows& = 10

Dim oldcnt as Integer, newcnt as Integer

me.Show
oldcnt = DBGrid1.Columns.Count
newcnt = 0
Dim i as Integer

' Remove old columns
for i = DBGrid1.Columns.Count - 1 to 0 step -1
DBGrid1.Columns.Remove i
next i

' Add new columns
for i = 0 to 2
DBGrid1.Columns.Add newcnt
DBGrid1.Columns(newcnt).Caption = "Col " & newcnt
DBGrid1.Columns(newcnt).Visible = true
newcnt = newcnt + 1
next i
End Sub




Michael Vlastos
Automation Engineer
Company Modus SA
Development Department

[mmforrester]

Hello Michael

thanks for your help on this. How would I insert a row in this table. It seems to happen automatically when I type a value into the last row, but what if a user wanted to insert a new row half way down the table.
Any help would be most gratefully received.

Regards
Margaret
P.S. Deleting a row seems to work okay - I just highlight the row and press delete and it disappears!

[Dr_Michael]

Still working on that... :-(
If I find a way, I will let you know...
You followed the right way for deleting. ;-)
P.S. Did you like the code???

Michael Vlastos
Automation Engineer
Company Modus SA
Development Department

[mmforrester]

thanks Michael

yes, I did like the code. I've been trying to do the same thing with a FlexGrid control, but think I prefer the look of the DBGrid. I find it hard to believe that Microsoft don't have a control that automatically does these things for you.
Margaret

[Dr_Michael]

I couldn't even believe it but...
I have heard that there is a dbgrid control named true dbgrid control made from Apex Software which has a lot of goods... I was ready to buy it but I found the way to solve my problem. If you want something more than this I gave you, maybe it's a good idea to search for Apex...

Michael Vlastos
Automation Engineer
Company Modus SA
Development Department
Greece, Athens

[Ravi Kiran]

Hi,

MSFlexGrid doesn't allow editing directly. But it can be done. It Involves placing a textbox control on top and all..I am sure there are a couple of articles here on Codeguru and a few on MSDN also.

Search for them. if you cant find them, then tell me i will mail some code... which may not completely do what you want ofcourse.

Right away, i cannot think any way of inserting a new record in the middle, w/o extra btn(s) click!
New record at the end can be done easily, though.


Grid control in umnound mode, as the other reply suggests is also good. Use it if you dont want color effects and other frills that come with FlexGrid!!


RK

[mmforrester]

Thanks Ravi

you've confirmed what I seem to be finding!! My problem is that I thought Microsoft would have a control that does these things for us as I can't imagine I'm the only person who's tried to do this!! Thanks for your help.
Margaret

I'll forwarn you that I've had some trouble with stability with the True DBGrid from Apex, especially in the unbound mode. They also have some classes available on their web site which make handling the unbound mode easier, but I also found them to be unstable. I couldn't pinpoint the problem, and sometimes they would work great, but I ended up not using them at all.

Ericka Haas
[email protected]

[Dr_Michael]

Thanx for the advice! I also do prefer my own coding... ;-)

Michael Vlastos
Automation Engineer
Company Modus SA
Development Department
Greece, Athens