Unbound columns not all appearing with code and crosstab query

[OOBlmh]

I have a crosstab query that currently has 20 columns in it. I have unbound fields on a report to allow me to see the data. However columns 13 through 20 are not showing me the data from the query but rather the detail section is blank but the labels appear in the header as the caption given in the properties Column 13, Column 14 etc. I am using the following code in the open event of the report.

Private Sub Report_Open(Cancel As Integer)
' You didn't know how may columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim I As Integer
Dim strName As String

On Error Resume Next

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
SOURCE:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

' Fill in information for the necessary controls.
For I = 1 To intColCount
strName = rst.Fields(I - 1).NAME
Me.Controls("lblHeader" & I).Caption = strName
Me.Controls("txtData" & I).ControlSource = strName
Me.Controls("txtSub" & I).ControlSource = _
"=Sum([" & strName & "])"
Me.Controls("txtSum" & I).ControlSource = _
"=Sum([" & strName & "])"
Me.Controls("txtSubtot" & I).ControlSource = _
"=Sum([" & strName & "])"
Me.Controls("txtGroup" & I).ControlSource = _
"=Sum([" & strName & "])"
Next I

'Hide the extra controls.
For I = intColCount + 1 To intControlCount
Me.Controls("txtData" & I).Visible = False
Me.Controls("lblHeader" & I).Visible = False
Me.Controls("txtSub" & I).Visible = False
Me.Controls("txtSum" & I).Visible = False
Me.Controls("txtSubtot" & I).Visible = False
Me.Controls("txtGroup" & I).Visible = False
Next I

'Close the recordset.
rst.Close

DoCmd.Maximize
End Sub

 

[dhookom]

Have you done anything to troubleshoot your code like setting a breakpoint and stepping through or using

Debug.Print "intColCount: " & intColCount
Debug.Print "inControlCount: " & intControlCount

Have you tried the much more flexible and efficient solution discussed in thread703-1623377?

Duane
Hook'D on Access
MS Access MVP

 

[OOBlmh]

I have not done any troubleshooting the code because I am unfamiliar with how to do it. I haven't tried the other solution because I was copying from another report. Is the other one better than this way?

 

[dhookom]

Check faq705-7148.

I think the other crosstab solution is much better. You need to also consider I created the better solution ;-)

The sample download has a table that explains the solution.

Duane
Hook'D on Access
MS Access MVP

 

[OOBlmh]

Thanks for the help. I realized that I was messing it up with the columns I had in the report. I had 8 columns in the query that I wasn't using for this report so I didn't create an spot for on the report which mad the report stop at column 12 instead of going through to column 20.