Dynamic Crosstab - Repost

[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'm not sure why the repost. Did you not see the reply to your question
earlier this morning?

 

[Sandy H]

Thanks Duane. I did see your post but the link took me to the FMS download
page and I couldn't find the crosstab report area within the downloaded
database. I realise there are other solutions for creating dynamic crosstab
reports but I was more curious as to why my current code isn't working when
I have had it working before. I thought I may have overlooked something
simple in my code.

Sandy

 

[Duane Hookom]

My previous post had a link to Corporate Technologies web site. In addition,
I think I provided the solution to the issue with your code. From my earlier
post:
====================
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
====================

 

[Sandy H]

Thanks for the reply Duane. Apologies if I am misunderstanding, but the
code you have provided is assigning the name of the column which I have
already done successfully in the header. It is the data contained in the
query that I am having problems getting out to the report. The first line
writes ok but then every line thereafter is the same when it shouldn't be.

Sandy

 

[Duane Hookom]

I didn't notice you were attempting to assign values from a crosstab result
to the detail section. I would think with that method, you would have to
open a one record recordset to get the values for your controls. The next
call to the detail format would need to open the next record.

I think the solutions database set the control source properties of the text
boxes in the On Open event using the field names. Some samples I have seen
open a recordset in the On Open event of the report and use this same
recordset in the detail section code. The code looks for the FormatCount to
synchronize the stepping through the recordset with the printing of the
detail section.

I still think the Corp Tech solution is much more flexible.