recordset to msflexgrid-VBForums
Results 1 to 30 of 30

Thread: recordset to msflexgrid

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2005
    Posts
    79

    recordset to msflexgrid

    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.

    VB Code:
    1. Private Sub FG_ShowRecordset(myFG As MSFlexGrid, myRST As ADODB.Recordset)
    2.   Dim iField As Integer, iNofFields As Integer
    3.   Dim lRow As Long
    4.  
    5.   Screen.MousePointer = vbHourglass
    6.  
    7.   myFG.Redraw = False
    8.   myFG.AllowUserResizing = flexResizeColumns
    9.   myFG.ScrollTrack = True
    10.  
    11. 'column header
    12. myFG.TextMatrix(0, 0) = "DocNo"
    13. myFG.TextMatrix(0, 1) = "ItemNo"
    14. myFG.TextMatrix(0, 2) = "Item"
    15. myFG.TextMatrix(0, 3) = "Occurances"
    16.  
    17.  
    18. With myRST
    19.     'loop for displaying the document id in DocNo column
    20.     'fields(0) = Id
    21.     For x = 1 To myFG.Rows - 1
    22.         myFG.TextMatrix(x, 0) = .Fields(0).Value
    23.         .MoveNext
    24.         x = x + 1
    25.     Next
    26. End With
    27.  
    28. myFG.Redraw = True
    29.  
    30.  Screen.MousePointer = vbNormal
    31.  
    32.   End Sub

  2. #2
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    6,900

    Re: recordset to msflexgrid

    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.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: recordset to msflexgrid

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    Addicted Member
    Join Date
    May 2005
    Posts
    168

    Re: recordset to msflexgrid

    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

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2005
    Posts
    79

    Re: recordset to msflexgrid

    Why I got this error : subscript out of range ?

    VB Code:
    1. With myRST
    2.     'loop for displaying the document id in DocNo column
    3.     'fields(0) = Id
    4.     Do While Not .EOF
    5.         myFG.Rows = myFG.Rows + 1
    6.         myFG.TextMatrix(myFG.Rows, 0) = .Fields(0).Value
    7.         .MoveNext
    8.     Loop
    9. End With

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jul 2005
    Posts
    79

    Re: recordset to msflexgrid

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

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: recordset to msflexgrid

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    Hyperactive Member
    Join Date
    May 2005
    Posts
    262

    Re: recordset to msflexgrid

    VB Code:
    1. Set rs = New ADODB.Recordset
    2.  
    3. myFG.ColHeaderCaption(0, 0) = "DocNo"
    4. myFG.ColHeaderCaption(0, 1) = "ItemNo"
    5. myFG.ColHeaderCaption(0, 2) = "Item"
    6. myFG.ColHeaderCaption(0, 3) = "Occurances"
    7.  
    8. Do While Not .EOF
    9.  
    10.   myFG.AddItem rs!DocNo & vbTab & rs!ItemNo & vbTab & rs!Item & vbTab & rs!Occurances
    11.  
    12. rs.MoveNext
    13.  
    14. Loop

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: recordset to msflexgrid

    Quote Originally Posted by Rina
    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

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jul 2005
    Posts
    79

    Re: recordset to msflexgrid

    I try this code which I put .additem syntax in the for loop.
    VB Code:
    1. With myRST
    2.     'loop for displaying the document id in DocNo column
    3.     'fields(0) = Id
    4.     For x = 1 To myFG.Rows - 1
    5.         myFG.TextMatrix(x, 0) = .Fields(0).Value
    6.         .MoveNext
    7.     Next
    8.     myFG.AddItem strGridLine
    9. 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?

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jul 2005
    Posts
    79

    Re: recordset to msflexgrid

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

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: recordset to msflexgrid

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: recordset to msflexgrid

    Quote Originally Posted by Rina
    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,907

    Re: recordset to msflexgrid

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

    Code:
    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15
    Hyperactive Member
    Join Date
    May 2005
    Posts
    324

    Re: recordset to msflexgrid

    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.

  16. #16
    New Member
    Join Date
    Aug 2005
    Location
    Atlanta, GA
    Posts
    4

    Re: recordset to msflexgrid

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

    Dave

  17. #17
    Banned dglienna's Avatar
    Join Date
    Jun 2004
    Location
    Center of it all
    Posts
    17,901

    Re: recordset to msflexgrid

    You can use the shape command to load a MSHFlexgrid.

  18. #18
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: recordset to msflexgrid

    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.

    VB Code:
    1. Option Explicit
    2. Private m_GroupBy, m_SumOn, m_OmitZero As String
    3. Private m_TotalCaption As Long
    4. Private TotalFields As Long
    5. Private m_isAppend As Boolean
    6. Property Get FieldGroupBy() As String
    7.     FieldGroupBy = m_GroupBy
    8. End Property
    9. Property Let FieldGroupBy(ByVal newGroupBy As String)
    10.     m_GroupBy = newGroupBy
    11. End Property
    12. Property Get SumOnField() As String
    13.     SumOnField = m_SumOn
    14. End Property
    15. Property Let SumOnField(ByVal newSumOn As String)
    16.     m_SumOn = newSumOn
    17. End Property
    18. Property Get ColoumnNameToSkipRecordIfZero() As String
    19.     ColoumnNameToSkipRecordIfZero = m_OmitZero
    20. End Property
    21. Property Let ColoumnNameToSkipRecordIfZero(ByVal newOmitZero As String)
    22.     m_OmitZero = newOmitZero
    23. End Property
    24. Property Get TotalCaptionAtColumnNumber() As Long
    25.     TotalCaptionAtColumnNumber = m_TotalCaption
    26. End Property
    27. Property Let TotalCaptionAtColumnNumber(ByVal newTotalCaption As Long)
    28.     m_TotalCaption = newTotalCaption
    29. End Property
    30. Property Get isAppend() As Boolean
    31.     isAppend = m_isAppend
    32. End Property
    33. Property Let isAppend(ByVal newIsAppend As Boolean)
    34.     m_isAppend = newIsAppend
    35. End Property
    36. Public Sub AddinGrid(pRsName As ADODB.Recordset, FlexGrid As MSFlexGrid, Optional CloseCursor As Boolean)
    37.     If pRsName.EOF = True Or pRsName.BOF = True Then
    38.         MsgBox "Recordset either EOF or BOF"
    39.         pRsName.Close
    40.        
    41.         Exit Sub
    42.     End If
    43.     If pRsName.RecordCount >= 5555 Then
    44.         MsgBox "Too bit table to fit in FlexGrid"
    45.         pRsName.Close
    46.        
    47.         Exit Sub
    48.     End If
    49.     FlexGrid.AllowUserResizing = flexResizeColumns
    50.     Dim i, NewRow, FieldNumber, gFieldNumber As Long
    51.     Dim yesGroupFound As Boolean
    52.     Dim yesSumFound As Boolean
    53.     Dim yesOmitFound As Boolean
    54.     Dim LastFieldValue As Variant
    55.     Dim mTotal As Variant
    56.     Dim RecordSkip As Boolean
    57.    
    58.     TotalFields = pRsName.Fields.Count
    59. If m_GroupBy <> "" Then
    60.     For i = 0 To TotalFields - 1
    61.         If UCase(pRsName.Fields(i).Name) = UCase(m_GroupBy) Then
    62.             yesGroupFound = True
    63.             gFieldNumber = i
    64.             Exit For
    65.         End If
    66.     Next i
    67.     If yesGroupFound = False Then
    68.         MsgBox "Invalid field entered for grouping"
    69.        
    70.         Exit Sub
    71.     End If
    72. End If
    73.  
    74. If m_SumOn <> "" Then
    75.     For i = 0 To TotalFields - 1
    76.         If UCase(pRsName.Fields(i).Name) = UCase(m_SumOn) And (pRsName.Fields(i).Type = adDouble _
    77.         Or pRsName.Fields(i).Type = adNumeric _
    78.         Or pRsName.Fields(i).Type = adLongVarChar) Then
    79.             yesSumFound = True
    80.             FieldNumber = i
    81.             Exit For
    82.         End If
    83.     Next i
    84.     If yesSumFound = False Then
    85.         MsgBox "Field entered for sum invalid or non numeric."
    86.         Exit Sub
    87.     End If
    88. End If
    89. If m_OmitZero <> "" Then
    90.     For i = 0 To TotalFields - 1
    91.         If UCase(pRsName.Fields(i).Name) = UCase(m_OmitZero) And (pRsName.Fields(i).Type = adDouble _
    92.         Or pRsName.Fields(i).Type = adNumeric _
    93.         Or pRsName.Fields(i).Type = adLongVarChar) Then
    94.             yesOmitFound = True
    95.             Exit For
    96.         End If
    97.     Next i
    98.     If yesOmitFound = False Then
    99.         MsgBox "Field entered for Omit zero value is invalid or non numeric."
    100.         Exit Sub
    101.     End If
    102. End If
    103.        
    104.     i = 0
    105.     FlexGrid.Cols = TotalFields
    106.     If m_isAppend = False Then
    107.         FlexGrid.Rows = 2
    108.     End If
    109.     FlexGrid.RowHeight(0) = 400
    110.    
    111. With FlexGrid
    112.  
    113.         For i = 0 To TotalFields - 1
    114.         If Not IsNull(pRsName.Fields(i)) Then
    115.             .CellFontBold = True
    116.             .TextMatrix(0, i) = UCase(pRsName.Fields(i).Name)
    117.         End If
    118.         Next i
    119.         i = 0
    120.     pRsName.MoveFirst
    121.     If m_GroupBy <> "" Then
    122.         LastFieldValue = pRsName.Fields("" & m_GroupBy & "")
    123.     End If
    124.    
    125.     If m_isAppend = False Then
    126.         NewRow = 0
    127.     End If
    128.    
    129.     Do While pRsName.EOF = False
    130.         If m_OmitZero <> "" Then
    131.             If pRsName.Fields("" & m_OmitZero & "") = 0 Then
    132.                 RecordSkip = True
    133.             Else
    134.                 RecordSkip = False
    135.             End If
    136.         End If
    137.     If RecordSkip = False Then
    138.             If m_SumOn <> "" And m_GroupBy = "" Then
    139.                   mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
    140.             End If
    141.         If m_GroupBy <> "" Then
    142.             If LastFieldValue = pRsName.Fields("" & m_GroupBy & "").Value Then
    143.                 If m_SumOn <> "" Then
    144.                     mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
    145.                 End If
    146.             Else
    147.                 If NewRow > 1 Then
    148.                     .Rows = .Rows + 1
    149.                     NewRow = .Rows - 1
    150.                     If m_SumOn <> "" Then
    151.                         If m_TotalCaption > 0 Then
    152.                             .TextMatrix(NewRow, m_TotalCaption) = "Total"
    153.                             .Col = m_TotalCaption
    154.                             .Row = .Rows - 1
    155.                             .CellFontUnderline = True
    156.                             .CellFontBold = True
    157.                             .CellBackColor = vbWhite
    158.                             .CellForeColor = vbMagenta
    159.                         Else
    160.                             .TextMatrix(NewRow, 1) = "Total"
    161.                            
    162.                         End If
    163.                         .TextMatrix(NewRow, FieldNumber) = Format(Round(mTotal, 0), "#######.00")
    164.                         mTotal = 0
    165.                         mTotal = mTotal + pRsName.Fields("" & m_SumOn & "")
    166.                     End If
    167.                     .Col = gFieldNumber
    168.                     .Row = .Rows - 1
    169.                     .CellBackColor = vbWhite
    170.                    
    171.                     .Col = TotalFields - 1
    172.                     '.Cols = TotalFields
    173.                     .Row = .Rows - 1
    174.                     .ColSel = FieldNumber
    175.                     .FillStyle = flexFillRepeat
    176.                     .CellBackColor = vbWhite
    177.                     .CellForeColor = vbBlue
    178.                     .CellFontBold = True
    179.                 End If
    180.             End If
    181.         End If
    182.     End If
    183.     If RecordSkip = False Then
    184.         .Rows = .Rows + 1
    185.         NewRow = .Rows - 1
    186.    
    187.         For i = 0 To TotalFields - 1
    188.         If Not IsNull(pRsName.Fields(i)) Then
    189.             If pRsName.Fields(i).Type = adDouble Then
    190.                 .TextMatrix(NewRow, i) = Format(pRsName.Fields(i).Value, "#######.00")
    191.             Else
    192.                .TextMatrix(NewRow, i) = pRsName.Fields(i).Value
    193.             End If
    194.         Else
    195.             .TextMatrix(NewRow, i) = ""
    196.         End If
    197.         Next i
    198.         i = 0
    199.         If m_GroupBy <> "" Then
    200.             LastFieldValue = pRsName.Fields("" & m_GroupBy & "")
    201.         End If
    202.     End If
    203.         pRsName.MoveNext
    204.     Loop
    205.         If mTotal > 0 And m_GroupBy = "" And m_SumOn <> "" Then
    206.             .Rows = .Rows + 1
    207.             NewRow = .Rows - 1
    208.             If m_TotalCaption > 0 Then
    209.                 .TextMatrix(NewRow, m_TotalCaption) = "Total"
    210.             Else
    211.                 .TextMatrix(NewRow, 1) = "Total"
    212.             End If
    213.             .TextMatrix(NewRow, FieldNumber) = Format(Round(mTotal, 0), "#######.00")
    214.             mTotal = 0
    215.            
    216.             .Col = TotalFields - 1
    217.             .Row = .Rows - 1
    218.             .ColSel = FieldNumber
    219.             .FillStyle = flexFillRepeat
    220.             .CellBackColor = vbWhite
    221.             .CellForeColor = vbBlue
    222.             .CellFontBold = True
    223.         End If
    224.     If CloseCursor = True Then
    225.         pRsName.Close
    226.         Set pRsName = Nothing
    227.     End If
    228. End With
    229.    
    230. End Sub
    add this class in your project , set it in your form from where u wand to add data in grid.
    VB Code:
    1. Set newAddintoGrid = New clsAddInFlexGrid
    2.     Call newAddintoGrid.AddinGrid(AppRateRs, FxGrid)
    fxgrid is that where u want to add data.

  19. #19
    Banned dglienna's Avatar
    Join Date
    Jun 2004
    Location
    Center of it all
    Posts
    17,901

    Re: recordset to msflexgrid

    Can you post your class, along with how to use the flags? It looks very interesting.
    Last edited by dglienna; Aug 8th, 2005 at 02:36 AM.

  20. #20
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,531

    Re: recordset to msflexgrid

    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.
    VB Code:
    1. Set MSHFlexGrid1.Recordset = rsData
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." Charles F. Kettering

    Read articles on My Blog 101 LINQ Samples JSON Validator XML Schema Validator "How Do I" videos on MSDN VB.NET and C# Comparison Good Coding Practices VBForums Reputation Saver String Enum Super Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  21. #21
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: recordset to msflexgrid

    VB Code:
    1. newAddintoGrid.ColoumnNameToSkipRecordIfZero
    2.     newAddintoGrid.FieldGroupBy
    3.     newAddintoGrid.isAppend
    4.     newAddintoGrid.SumOnField
    5.     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.

  22. #22
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: recordset to msflexgrid

    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.

  23. #23
    Banned dglienna's Avatar
    Join Date
    Jun 2004
    Location
    Center of it all
    Posts
    17,901

    Re: recordset to msflexgrid

    zubairkhan:

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

    Thanks.

  24. #24
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: recordset to msflexgrid

    David

    I have not small project, but i am sending you recordset and its fields set by class
    VB Code:
    1. .Open "SELECT b.JobNo,b.JobDate,b.JobDesc,b.PartyName,b.OrderQty, " & _
    2.         "iif(isnull(b.Issued),0,b.issued)-iif(isnull(e.rejected),0,e.rejected) as Issued,b.Billed, " & _
    3.         "b.BilledRate,b.RateUnit,b.BilledAmt,b.GstAmt, " & _
    4.         "b.TotValue,b.ToBill " & _
    5.         "FROM billedquery b,enquiry e " & _
    6.         "WHERE e.jobno = b.jobno " & _
    7.         "AND mid(e.jobno,3,1) = '" & txtType.Text & "' " & _
    8.         "ORDER BY b.partyname"

    this will might help you to rectify my error i really appreciate you if you identified my mistakes .
    VB Code:
    1. Set NewAddinGrid = New clsAddInFlexGrid
    2.     NewAddinGrid.ColoumnNameToSkipRecordIfZero = "tobill"
    3.     NewAddinGrid.FieldGroupBy = "partyname"
    4.     NewAddinGrid.SumOnField = "billedamt"
    5.     NewAddinGrid.TotalCaptionAtColumnNumber = 7
    6.     Call NewAddinGrid.AddinGrid(ViewRs, FxGrid)
    Thanks

  25. #25
    Banned dglienna's Avatar
    Join Date
    Jun 2004
    Location
    Center of it all
    Posts
    17,901

    Re: recordset to msflexgrid

    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.

  26. #26
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: recordset to msflexgrid

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

  27. #27
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,531

    Re: recordset to msflexgrid

    Quote Originally Posted by 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.
    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
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." Charles F. Kettering

    Read articles on My Blog 101 LINQ Samples JSON Validator XML Schema Validator "How Do I" videos on MSDN VB.NET and C# Comparison Good Coding Practices VBForums Reputation Saver String Enum Super Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  28. #28

    Thread Starter
    Lively Member
    Join Date
    Jul 2005
    Posts
    79

    Re: recordset to msflexgrid

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

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

    VB Code:
    1. Private Sub FG_ShowRecordset(myFG As MSFlexGrid, myRST As ADODB.Recordset)
    2.  
    3. Screen.MousePointer = vbHourglass
    4.  
    5.   myFG.Redraw = False
    6.   myFG.AllowUserResizing = flexResizeColumns
    7.   myFG.ScrollTrack = True
    8.  
    9. 'column header
    10. myFG.TextMatrix(0, 0) = "DocNo"
    11. myFG.TextMatrix(0, 1) = "ItemNo"
    12. myFG.TextMatrix(0, 2) = "Item"
    13. myFG.TextMatrix(0, 3) = "Occurances"
    14.  
    15. With myRST
    16.     'loop for displaying the document id in DocNo column
    17.     'fields(0) = Id
    18.     x = 0
    19.     Do
    20.     x = x + 1
    21.     myFG.TextMatrix(x, 0) = .Fields(0).Value 'error points to this line
    22.     .MoveNext
    23.     Loop Until .EOF
    24. End With
    25.  
    26. myFG.Redraw = True
    27.  
    28. Screen.MousePointer = vbNormal
    29.  
    30. End Sub

  29. #29
    Banned dglienna's Avatar
    Join Date
    Jun 2004
    Location
    Center of it all
    Posts
    17,901

    Re: recordset to msflexgrid

    I've adapted szlamany's method

    VB Code:
    1. Sub DisplayRecordset(x As ADODB.Recordset)
    2.   Dim rs As String, fld As String, z As Long
    3.   Dim str As String
    4.   flx.Rows = 1
    5.   str = "ID" & vbTab & "Last Name   " & vbTab & "First Name   " & vbTab
    6.   str = str & "Address               " & vbTab & "City          " & vbTab & "State " & vbTab
    7.  
    8.   flx.FormatString = str
    9.  
    10.   Do While x.EOF = False
    11.     For z = 0 To x.Fields.Count - 1
    12.       fld = x(z) & ""
    13.       If rs <> "" Then rs = rs & vbTab
    14.       rs = rs & fld
    15.     Next z
    16.     flx.AddItem rs
    17.     rs = ""
    18.     x.MoveNext
    19.   Loop
    20.   Set x = Nothing
    21. 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.

  30. #30
    Banned dglienna's Avatar
    Join Date
    Jun 2004
    Location
    Center of it all
    Posts
    17,901

    Re: recordset to msflexgrid

    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.

    VB Code:
    1. If m_isAppend = False Then
    2.         FlexGrid.Rows = 1 ' Instead of 2
    3.     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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.