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!