dynamic columns in report from crosstab

[jweisman]

I am using Access 2000. I am trying to set up a report with dynamic column headings and text boxes. I have four crosstab queries. I have combined the results of these queries into one query that is the recordsource for the report. I have the following code for making dynamic columns:

Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim i As Integer
Dim strName As String, strSQL As String
Dim Length As Integer, Position As Integer

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

On Error Resume Next

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

strSQL = "Select * from [" & Me.RecordSource & "] "

rst.Open Source:=strSQL, ActiveConnection:=cnn, LockType:=adLockReadOnly

intColCount = rst.Fields.Count
MsgBox intColCount

intControlCount = Me.Detail.Controls.Count

' Fill in information for the necessary controls.
For i = 1 To intColCount
If (i <> 4 Or i <> 8 Or i <> 12) Then

strName = rst.Fields(i).Name
MsgBox strName
Me.Controls(&quot;txtData&quot; & i).ControlSource = strName

Length = Len(strName)
Position = InStr(1, strName, &quot;.&quot;, vbTextCompare)
strName = Right(strName, (Length - Position))

Me.Controls(&quot;lblHeader&quot; & i).Caption = strName
End If

Next i


' Close the recordset.
rst.Close
cnn.Close
End Sub

The code is not reading the recordset properly. intcolcount is returning 0. Therefore the code is not getting past the For Loop. I am not too familiar with ADO. I tried using DAO also but that did not work. Any help would be greatly appreciated.

Thanks,

Jeff Weisman

 

[DanJR]

1. comment out the On Error Resume Next line of code. Run the report and see if an error occurs...

2. check your strSQL string, e.g. put a Debug.Pring strSQL after the

strSQL = &quot;Select * from [&quot; & Me.RecordSource & &quot;] &quot;

line. Then past the select statement into a query and see if it returns what you expect.

My first guess would be an error in the strSQL string with two SELECT clauses. The error is likely to be something like this...


SELECT * FROM SELECT .....(rest of SELECT statement)

That is, strSQL should simply be:

strSQL = Me.RecordSource



Hope this helps....