Populating MSFlexGrid with Recordset

I couple of days ago I posted a query as to how to program with a recordset to populate MSHflexGrid. A member sent me a code to try. whenever I run the code, there is an error message "runtime error, subscript out of range". Note, I have set reference to the ADO object in code and also using the "NWind" database. Any help would be much appreciated.Attached at the bottom page is the code. Thank you

Albert.


Option Explicit

Private Sub Form_Load()

Dim cnData As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim Char As String
Dim sconnect As String
Dim IRow As Long
Dim i As Integer

Set cnData = New ADODB.Connection
Set rsData = New ADODB.Recordset

cnData.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=F:\Program files\Microsoft Visual studio\Vb98\NWind.mdb"

sconnect = "Select EmployeeId, Lastname from Employees"
cnData.Open

rsData.Open sconnect, cnData, adOpenDynamic
IRow = 1

If rsData.EOF And rsData.BOF Then Exit Sub
MSHFlexGrid1.Cols = rsData.Fields.Count
MSHFlexGrid1.Rows = rsData.RecordCount + 1

Do Until rsData.EOF
For i = 0 To rsData.Fields.Count - 1
MSHFlexGrid1.TextMatrix(0, i) = rsData(i).Name
MSHFlexGrid1.TextMatrix(IRow, i) = rsData(i).Value & " "

Next i
IRow = IRow + 1
rsData.MoveNext
Loop

rsData.Close
cnData.Close
End Sub

[Clunietp]

I see your problem but can't explain it too well (it's too late for me tonight) so in order to avoid thinking, I threw together some code that works:

    Dim cn As Connection
    Dim rs As Recordset
    Dim I As Integer
    Dim intRecCount As Integer
    
    Set cn = New Connection
    Set rs = New Recordset
    
    
    cn.Open "Provider=Microsoft.jet.OLEDB.3.51;Data Source=NWind.mdb"
    
    rs.Open "Select * from Customers", cn, adOpenStatic, adLockReadOnly, adCmdText
    
    Me.MSFlexGrid1.Rows = rs.RecordCount + 1
    Me.MSFlexGrid1.Cols = rs.Fields.Count
    
    For I = 0 To rs.Fields.Count - 1
        Me.MSFlexGrid1.TextMatrix(0, I) = rs.Fields(I).Name & ""
    Next I
    
    Do Until rs.EOF = True
    
        intRecCount = intRecCount + 1
        For I = 0 To rs.Fields.Count - 1
            Me.MSFlexGrid1.TextMatrix(intRecCount, I) = rs.Fields(I).Value & ""
        Next I
        
        rs.MoveNext
    Loop

Enjoy

Tom

[ebma1]

I had the same problem and It was to do with the recordset declaration e.g

rsClientJobs.Open sqlClientjob, cnnConn, adOpenForwardOnly, adLockReadOnly, adCmdText

and I changed it to

rsClientJobs.Open sqlClientjob, cnnConn, adOpenStatic, adLockReadOnly, adCmdText


i.e. I used 'adOpenStatic' instead of 'adOpenForwardOnly'

that solved it for me!