How to set a data comparing with row and column of msflexgrid in vb6

Question

I have an MSFlexGrid in VB6. In that I want to check the column and rows heading. If both data matches the result should be shown in the corresponding cell.

For example I have a column heading like 1, 2, 3, 4, 5 and rows headings like 6-7, 7-8, 8-9.

------------------------------------
     |  1  |  2  |  3  |  4  |  5  |
------------------------------------
6-7  |     |     |     |     |     |
------------------------------------
7-8  |     |     |     |     |     |
------------------------------------
8-9  |     |     |     |     |     |
------------------------------------
9-10 |     |     |     |     |     |
------------------------------------

I need to check if a record matched 2 and 7-8 I need to put that data in the cell matching.

------------------------------------
     |  1  |  2  |  3  |  4  |  5  |
------------------------------------
6-7  |     |     |     |     |     |
------------------------------------
7-8  |     | Ok  |     |     |     |
------------------------------------
8-9  |     |     |     |     |     |
------------------------------------
9-10 |     |     |     |     |     |
------------------------------------

if i run the belwo code it displays only one record. if i have 10 records. it is not displaying all records. what is wrong in the looping statements.

rs.MoveFirst

Do While Not rs.EOF
    For i = 0 To MSFlexGrid1.Cols - 1
        For r = 0 To MSFlexGrid1.Rows - 1
            If MSFlexGrid1.TextMatrix(0, i) = rs(30) And MSFlexGrid1.TextMatrix(r, 0) = rs(31) Then
                MSFlexGrid1.TextMatrix(r, i) = rs(1)
                rs.MoveNext
            End If
            
            r = r + 1
        Next

        i = i + 1
        rs.MoveNext
    Next
Loop

Answer 1

As mentioned in a comment, there are a number of issues with your code. However, by using the Filter method of the Recordset you can simplify the code quite a bit. I can only infer what your data looks like but something like the following should work:

For i = 1 To MSFlexGrid1.Cols - 1
   For r = 1 To MSFlexGrid1.Rows - 1
      rs.Filter = "Col = '" & MSFlexGrid1.TextMatrix(0, i) & "' AND Row = '" & MSFlexGrid1.TextMatrix(r, 0) & "'"
      If Not rs.EOF Then MSFlexGrid1.TextMatrix(r, i) = rs("Result")
   Next
Next

This code loops through all the cells in the grid and quickly determines if there is a match in the Recordset. If there is a match, it places the Result column of the Recordset into the corresponding cell.

My Recordset has 3 columns: Result, Col, and Row. You will need to plug in the correct field names for your data.

When I run the code against a Recordset with 3 records I get the following: