If the heading names are consistent, though not always present, you can
solve the problem by specifying all the possible names in the Column
Headings property of the crosstab query.
If not, you can create a report with enough text boxes for the number of
fields you will ever need, but leave the unbound. Use code in the Open event
of the report to read the names of the actual fields, and assign them to
your text boxes, hiding those you do not need. This involves a bit of work,
but here is a sample of what the report's Open event procedure might look
like:
Private Sub Report_Open(Cancel As Integer)
'Purpose: Organize the text boxes on this report to match the fields
of the query.
'Assumes text boxes named txt00, txt01, ... and labels named lbl00,
lbl01, ...
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim iFieldCount As Integer
Dim iWidthEach As Integer
Dim i As Integer
Const icMaxFieldsToHandle = 20 'Max number of text boxes available.
'Get the querydef named in the report's RecordSource.
Set db = CurrentDb()
Set qdf = db.QueryDefs(Me.RecordSource)
'Get the number of fields in the source query.
iFieldCount = qdf.Fields.Count
If iFieldCount <= icMaxFieldsToHandle - 1 Then
'Hide boxes we do not need.
For i = iFieldCount To icMaxFieldsToHandle
Me.Controls("txt" & i).Visible = False
Me.Controls("lbl" & i).Visible = False
Next
ElseIf iFieldCount > icMaxFieldsToHandle Then
'Limit to available text boxes.
iFieldCount = icMaxFieldsToHandle
End If
'Calculate width for each text box.
iWidthEach = Int(Me.Width / iFieldCount)
'Assign the ControlSource and placement of each text box,
' and the Caption and placement of each label.
For i = 0 To iFieldCount - 1
With Me("txt" & i)
.ControlSource = qdf.Fields(i).Name
.Left = i * iWidthEach
.Width = iWidthEach
.Visible = True
End With
With Me("lbl" & i)
.Caption = qdf.Fields(i).Name
.Left = i * iWidthEach
.Width = iWidthEach
.Visible = True
End With
Next
'Clean up
Set qdf = Nothing
Set db = Nothing
End Sub
