Filling MSFlexGrid Control with DAO recordset

[whittam]

Can anyone help me I need to resize the individual columns in a MS FlexGrid control so all data is visible when I place the records from a DAO recordset into it.

I found some code for an ADO recordset but I can't seem to adapt it to to DAO recordset

Any information would be appreciated

[Paul Warren]

whittam - Here's some code I've used before. It's not perfect because if you know the number of font points per twip you could scale the widths better but it'll give you the idea.

Private Const SCALE_FAC As Integer = 12

Private Sub Fill_Grid()

Dim SQL1 As String
Dim intNumber As Integer
Dim intCount As Integer
Dim RowNum As Integer
Dim intLen As Integer
Dim intRows As Integer
Dim dbTemp As Database

Set dbTemp = OpenDatabase("C:\db1.mdb")

SQL1 = "SELECT * FROM Table1"

Set rsTemp = dbTemp.OpenRecordset(SQL1)

If rsTemp.RecordCount = 0 Then
    Exit Sub
End If

intRows = rsTemp.RecordCount
intNumber = rsTemp.Fields.Count

' Set the dimensions of the grid based on the recordset
grdFields.Cols = intNumber
grdFields.Rows = 1

rsTemp.MoveFirst

intLen = 0

' Get the headings from the information recordset
For intCount = 0 To intNumber - 1
    
    intLen = Len(rsTemp.Fields(intCount).Name) + 1
    grdFields.ColWidth(intCount) = (intLen * (grdFields.FontSize * SCALE_FAC) + 200)
    grdFields.TextMatrix(0, intCount) = rsTemp.Fields(intCount).Name
    grdFields.Col = intCount
    grdFields.CellFontBold = True
    rsTemp.MoveNext

Next intCount

rsTemp.MoveFirst

RowNum = 0

Do Until rsTemp.EOF

    RowNum = RowNum + 1
    grdFields.AddItem " " ' Add a blank row to populate
    
    For intCount = 0 To intNumber - 1 ' Loop through the records, inserting the data
        If Not IsNull(rsTemp.Fields(intCount).Value) Then
            intLen = Len(rsTemp.Fields(intCount).Value) + 1
            If grdFields.ColWidth(intCount) < (intLen * (grdFields.FontSize * SCALE_FAC) + 200) Then
                grdFields.ColWidth(intCount) = (intLen * (grdFields.FontSize * SCALE_FAC) + 200)
            End If
            grdFields.TextMatrix(RowNum, intCount) = rsTemp.Fields(intCount).Value
        Else
            grdFields.TextMatrix(RowNum, intCount) = " "
        End If
        grdFields.ColAlignment(intCount) = 0
    Next intCount
    
    rsTemp.MoveNext
    
Loop

End Sub

Private Sub Form_Load()

Fill_Grid

End Sub

You'll need a flexgrid called grdFields to see it work. Oh, and a database.