MSFlexgrid Data Retrieval With ADO....

[si_the_geek]

Here's a Sub I recently wrote, you can just put this into your project and use it as in the "'Example usage" comments.

Public Sub FillFromRecordset_FlexGrid(p_ctlFlexGrid As Control, _
                                      p_objRecordset As Object, _
                                      p_booFieldNamesAsHeaders As Boolean)
'Fill an MSFlexGrid control with values from a recordset
'By Si_the_geek, VBForums

'Parameters:
'  p_ctlFlexGrid            - The FlexGrid control to fill
'  p_objRecordset           - The Recordset to get the data from (can be ADO/DAO)
'  p_booFieldNamesAsHeaders - True to show field names as column headers

'Example usage:
'  Call FillFromRecordset_FlexGrid(MSFlexGrid1, objRS, True)


Dim lngCol As Long
Dim lngRow As Long
Dim booOldRedraw As Boolean

  With p_ctlFlexGrid
      'Set up the required number of columns
    .Cols = p_objRecordset.Fields.Count
    .FixedCols = 0

      'If specified, show field names as headers
    If p_booFieldNamesAsHeaders Then
      .FixedRows = 1
      For lngCol = 0 To p_objRecordset.Fields.Count - 1
        .TextMatrix(0, lngCol) = p_objRecordset.Fields(lngCol).Name
      Next lngCol 
    End If

      'remove all rows except any headers, and the first data row (cannot be removed)
    .Rows = .FixedRows + 1

      'Turn off screen updates (much faster to fill the data)
    booOldRedraw = .Redraw
    .Redraw = False

      'Check if there is any data
    If p_objRecordset.EOF Then
        'if there is no data, only allow the required blank row, and hide it (height=0)
      .AddItem ""
      .RemoveItem .FixedRows
      .RowHeight(.FixedRows) = 0
    Else

        'We have data, add it one row at a time
        '(nb: there are various ways to do this, this way is quick, and easy to read)
      lngRow = .Rows
      Do While Not p_objRecordset.EOF
          'Add the row (empty)
        .AddItem ""
          'Set the values once cell at a time (avoids problems with Nulls and data containing grid delimiters)
        For lngCol = 0 To p_objRecordset.Fields.Count - 1
            'if you want to format the text for some columns differenly, you can _
            'use If/Else or Select Case here - but its better to do that in your SQL _
            'statement, as it is more efficient, and allows this sub to be re-used easily
          .TextMatrix(lngRow, lngCol) = p_objRecordset.Fields(lngCol).Value & ""
        Next lngCol
          'Increment our row counter
        lngRow = lngRow + 1
          'Move to the next row of data
        p_objRecordset.MoveNext
      Loop

        'Remove the blank row we left at the top
      .RemoveItem .FixedRows

    End If

      'Re-enable screen updates (if was previously enabled)
    .Redraw = booOldRedraw

      'Force a redraw of the grid
    .Refresh
  End With

End Sub