Crosstab Report 1

[perrymans]

I am using the Microsoft Article for making a Crosstab Report:

http://support.microsoft.com/kb/328320

However, the report is only printing a single line from the crosstab. If I leave the code as:

If Not rstReport.EOF Then

then only first line displays because it exits the sub without actually moving to the next. If I change it to:

Do Until rstReport.EOF

Then the last record displays as a single line in the report. In this case I can step through with the code all the way through the recordset, but it still only writes the last record from the crosstab.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   ' Put values in text boxes and hide unused text boxes.
    
   Dim intX As Integer
   '  Verify that you are not at end of recordset.
   Do Until rstReport.EOF
      '  If FormatCount is 1, put values from recordset into text boxes
      '  in "Detail" section.
      If Me.FormatCount = 1 Then
         For intX = 1 To intColumnCount
            '  Convert Null values to 0.
            Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
         Next intX
    
         '  Hide unused text boxes in the "Detail" section.
         For intX = intColumnCount + 2 To conTotalColumns
            Me("Col" + Format(intX)).Visible = False
         Next intX

         '  Move to next record in recordset.
         rstReport.MoveNext
      End If
   Loop
    
End Sub

After the Detail_Format, the report opens, so there isn't anything left I guess? I don't really see in this code where it steps through the ReportDetail.Records, just the Recordset.Records.

Thanks. Sean.

 

[dhookom]

IMO, that article suggests a very inflexible, slow, and complex solution. I suggest Cross Tab which allows for greater flexibility and performance with less code.

Duane
Hook'D on Access
MS Access MVP

 

[perrymans]

That is much better.

Thank you. Sean.