Crosstab Form

[sargentrs]

I have a report based on a crosstab query where the column headings come from a field named DueDate. The column headings are dynamic and change depending on the dates. Code behind the report is as follows:

Private Sub Report_Open(Cancel As Integer)
' You didn't know how many 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("txtSum" & 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("txtSum" & i).Visible = False
Next i

Works beautifully! Can you use the same code in a form. I'd like to take this report and make it viewable as a form so I can add some option buttons and comment fields. See attached for crosstab query behind the report.