Dynamic Crosstab

[Sandy H]

Hi
I am using the following code to fill in the data for a dynamic crosstab
report. I have done this before for other cross tab queries but for some
reason, this one keeps listing the values from the first row over and over
again. Can anyone see what I am doing wrong? Could it be anything to do
with the report having a group header and footer?

This is the code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim rs As Recordset
Dim i As Integer
Dim j As Integer
Dim total(12) As Integer
Set rs = DBEngine(0)(0).OpenRecordset("select * from
qryOutputs_Crosstab")

j = 0
i = 0

While Not rs.EOF
For i = 3 To rs.Fields.Count - 1
Select Case j
Case 0
Me.Text1.Caption = Nz(rs.Fields(i), 0)
Me.Text20.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 1
Me.Text2.Caption = Nz(rs.Fields(i), 0)
Me.Text21.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 2
Me.Text3.Caption = Nz(rs.Fields(i), 0)
Me.Text22.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 3
Me.Text4.Caption = Nz(rs.Fields(i), 0)
Me.Text23.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 4
Me.Text5.Caption = Nz(rs.Fields(i), 0)
Me.Text24.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 5
Me.Text6.Caption = Nz(rs.Fields(i), 0)
Me.Text25.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 6
Me.Text7.Caption = Nz(rs.Fields(i), 0)
Me.Text26.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 7
Me.Text8.Caption = Nz(rs.Fields(i), 0)
Me.Text27.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 8
Me.Text9.Caption = Nz(rs.Fields(i), 0)
Me.Text28.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 9
Me.Text10.Caption = Nz(rs.Fields(i), 0)
Me.Text29.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 10
Me.Text11.Caption = Nz(rs.Fields(i), 0)
Me.Text30.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"
Case 11
Me.Text12.Caption = Nz(rs.Fields(i), 0)
Me.Text31.ControlSource = "=Sum(" & Nz(rs.Fields(i), 0)
& ")"

End Select
j = j + 1
Next i
rs.MoveNext
Wend

End Sub

Thanks in advance
Sandy

 

[Duane Hookom]

I don't particularly care for that method but don't you need to reference
the name of the field rather than the value?
Case 0
Me.Text1.Caption = Nz(rs.Fields(i).Name, 0)
Me.Text20.ControlSource = "=Sum(" & Nz(rs.Fields(i).Name, 0) & ")"

I think a more flexible solution can be found in the Crosstab.mdb in the
Crosstab Report download at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

 

[Guest]

Thank you so much for your example. It really helps me a lot, being in
exactely the same situation. I just need to adapt a little bit to my own
needs, but I guess that I got the general idea.
patrick chambon

 

[Duane Hookom]

Glad to hear the method is finding another application.