Report based on a Crosstab Query

[Keith Wilby]

I have a report that is based upon a crosstab query which return only the
columns (fields) it has data for. When my report runs it sometimes fails
because some of the text boxes don't have a field in the query. I've
written some code to "fake" the missing field names which works perfectly
but is quite complex and I'm wondering if there's a simple light bulb
shining somewhere that I'm missing. The field names returned by the
crosstab are 0, 1, 2, 3, 4 and 5. Here's my code (don't laugh):

On Error GoTo errTrap
Const cProcedure = "Report_Open"

'The record source is a crosstab query which returns only the fields it has
data for so
'we need to fake the field names that are missing

Dim db As DAO.Database, rs As DAO.Recordset, strField As String

'Declare boolean flags to indicate whether or not a field name has been
established
Dim bln0Set As Boolean, bln1Set As Boolean, bln2Set As Boolean, bln3Set As
Boolean, bln4Set As Boolean, bln5Set As Boolean

'strField must not be empty at start
strField = "x"
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.OpenArgs)

With rs
.MoveFirst
Do Until .EOF

If (Not IsNull(![0])) And bln0Set = False Then
If strField <"" Then
strField = "0" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln0Set = True 'Set to true once field name has been set
End If
Me.txtLevel0.ControlSource = strField
strField = "x" 'Fill the string ready for next usage
End If

If (Not IsNull(![1])) And bln1Set = False Then
If strField <"" Then
strField = "1" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln1Set = True
End If
Me.txtLevel1.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![2])) And bln2Set = False Then
If strField <"" Then
strField = "2" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln2Set = True
End If
Me.txtLevel2.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![3])) And bln3Set = False Then
If strField <"" Then
strField = "3" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln3Set = True
End If
Me.txtLevel3.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![4])) And bln4Set = False Then
If strField <"" Then
strField = "4" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln4Set = True
End If
Me.txtLevel4.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![5])) And bln5Set = False Then
If strField <"" Then
strField = "5" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln5Set = True
End If
Me.txtLevel5.ControlSource = strField
strField = "x"
End If

.MoveNext
Loop
End With

Exit Sub

errTrap:
If Err.Number = 3265 Then
strField = ""
Resume Next 'Trap the error produced when the field doesn't exist
Else
Call libErrorHandler(Me.Name, cProcedure)
End If

[Allen Browne]

Open the crosstab in design view.
In the Properties box, type all the possible values beside the Column
Headings property.

The query now has those columns (and only those columns), regardless of
whether there is data for them or not, so the report will be fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Keith Wilby" <he**@there.comwrote in message
news:45**********@glkas0286.greenlnk.net...

>I have a report that is based upon a crosstab query which return only the
columns (fields) it has data for. When my report runs it sometimes fails
because some of the text boxes don't have a field in the query. I've
written some code to "fake" the missing field names which works perfectly
but is quite complex and I'm wondering if there's a simple light bulb
shining somewhere that I'm missing. The field names returned by the
crosstab are 0, 1, 2, 3, 4 and 5. Here's my code (don't laugh):

On Error GoTo errTrap
Const cProcedure = "Report_Open"

'The record source is a crosstab query which returns only the fields it
has data for so
'we need to fake the field names that are missing

Dim db As DAO.Database, rs As DAO.Recordset, strField As String

'Declare boolean flags to indicate whether or not a field name has been
established
Dim bln0Set As Boolean, bln1Set As Boolean, bln2Set As Boolean, bln3Set As
Boolean, bln4Set As Boolean, bln5Set As Boolean

'strField must not be empty at start
strField = "x"
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.OpenArgs)

With rs
.MoveFirst
Do Until .EOF

If (Not IsNull(![0])) And bln0Set = False Then
If strField <"" Then
strField = "0" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln0Set = True 'Set to true once field name has been set
End If
Me.txtLevel0.ControlSource = strField
strField = "x" 'Fill the string ready for next usage
End If

If (Not IsNull(![1])) And bln1Set = False Then
If strField <"" Then
strField = "1" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln1Set = True
End If
Me.txtLevel1.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![2])) And bln2Set = False Then
If strField <"" Then
strField = "2" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln2Set = True
End If
Me.txtLevel2.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![3])) And bln3Set = False Then
If strField <"" Then
strField = "3" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln3Set = True
End If
Me.txtLevel3.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![4])) And bln4Set = False Then
If strField <"" Then
strField = "4" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln4Set = True
End If
Me.txtLevel4.ControlSource = strField
strField = "x"
End If

If (Not IsNull(![5])) And bln5Set = False Then
If strField <"" Then
strField = "5" 'Only assign the value to the string if the
"no field" error hasn't occurred
bln5Set = True
End If
Me.txtLevel5.ControlSource = strField
strField = "x"
End If

.MoveNext
Loop
End With

Exit Sub

errTrap:
If Err.Number = 3265 Then
strField = ""
Resume Next 'Trap the error produced when the field doesn't exist
Else
Call libErrorHandler(Me.Name, cProcedure)
End If

[Keith Wilby]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45**********************@per-qv1-newsreader-01.iinet.net.au...

Open the crosstab in design view.
In the Properties box, type all the possible values beside the Column
Headings property.

The query now has those columns (and only those columns), regardless of
whether there is data for them or not, so the report will be fine.

Blimey, that's embarrassingly simple, can't you tell I've never used
crosstabs before? Many thanks Allen.

Regards,
Keith.