Creating a dynamic crosstab report

[koolwaters]

I have a report whose record source is a crosstab query. This report is displaying training done by Territory and I would have added labels and controls for all of the Territories. I have to submit monthly reports and in a particular month there may be several Territories which have not done any training.

When I try to preview the report, I get errors because the crosstab query does not contain the Territories where no training was done.

Is there a way to create a report and have the labels and control "changed" to match the Territories in the crosstab query? The Territory is the column heading in the crosstab query.

Please help!!!

 

[SydneyGeek]

You don't have to make it dynamic. Instead you can force all the headings to display.

Open the query in Design view
In the Territories field, right-click below the grid and choose Properties.
In the Column Headers row, enter all of your territories, in quotes and separated by commas, eg: "South","North","East","West"

Save the query and you're good to go.

Denis

 

[koolwaters]

Wow! I am absolutely bowled over.

Thank you so very much. Your method worked.

 

[evert]

Is there a way here to make sure that all the values, that occur in the Territory field
of the source table, also will occur as column heading - even if I forgot to type one of these values in the properties wizard?

 

[SydneyGeek]

If you leave it out of the column headers, you won't see it.

Does this mean that your headers will change every time? Because if that's the case you will need to get devious.
And will there always be the same number of headings in the report?

Denis

 

[koolwaters]

Hi,

I have another question based an a dynamic report.

How can I create the dynamic report if I do not know what my column headings will be?

My crosstab report displays years as columns and I need to create a report with these same years as headings?

 

[Kreszch68]

Create a report based on the query with the dynamic fields. (the yearly amounts in this case).

Place all fields you need from the query, except from the dynamic fields.
For the dynamic fields do the following:

Create labels for the number of years you want to show in your report.
Say you want to display 5 years, make 5 labels with the prefix as shown in de code below.
Then create an unbound textbox for each year, name them with the prefix as shown in the code below.

Place the next code in the load event of your report, run report

Private Sub Report_Load()
'Code by Johan Kreszner, MIO-Software Netherlands
'Sets labels and controlsources for dynamic fields
'lblAmtY = prefix for the labels in the header
'valAmtY = prefix for unbound controls
'Create a report with labels like lblAmtY1, lblAmtY2, lblAmtY3, lblAmtY4, lblAmtY5
'and unbound textbox controls like valAmtY1, valAmtY2, valAmtY3, valAmtY4, valAmtY5
'Important is :that the prefix is correct, what comes next doesn't matter for this code, as long as the controls have unique names
'             :the number of years fetched is equal or larger than the number of columns created in the report
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sDynaFldNames() As String
Dim iDynaFldCnt As Integer
Dim sTmpHeaders() As String
Dim iTmpHeaderCnt As Integer
Dim sTmpControls() As String
Dim iTmpCtrlCnt As Integer
Dim sQName As String
Dim c As Control
Set dbs = CurrentDb
iTmpHeaderCnt = 0
iTmpCtrlCnt = 0
For Each c In Me.Controls
   
    If Left(c.Name, 7) = "lblAmtY" Then
        ReDim Preserve sTmpHeaders(iTmpHeaderCnt)
        sTmpHeaders(iTmpHeaderCnt) = c.Name
        iTmpHeaderCnt = iTmpHeaderCnt + 1
    End If
       
   If Left(c.Name, 7) = "valAmtY" Then
        ReDim Preserve sTmpControls(iTmpCtrlCnt)
        sTmpControls(iTmpCtrlCnt) = c.Name
        iTmpCtrlCnt = iTmpCtrlCnt + 1
   End If
   
Next c
   
sQName = Me.RecordSource
Set qdf = dbs.QueryDefs(sQName)
iDynaFldCnt = 0
For Each fld In qdf.Fields
        If InStr(1, fld.Name, sTrail, vbTextCompare) <> 0 Then 'Note: sTrail is a constant, " - Amt in (US$)"
        ReDim Preserve sDynaFldNames(iDynaFldCnt)
        sDynaFldNames(iDynaFldCnt) = fld.Name
        iDynaFldCnt = iDynaFldCnt + 1
        End If
Next fld
''set labels and control source
For iTmpHeaderCnt = 0 To UBound(sTmpHeaders)
    
    Me.Controls(sTmpHeaders(iTmpHeaderCnt)).Caption = sDynaFldNames(iTmpHeaderCnt)
    Me.Controls(sTmpControls(iTmpHeaderCnt)).ControlSource = sDynaFldNames(iTmpHeaderCnt)
   
Next iTmpHeaderCnt

End Sub

 

[koolwaters]

I get an Run-time error '2191': You can't set the Control Source property in print preview or after printing has started. The line of code below is highlighted when I debug.

Me.Controls(sTmpControls(iTmpHeaderCnt)).ControlSource = sDynaFldNames(iTmpHeaderCnt)

When I place the code in the OnOpen property of the form, it works fine.

 

[Kreszch68]

You're right, if it's in the load event you can't print the report.

So the right sequence is:

Open
Print

 

[koolwaters]

This does not solve the problem though.

When my year increases, I still have to add additional labels and controls. So if I have someone who's program length is 6 years, it means I have to add a label and control.

I tried adding 15 labels and controls and I got an error.