recordset to msflexgrid

[Rina]

Hi all.

I have a problem in retrieve data from database column and display the data in msflexgrid.
The attached code below can only display the first row data in recordset.
Can anyone here help me to fix this problem?

Thanks in advance.

Private Sub FG_ShowRecordset(myFG As MSFlexGrid, myRST As ADODB.Recordset)
  Dim iField As Integer, iNofFields As Integer
  Dim lRow As Long
  
  Screen.MousePointer = vbHourglass
  
  myFG.Redraw = False
  myFG.AllowUserResizing = flexResizeColumns
  myFG.ScrollTrack = True
 
'column header
myFG.TextMatrix(0, 0) = "DocNo"
myFG.TextMatrix(0, 1) = "ItemNo"
myFG.TextMatrix(0, 2) = "Item"
myFG.TextMatrix(0, 3) = "Occurances"
  
  
With myRST
    'loop for displaying the document id in DocNo column
    'fields(0) = Id
    For x = 1 To myFG.Rows - 1
        myFG.TextMatrix(x, 0) = .Fields(0).Value
        .MoveNext
        x = x + 1
    Next
End With
 
myFG.Redraw = True
 
 Screen.MousePointer = vbNormal
  
  End Sub

[GaryMazzone]

Also where do you tell the grid how many rows you will have. I normally will perform an if not recordset.BOF and not recordset.EOF then do your loop. Also before posing the first row into the grid perform a recordset.movelast and recordset.MoverFirst and set the grid.rows to recordset.recordcount + 1.

[szlamany]

Do not set the .ROWS property of a FLEXGRID - use .ADDITEM to add a whole row at once...

See this thread:

http://www.vbforums.com/showthread.p...light=.additem

[Black__Knight]

HI,

Szlamany:

I like that additem method. Neat. There is nothing wrong with using .Rows. I would say additem would be easier to use.

Have a good one!
BK

[Rina]

Why I got this error : subscript out of range ?

With myRST
    'loop for displaying the document id in DocNo column
    'fields(0) = Id
    Do While Not .EOF
        myFG.Rows = myFG.Rows + 1
        myFG.TextMatrix(myFG.Rows, 0) = .Fields(0).Value
        .MoveNext
    Loop
End With

[Rina]

Do I need to set the first value for myFG.rows?

[szlamany]

I agree - you can increment .ROWS if you would like.

Then you have to remember how to deal with the "offset-of-0" issue and the FIXED rows issues.

Doing a .MOVELAST which will cause the entire recordset to be transferred to the client side, just to find out the row count is wasteful.

Since we use forward-only-read-only recordsets, that's not possible anyway.

[ooOOJaVaOOoo]

Set rs = New ADODB.Recordset
 
myFG.ColHeaderCaption(0, 0) = "DocNo"
myFG.ColHeaderCaption(0, 1) = "ItemNo"
myFG.ColHeaderCaption(0, 2) = "Item"
myFG.ColHeaderCaption(0, 3) = "Occurances"
 
Do While Not .EOF
 
  myFG.AddItem rs!DocNo & vbTab & rs!ItemNo & vbTab & rs!Item & vbTab & rs!Occurances
 
rs.MoveNext
 
Loop

[szlamany]

Do I need to set the first value for myFG.rows?

Is that first row a fixed row?

Usually myFG.Rows = myFG.FixedRows-1 will set the row count to be just the header row, or no header row if you have no FIXEDROWS.

We use .FORMATSTRING to build the header...

Something like

myFG.FormatString = "Column1" & vbTab & "^Centered Col" & vbTab & ">Right Col"

[Rina]

I try this code which I put .additem syntax in the for loop.

With myRST
    'loop for displaying the document id in DocNo column
    'fields(0) = Id
    For x = 1 To myFG.Rows - 1
        myFG.TextMatrix(x, 0) = .Fields(0).Value
        .MoveNext
    Next
    myFG.AddItem strGridLine
End With

But the result is..I have to click the button everytime I want to increase the row plus display the data in .Fields(0).

Why?

[Rina]

What is vbTab for? Im sorry if this is such a silly question. Im new in vb programming so dont have much idea.

[szlamany]

It's one or the other - either use .ADDITEM, after setting .ROWS=0 (or .ROWS=.FIXEDROWS-1)

or...

use .TEXTMATRIX to fill the entire recordset.

.ADDITEM adds the row for you - all at once, and increments the row counter - all at once. You do this just like the example in the thread I posted.

[szlamany]

What is vbTab for? Im sorry if this is such a silly question. Im new in vb programming so dont have much idea.

vbTab is a constant for a TAB character - recognized by the flexgrid as a column separator.

That's how you can pass one string for the heading and one string for each row and have the flexgrid know where the columns are.

[szlamany]

Here's the commented example of loading the data rows into a flex grid from that other thread...

Dim strWhat as String, strGridLine as String
Dim z as Long

Do While rs_e.EOF = False              ' Loop through the recordset now
     For z = 0 To rs_e.Fields.Count - 1
         strWhat = rs_e(z) & ""        ' The data element for this row/column
         '  This assumes that the recordset has columns in order compared to the list you showed
         '  in your post - EnqNo, FirstName, LastName and so on
         '  Using & "" will make sure that a null value is turned into an empty string

         If strGridLine <> "" Then strGridLine = strGridLine & vbTab
         '  Add a tab character to the string - separates the columns

         strGridLine = strGridLine & strWhat
         '  Put the column onto the string

     Next z

     MSFlexGrid1.AddItem strGridLine
     '  This adds the row to the grid - all in one shot

     strGridLine = ""
     rs_e.MoveNext
Loop

[anguswalker]

There is by the way an undocumented bug with .additem for hierarchical recordsets. According to microsoft .additem should accept an optional third parameter defining the band to which the row should be added. However it does not, and it is in fact apparently impossible to use .additem (or .removeitem) with hierarchical recordsets. This has proved a major pain to me, and I've still got nowhere with resolving it.

[davem1958]

You might also consider that MSHFlexGrid, which can be data-bound using ADO. If you're grid is read-only, might be simpler.

Dave

[dglienna]

You can use the shape command to load a MSHFlexgrid.

[zubairkhan]

Here is my code , where i developed a class to accomodate data from any table or recordset. I do not say that there codes are very fine, but once u add in your project you do not have to write codes for adding in each and every program.

Option Explicit
Private m_GroupBy, m_SumOn, m_OmitZero As String
Private m_TotalCaption As Long
Private TotalFields As Long
Private m_isAppend As Boolean
Property Get FieldGroupBy() As String
    FieldGroupBy = m_GroupBy
End Property
Property Let FieldGroupBy(ByVal newGroupBy As String)
    m_GroupBy = newGroupBy
End Property
Property Get SumOnField() As String
    SumOnField = m_SumOn
End Property
Property Let SumOnField(ByVal newSumOn As String)
    m_SumOn = newSumOn
End Property
Property Get ColoumnNameToSkipRecordIfZero() As String
    ColoumnNameToSkipRecordIfZero = m_OmitZero
End Property
Property Let ColoumnNameToSkipRecordIfZero(ByVal newOmitZero As String)
    m_OmitZero = newOmitZero
End Property
Property Get TotalCaptionAtColumnNumber() As Long
    TotalCaptionAtColumnNumber = m_TotalCaption
End Property
Property Let TotalCaptionAtColumnNumber(ByVal newTotalCaption As Long)
    m_TotalCaption = newTotalCaption
End Property
Property Get isAppend() As Boolean
    isAppend = m_isAppend
End Property
Property Let isAppend(ByVal newIsAppend As Boolean)
    m_isAppend = newIsAppend
End Property
Public Sub AddinGrid(pRsName As ADODB.Recordset, FlexGrid As MSFlexGrid, Optional CloseCursor As Boolean)
    If pRsName.EOF = True Or pRsName.BOF = True Then
        MsgBox "Recordset either EOF or BOF"
        pRsName.Close
        
        Exit Sub
    End If
    If pRsName.RecordCount >= 5555 Then
        MsgBox "Too bit table to fit in FlexGrid"
        pRsName.Close
        
        Exit Sub
    End If
    FlexGrid.AllowUserResizing = flexResizeColumns
    Dim i, NewRow, FieldNumber, gFieldNumber As Long
    Dim yesGroupFound As Boolean
    Dim yesSumFound As Boolean
    Dim yesOmitFound As Boolean
    Dim LastFieldValue As Variant
    Dim mTotal As Variant
    Dim RecordSkip As Boolean
    
    TotalFields = pRsName.Fields.Count
If m_GroupBy <> "" Then
    For i = 0 To TotalFields - 1
        If UCase(pRsName.Fields(i).Name) = UCase(m_GroupBy) Then
            yesGroupFound = True
            gFieldNumber = i
            Exit For
        End If
    Next i
    If yesGroupFound = False Then
        MsgBox "Invalid field entered for grouping"
        
        Exit Sub
    End If
End If
 
If m_SumOn <> "" Then
    For i = 0 To TotalFields - 1
        If UCase(pRsName.Fields(i).Name) = UCase(m_SumOn) And (pRsName.Fields(i).Type = adDouble _
        Or pRsName.Fields(i).Type = adNumeric _
        Or pRsName.Fields(i).Type = adLongVarChar) Then
            yesSumFound = True
            FieldNumber = i
            Exit For
        End If
    Next i
    If yesSumFound = False Then
        MsgBox "Field entered for sum invalid or non numeric."
        Exit Sub
    End If
End If
If m_OmitZero <> "" Then
    For i = 0 To TotalFields - 1
        If UCase(pRsName.Fields(i).Name) = UCase(m_OmitZero) And (pRsName.Fields(i).Type = adDouble _
        Or pRsName.Fields(i).Type = adNumeric _
        Or pRsName.Fields(i).Type = adLongVarChar) Then
            yesOmitFound = True
            Exit For
        End If
    Next i
    If yesOmitFound = False Then
        MsgBox "Field entered for Omit zero value is invalid or non numeric."
        Exit Sub
    End If
End If
        
    i = 0
    FlexGrid.Cols = TotalFields
    If m_isAppend = False Then
        FlexGrid.Rows = 2
    End If
    FlexGrid.RowHeight(0) = 400
   
With FlexGrid
 
        For i = 0 To TotalFields - 1
        If Not IsNull(pRsName.Fields(i)) Then
            .CellFontBold = True
            .TextMatrix(0, i) = UCase(pRsName.Fields(i).Name)
        End If
        Next i
        i = 0
    pRsName.MoveFirst
    If m_GroupBy <> "" Then
        LastFieldValue = pRsName.Fields("" & m_GroupBy & "")
    End If
    
    If m_isAppend = False Then
        NewRow = 0
    End If
    
    Do While pRsName.EOF = False
        If m_OmitZero <> "" Then
            If pRsName.Fields("" & m_OmitZero & "") = 0 Then
                RecordSkip = True
            Else
                RecordSkip = False
            End If
        End If
    If RecordSkip = False Then
            If m_SumOn <> "" And m_GroupBy = "" Then
                  mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
            End If
        If m_GroupBy <> "" Then
            If LastFieldValue = pRsName.Fields("" & m_GroupBy & "").Value Then
                If m_SumOn <> "" Then
                    mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
                End If
            Else
                If NewRow > 1 Then
                    .Rows = .Rows + 1
                    NewRow = .Rows - 1
                    If m_SumOn <> "" Then
                        If m_TotalCaption > 0 Then
                            .TextMatrix(NewRow, m_TotalCaption) = "Total"
                            .Col = m_TotalCaption
                            .Row = .Rows - 1
                            .CellFontUnderline = True
                            .CellFontBold = True
                            .CellBackColor = vbWhite
                            .CellForeColor = vbMagenta
                        Else
                            .TextMatrix(NewRow, 1) = "Total"
                            
                        End If
                        .TextMatrix(NewRow, FieldNumber) = Format(Round(mTotal, 0), "#######.00")
                        mTotal = 0
                        mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
                    End If
                    .Col = gFieldNumber
                    .Row = .Rows - 1
                    .CellBackColor = vbWhite
                    
                    .Col = TotalFields - 1
                    '.Cols = TotalFields
                    .Row = .Rows - 1
                    .ColSel = FieldNumber
                    .FillStyle = flexFillRepeat
                    .CellBackColor = vbWhite
                    .CellForeColor = vbBlue
                    .CellFontBold = True
                End If
            End If
        End If
    End If
    If RecordSkip = False Then
        .Rows = .Rows + 1
        NewRow = .Rows - 1
    
        For i = 0 To TotalFields - 1
        If Not IsNull(pRsName.Fields(i)) Then
            If pRsName.Fields(i).Type = adDouble Then
                .TextMatrix(NewRow, i) = Format(pRsName.Fields(i).Value, "#######.00")
            Else
               .TextMatrix(NewRow, i) = pRsName.Fields(i).Value
            End If
        Else
            .TextMatrix(NewRow, i) = ""
        End If
        Next i
        i = 0
        If m_GroupBy <> "" Then
            LastFieldValue = pRsName.Fields("" & m_GroupBy & "")
        End If
    End If
        pRsName.MoveNext
    Loop
        If mTotal > 0 And m_GroupBy = "" And m_SumOn <> "" Then
            .Rows = .Rows + 1
            NewRow = .Rows - 1
            If m_TotalCaption > 0 Then
                .TextMatrix(NewRow, m_TotalCaption) = "Total"
            Else
                .TextMatrix(NewRow, 1) = "Total"
            End If
            .TextMatrix(NewRow, FieldNumber) = Format(Round(mTotal, 0), "#######.00")
            mTotal = 0
            
            .Col = TotalFields - 1
            .Row = .Rows - 1
            .ColSel = FieldNumber
            .FillStyle = flexFillRepeat
            .CellBackColor = vbWhite
            .CellForeColor = vbBlue
            .CellFontBold = True
        End If
    If CloseCursor = True Then
        pRsName.Close
        Set pRsName = Nothing
    End If
End With
    
End Sub

add this class in your project , set it in your form from where u wand to add data in grid.

Set newAddintoGrid = New clsAddInFlexGrid
    Call newAddintoGrid.AddinGrid(AppRateRs, FxGrid)

fxgrid is that where u want to add data.

[dglienna]

Can you post your class, along with how to use the flags? It looks very interesting.

[Pradeep1210]

The flexgrid control is not data-aware. Instead replace it with a MSHFlexgrid control. You can use all the methods/properties of flexgrid in this along with the one-liner to fill it with the recordset.

Set MSHFlexGrid1.Recordset = rsData

[zubairkhan]

newAddintoGrid.ColoumnNameToSkipRecordIfZero
    newAddintoGrid.FieldGroupBy
    newAddintoGrid.isAppend
    newAddintoGrid.SumOnField
    newAddintoGrid.TotalCaptionAtColumnNumber

If you want to skip record if value zero of any particular colour for example we do not need that item which stock value is 0, simply name = fieldname

if you want to grouping on grid on particular field = fieldname

if you want to add data from different records set append = true

if you want to sum on numeric field = fieldname

u can place "Total" caption near to sum result.

many other feature can be added.

[zubairkhan]

But here i we have un limited options , can MshFlexgrid allow you to add data from different recordset?. Can MshFlexGrid allow you to place any heading or caption at run time. And most important is that it release the record set after retreiving the data.

[dglienna]

zubairkhan:

I got a few errors. Can you post a small project along with the class?

Thanks.

[zubairkhan]

David

I have not small project, but i am sending you recordset and its fields set by class

.Open "SELECT b.JobNo,b.JobDate,b.JobDesc,b.PartyName,b.OrderQty, " & _
        "iif(isnull(b.Issued),0,b.issued)-iif(isnull(e.rejected),0,e.rejected) as Issued,b.Billed, " & _
        "b.BilledRate,b.RateUnit,b.BilledAmt,b.GstAmt, " & _
        "b.TotValue,b.ToBill " & _
        "FROM billedquery b,enquiry e " & _
        "WHERE e.jobno = b.jobno " & _
        "AND mid(e.jobno,3,1) = '" & txtType.Text & "' " & _
        "ORDER BY b.partyname"

this will might help you to rectify my error i really appreciate you if you identified my mistakes .

Set NewAddinGrid = New clsAddInFlexGrid
    NewAddinGrid.ColoumnNameToSkipRecordIfZero = "tobill"
    NewAddinGrid.FieldGroupBy = "partyname"
    NewAddinGrid.SumOnField = "billedamt"
    NewAddinGrid.TotalCaptionAtColumnNumber = 7
    Call NewAddinGrid.AddinGrid(ViewRs, FxGrid)

Thanks

[dglienna]

OK. I've resolved the errors. You also have to declare NewAddinGrid as a object. I'll use some data tomorrow. I just worked on adding a flexgrid to a new project. This would have saved some time. I may replace my code with your class tomorrow. Thanks.

EDIT: You should place a copy in the CodeBank. I'll let you know if there are any more problems.

[zubairkhan]

Thanks for your co-operation, i will add in code bank after your reply.

[Pradeep1210]

But here i we have un limited options , can MshFlexgrid allow you to add data from different recordset?. Can MshFlexGrid allow you to place any heading or caption at run time. And most important is that it release the record set after retreiving the data.

You'll be able to do all these. You just need to change the approach. An MSHFlexGrod can do everything a FlexGrid can do plus more.
It just populates itself with the recordset, doesn't bind to it. So after that you can close the recordset or do whatever else. And changes to the recordset or MSHFlexGrid cell after that won' be reflected in the other one either.

Pradeep

[Rina]

Why do I always get this error?
subscript out of range

Can anyone here help me correct this code.
Thanks in advance.

Private Sub FG_ShowRecordset(myFG As MSFlexGrid, myRST As ADODB.Recordset)
 
Screen.MousePointer = vbHourglass
  
  myFG.Redraw = False
  myFG.AllowUserResizing = flexResizeColumns
  myFG.ScrollTrack = True
 
'column header
myFG.TextMatrix(0, 0) = "DocNo"
myFG.TextMatrix(0, 1) = "ItemNo"
myFG.TextMatrix(0, 2) = "Item"
myFG.TextMatrix(0, 3) = "Occurances"
 
With myRST
    'loop for displaying the document id in DocNo column
    'fields(0) = Id
    x = 0
    Do
    x = x + 1
    myFG.TextMatrix(x, 0) = .Fields(0).Value 'error points to this line
    .MoveNext
    Loop Until .EOF
End With
 
myFG.Redraw = True
 
Screen.MousePointer = vbNormal
  
End Sub

[dglienna]

I've adapted szlamany's method

Sub DisplayRecordset(x As ADODB.Recordset)
  Dim rs As String, fld As String, z As Long
  Dim str As String
  flx.Rows = 1
  str = "ID" & vbTab & "Last Name   " & vbTab & "First Name   " & vbTab
  str = str & "Address               " & vbTab & "City          " & vbTab & "State " & vbTab
  
  flx.FormatString = str
 
  Do While x.EOF = False
    For z = 0 To x.Fields.Count - 1
      fld = x(z) & ""
      If rs <> "" Then rs = rs & vbTab
      rs = rs & fld
    Next z
    flx.AddItem rs
    rs = ""
    x.MoveNext
  Loop
  Set x = Nothing
End Sub

It adds the autonumber to col(0). You could use formatstrint to load the row headings, also. I had to set .rows to 1 before I loaded the grid as it was adding a blank row to the beginning of the grid each time I populated it.

[dglienna]

I was getting a blank row on top, so I made this change to your class, which fixed things. I like the bold KEY field name.

If m_isAppend = False Then
        FlexGrid.Rows = 1 ' Instead of 2
    End If

Also, the columns weren't wide enough in a few cases. I have some long and some short fieldnames. Also, it would be nice if you could set the alignment of fields and the format.