MSFlexgrid, data environment

Thanks to szlamany for all the help

Hey all,

I havn't used MSFlexgrid before so i am just wondering how exactly to insert the data from a table using data environment into a MSFlexgrid. I would like it to put in the column names as well and possibly auto adjust the number of columns and rows depending on the number of entries. Any help would be much appreciated!

Hope you all had a lovely christmas :)

Thanks very much

Open attachments for a sampe project.

Forgot to mention: you will need a Northwind database in the application folder (it comes with VB and MS Access: NWIND.MDB) - I couldn't add one to the zip as file size went over the limit.

We use ADO to get a RS from a STORED PROCEDURE in MS SQL SERVER 2000.

This is a loop we use to build a FLEX GRID from that RECORDSET.

I did a lot of cut/paste to put this together - hope it still runs (it came from a much bigger routine).

Code:

---

For z = 0 To rsInquire.Fields.Count - 1    ' Lets look at our column headings first
   
    strEle = rsInquire.Fields(z).Name      ' Column heading
   
    Select Case rsInquire.Fields(z).Type    ' Deal with data type and column size
        Case adTinyInt
            lngSize = 4
        Case adUnsignedTinyInt
            lngSize = 4
        Case adInteger
            lngSize = 12
        Case adUnsignedInt
            lngSize = 12
        Case adCurrency
            lngSize = 13
        Case Else
            lngSize = rsInquire.Fields(z).DefinedSize
    End Select
   
    With flxInput(lngGrid)                  ' Build the heading format string
        If .FormatString <> "" Then .FormatString = .FormatString & "|"
        If Left(strEle, 1) <> "^" And Left(strEle, 1) <> ">" Then strEle = "<" & strEle
        .FormatString = .FormatString & strEle
        .ColData(.Cols - 1) = lngSize
    End With
Next z

Do While rsInquire.EOF = False              ' Loop through the recordset now
    lngRecCnt = lngRecCnt + 1
    For z = 0 To rsInquire.Fields.Count - 1
        strWhat = rsInquire(z) & ""            ' The data element for this row/column
        If strGridLine <> "" Then strGridLine = strGridLine & vbTab
        strGridLine = strGridLine & strWhat
    Next z
    flxInput(lngGrid).AddItem strGridLine
    strGridLine = ""
    rsInquire.MoveNext
Loop

---

szlamany, thanks for the help but i am having a problem working out what your variables are for. Such as: With flxInput(lngGrid)

Thanks bull, i am looking through your zip right now.

flxInput is simply the name of the FLEX GRID. We use a control array of flex grids on a screen - so lngGrid is the "index" of the flex grid control array.

rsInquire is the name of the recordset.

Anything starting with "str" is a string - "lng" is a long.

Any place you find x, y or z they are simply long temporary variables for looping.

Hm i cant seem to get it to work.. still the same problem with With MSFlexGrid1(lngGrid) i edited the code...

my flex grid is called MSFlexGrid1
rs1 is the recordset

maybe i have missed some declarations?

code:
rs1.Open
For Z = 0 To rs1.Fields.Count - 1 ' Lets look at our column headings first

strEle = rs1.Fields(Z).Name ' Column heading

Select Case rs1.Fields(Z).Type ' Deal with data type and column size
Case adTinyInt
lngSize = 4
Case adUnsignedTinyInt
lngSize = 4
Case adInteger
lngSize = 12
Case adUnsignedInt
lngSize = 12
Case adCurrency
lngSize = 13
Case Else
lngSize = rs1.Fields(Z).DefinedSize
End Select

With MSFlexGrid1(lngGrid) ' Build the heading format string
If .FormatString <> "" Then .FormatString = .FormatString & "|"
If Left(strEle, 1) <> "^" And Left(strEle, 1) <> ">" Then strEle = "<" & strEle
.FormatString = .FormatString & strEle
.ColData(.Cols - 1) = lngSize
End With
Next Z

Do While rs1.EOF = False ' Loop through the recordset now
lngRecCnt = lngRecCnt + 1
For Z = 0 To rs1.Fields.Count - 1
strWhat = rs1(Z) & "" ' The data element for this row/column
If strGridLine <> "" Then strGridLine = strGridLine & vbTab
strGridLine = strGridLine & strWhat
Next Z
MSFlexGrid1(lngGrid).AddItem strGridLine
strGridLine = ""
rs1.MoveNext
Loop

Get rid of the (lngGrid) - replace my "flxInput(lngGrid)" with "MSFlexGrid1" - you do not have a control array of multiple flex grids like I do...

oh wow thanks very much i owe u one :) works like a charm.

One more thing.... Its simple but, how do i add a drop down menu like 'File'

again, thanks for the help :)

You are very welcome.

I believe the folks here like it if you edit your first post and put the CHECK MARK for RESOLVED in it...

Not sure what you mean by FILE MENU - but I would recommend you start another thread with that question - don't want it buried in this question.