Report in Grid or Grid type control

[sillylady]

Hi,

I have a form which searches database on user critera and displays the result in FlexiGrid and if user wants to export the result to excel they can. Everything is working fine. But now I want to modify this program a little.

The result in Grid is display like this:

Year-----Field1-----Field2-----Field3
2000----- A ------- A -------- A
2001----- B ------- B -------- C
2002----- C ------- C -------- C

But I want this to be displayed in Grid like this:

Year----2000-----2001----2002
Filed1----A--------B-------C
Field2----A--------B-------C
Filed3----A--------B-------C


How can I do this? Any help?

Thanks

[BrailleSchool]

can you post your program and/or its sourcecode? then someone can see how its written and offer advice.

Hi,

I have a form which searches database on user critera and displays the result in FlexiGrid and if user wants to export the result to excel they can. Everything is working fine. But now I want to modify this program a little.

The result in Grid is display like this:

Year-----Field1-----Field2-----Field3
2000----- A ------- A -------- A
2001----- B ------- B -------- C
2002----- C ------- C -------- C

But I want this to be displayed in Grid like this:

Year----2000-----2001----2002
Filed1----A--------B-------C
Field2----A--------B-------C
Filed3----A--------B-------C


How can I do this? Any help?

Thanks

[sillylady]

Here is code.

lngRow = adoRS.RecordCount
lngCol = adoRS.Fields.Count
 
With ManuRatioGrid
 
.Cols = 2
.Rows = 2
 
Do While Not adoRS.EOF
 
For j = 0 To lngRow
For i = 0 To lngCol
 
.TextMatrix(i, j) = adoRS.Fields(i).Value
 
Next i
 
adoPrimaryRS.MoveNext
Next j
 
Loop
End With

I think its working but the problem is if any cell of the Grid is empty, the for loop comes out.

[ganeshmoorthy]

how many records supposed to be retrieved and how many have been displayed in the grid...i cudnt see any problem with this code, but how do u open the recordset ....

[sillylady]

how many records supposed to be retrieved and how many have been displayed in the grid...i cudnt see any problem with this code, but how do u open the recordset ....

It can retrieve any number of records and it displays the fetched records in the grid. I think the problem is when the inner for look encounter 'blank' cell in the Grid the loop exits.

[ganeshmoorthy]

i hope you are filling only the blank cells with the resultset...
may be instead of using

.TextMatrix(i, j) = adoRS.Fields(i).Value
try this
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

[sillylady]

i hope you are filling only the blank cells with the resultset...
may be instead of using

.TextMatrix(i, j) = adoRS.Fields(i).Value
try this
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

I think i am not clear in my question. I am retrieving records in the ADO Recordset and trying to transfer those records in a GRID in this manner:
Year-----2000
Field1-----1
Field2-----1
Field3-----1

instead of the usual display like:
Year----Field1----Field2----Field3
2000-----1--------1--------1


the code which i have given here works fine the only problem is if any field in the row contains null value the for loop exits.

[ganeshmoorthy]

this will take care of the null values of the recordset, have you trid it...

.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

[sillylady]

this will take care of the null values of the recordset, have you trid it...

.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

I tried it but the problem is same..........for statement comes out after i=3

Do you have anyother idea?

[ganeshmoorthy]

is it displaying any error or how do u say if it is null it comes out of for loop...have you checked the recordcount, how much it returns...

[sillylady]

is it displaying any error or how do u say if it is null it comes out of for loop...have you checked the recordcount, how much it returns...

I have tested it by just setting the Grid Datasource to Ado recordset and it displays 6 records and 31 fields of which some have blank value.

[ganeshmoorthy]

i asked the value of lngRow after you assign lngRow = adoRS.RecordCount

[sillylady]

i asked the value of lngRow after you assign lngRow = adoRS.RecordCount

lngRow=6 and lngCol=31

[ganeshmoorthy]

i guess i is for your column and j is for your row values

.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

TextMatrix takes row, column, but you have given col, row check it and then in the MoveNext statement you have given some different recordset name and you have checked in the Do while some different recordset name... Do While adoRs.EOF and adoPrimaryRS.MoveNext

[sillylady]

i guess i is for your column and j is for your row values

.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

TextMatrix takes row, column, but you have given col, row check it and then in the MoveNext statement you have given some different recordset name and you have checked in the Do while some different recordset name... Do While adoRs.EOF and adoPrimaryRS.MoveNext

Don't be confused about the recordset name. I have used Col, Row to make the Row as Col and Col as Row in Grid display.

[ganeshmoorthy]

For j = 0 To adoRs.RecordCount -1
        For i = 0 To adoRs.Fields.Count - 1
            .TextMatrix(j+1, i+1) = adoRS.Fields(i).Value
        Next i
       adoRs.MoveNext
    Next j

[sillylady]

For j = 0 To adoRs.RecordCount -1
        For i = 0 To adoRs.Fields.Count - 1
            .TextMatrix(j+1, i+1) = adoRS.Fields(i).Value
        Next i
       adoRs.MoveNext
    Next j

Thank you for your continuous help but sadly it didnot solve my problem. I think I will have find another control so something that display the data in GRID as I want.

[sillylady]

The code below gives the result close to my desire. It dispays the 1st record vertically in GRID but after that nothing happens though there are 5 rows. Means 'For i' statement is working but after displaying the 1st record it doesn't display the next record mean 'For j' not working.

With ManuRatioGrid
 
Do While Not adoPrimaryRS.EOF
 
For j = 0 To adoPrimaryRS.RecordCount - 1
 
For i = 0 To lngCol
 
.TextMatrix(i + 1, j) = Trim(adoPrimaryRS.Fields(i).Value & "")
 
Next i
 
adoPrimaryRS.MoveNext
 
Next j
 
Loop
End With

[sillylady]

Any help on this plssssssss.

Or can any one suggest any grid where I can display records in the following format.

Year----2000
Field1---100
Field2---100
Field3---100


Thanks

[si_the_geek]

You just need to set the number of rows to the number of fields, and the number of columns to the number of records + 1 (field names). Then you fill the cells using (FieldNo,RecordNo), as the Textmatrix has parameters of (Row,Column).

Here's the code:

Dim lngRow As Long, lngCol As Long
 
  If adoPrimaryRS.EOF Then     'check we have data
    MsgBox "no data!"
    ManuRatioGrid.Rows = 0
  Else
    With ManuRatioGrid
      .Rows = adoPrimaryRS.Fields.Count
      .Cols = 2
      .FixedRows = 0
      .FixedCols = 1
                      'add field names
      For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
        .TextMatrix(lngRow, 0) = adoPrimaryRS.Fields(lngRow).Name
      Next lngRow
      
      lngCol = 1
      Do While Not adoPrimaryRS.EOF
                      'fill a column with a record
        For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
          .TextMatrix(lngRow, lngCol) = (adoPrimaryRS.Fields(lngRow).Value & "")
        Next lngRow
                      'move on to next record
        adoPrimaryRS.MoveNext
        If Not adoPrimaryRS.EOF Then
          .Cols = .Cols + 1
          lngCol = lngCol + 1
        End If
      Loop
    End With
  End If

[sillylady]

Thanks Geek. It is working. Thank you very much.

You just need to set the number of rows to the number of fields, and the number of columns to the number of records + 1 (field names). Then you fill the cells using (FieldNo,RecordNo), as the Textmatrix has parameters of (Row,Column).

Here's the code:

Dim lngRow As Long, lngCol As Long
 
  If adoPrimaryRS.EOF Then     'check we have data
    MsgBox "no data!"
    ManuRatioGrid.Rows = 0
  Else
    With ManuRatioGrid
      .Rows = adoPrimaryRS.Fields.Count
      .Cols = 2
      .FixedRows = 0
      .FixedCols = 1
                      'add field names
      For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
        .TextMatrix(lngRow, 0) = adoPrimaryRS.Fields(lngRow).Name
      Next lngRow
      
      lngCol = 1
      Do While Not adoPrimaryRS.EOF
                      'fill a column with a record
        For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
          .TextMatrix(lngRow, lngCol) = (adoPrimaryRS.Fields(lngRow).Value & "")
        Next lngRow
                      'move on to next record
        adoPrimaryRS.MoveNext
        If Not adoPrimaryRS.EOF Then
          .Cols = .Cols + 1
          lngCol = lngCol + 1
        End If
      Loop
    End With
  End If