MSFlexGrid - Load data

[jess]

Hi,
I'm trying to use a MSFlexGrid, but cannot get it loaded with data in a retrieved recordset.

I know my recordset (Dim rs As ADODB.Recordset) has retrieved the data correctly, but when I'm using:

Set MSFlexGrid1.DataSource = rs

Following message appear.
Run-time error '91':
Object variable or With block variable not set.


Could you please help me getting back on track...

[johnpc]

Here is the whole ball of wax, based on using a MS Access
data base named Parts.mdb with a Table named tblParts.

                           '**** Form Level Declarations ****

'*************************************************************************************************
'  Be sure to add a Reference to Ms ActiveX Data Objects 2.x Library to Project
'*************************************************************************************************

Dim DbFile As String                              'Name of DataBase
Dim cn as ADODB.Connection                'Connect to the ADO Data Type
Dim rs as ADODB.Recordset                   'Record Source Name
Dim SQLstmt as String                            'SQL Statement String(s)

Private Sub Form_Load()
     Open_cn
     Call ShowFlexGrid
End Sub

Private Sub Open_cn ()
'     Set the Database Applicable Path
      DbFile = App.Path & "\Parts.mdb"

'      Establish the Connection
       Set cn= New ADODB.Connection
       cn.CursorLocation = adUseClient
       cn.ConnectionString = _
             "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & DbFile & ";" & _
             "Persist Security Info=False"

'      Open the Connection
      cn.Open

'      Once this Connection is opened, it can
'      be used throughout the application

      SQLstmt = "SELECT * FROM [tblParts]"

'      Get the Records
      Set rs = New ADODB.Recordset
      rs.Open SQLstmt, cn, adOpenStatic, adLockOptimistic, _
           adCmdText

End Sub

Private Sub Close_cn ()
     cn.Close
     Set cn = Nothing
End Sub

Private Sub ShowFlexGrid()
Dim c as Integer
Dim flxgd_row as Integer
Dim field_wid as Integer
     ' Use one fixed row and no fixed columns 
     MSFlexGrid1.Rows = 2
     MSFlexGrid1.FixedRows = 1
     MSFlexGrid1.FixedCols = 0

     ' Display column headers 
     MSFlexGrid1.Rows = 1 
     MSFlexGrid1.Cols = rs.Fields.Count
     ReDim col_wid(0 To rs.Fields.Count - 1)

     For c = 0 to (rs.Fields.Count - 1)
               MSFlexGrid1.TextMatrix(0, c) = rs.Fields(c).Name
               col_wid(c) = TextWidth(rs.Fields(c).Name)
     Next c

     'Display the values for each row
     flxgd_row = 1

     Do While Not rs.EOF
          MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1

          For c = 0 To (rs.Fields.Count - 1)

               MSFlexGrid1.TextMatrix(flxgd_row, c) = _
               Format(rs.Fields(c).Value, ".      ")

               ' See how big the value is
               field_wid = TextWidth(rs.Fields(c).Value) 
               If col_wid(c) < field_wid Then col_wid(c) = field_wid

          Next c

          rs.MoveNext
          flxgd_row = flxgd_row + 1

     Loop

     End Sub

[jess]

Thank you so much, you opened my eays, I didn't think I had to loop through the recordset to display the data.

I will try this right away. - THANKS!