still no dynamic crosstab report

[Brigitte P]

I have posted here before about this topic but the help I got didn't work
because I work with DAO library. Following is the code that I'm using. it
gets hung up in the line: Set rst =
CurrentDb.OpenRecordset(Me.RecordSource) and the debug tells me that my query
is the problem. Therefore, I also added the SQL for all my queries. The
offending query is qryEventsReportCard_Crosstab which is based on the
preceeding two queries. My user wants to see Events that occur in the time
period selected, and then also see the 6 and 12 mo. average for the month
preceeding the start date selected. The only way I can think of doing this is
doing one query that calcualtes these averages and then another one that sets
criteria for the selected time frame (which includes injuries or all) and
join them by event type and unit. The crosstab runs great, but the report
won't come out because it doesn't like this crosstab querie. The events, unit
and averages (6 and 12 mo) would be static, while the new events need to be
dynamic. I'm really getting big headaches over this, so please, someone have
mercy on me.
Thanks.
Brigitte P.

Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim intFields As Integer
Dim intControls As Integer
Dim N As Integer

' Open a recordset for the crosstab query.
Debug.Print Me.RecordSource
Set rst = CurrentDb.OpenRecordset(Me.RecordSource)

' Find the number of text boxes available in the Detail
' section, minus 4 because we don't count the first 4 fields that are
row headings in the crosstab.
intControls = Me.Detail.Controls.Count - 4

' Find the number of fields, minus 1, because we don't
' count the row header field (City)
intFields = rst.Fields.Count - 4

' We can't use more than intControls number of fields
If intFields > intControls Then
intFields = intControls
End If

' Iterate through report fields to set label captions
' and field control sources.
For N = 1 To intControls
If N <= intFields Then
Me.Controls("Label" & N).Caption = rst.Fields(N).Name
Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name
Else
' Hide extra controls.
Me.Controls("Label" & N).Visible = False
Me.Controls("Field" & N).Visible = False
End If
Next N
rst.Close
End Sub


1. qryEventsReportCard


PARAMETERS [Forms]![frmSwitchboard]![txtUnit] Text ( 255 ),
[Forms]![frmSwitchboard]![txtEventType] Text ( 255 ),
[Forms]![frmSwitchboard]![txtInjury] Text ( 255 ),
[Forms]![frmSwitchboard]![txtStartDate] DateTime,
[Forms]![frmSwitchboard]![txtEndDate] DateTime;
SELECT CliEventTable.EventEvent, CliEventTable.EventIllnessInjury,
CliEventTable.EventDate, Format([EventDate],"yyyy mm") AS MonthNumber,
CliEventTable.EventBuilding, IIf([EventDate] Between
(DateAdd("m",-12,[Forms]![frmSwitchboard]![txtStartDate])) And
[Forms]![frmSwitchboard]![txtStartDate],1,0) AS Twelve, IIf([EventDate]
Between (DateAdd("m",-6,[Forms]![frmSwitchboard]![txtStartDate])) And
[Forms]![frmSwitchboard]![txtStartDate],1,0) AS Six
FROM CliEventTable
WHERE (((CliEventTable.EventEvent) Like
[Forms]![frmSwitchboard]![txtEventType] & "*") AND
((CliEventTable.EventIllnessInjury)<>"None apparent") AND
((CliEventTable.EventDate) Between
(DateAdd("m",-12,[Forms]![frmSwitchboard]![txtStartDate])) And
[Forms]![frmSwitchboard]![txtEndDate]) AND ((CliEventTable.EventBuilding)
Like [Forms]![frmSwitchboard]![txtUnit] & "*"));

2 qryEventReportCard12_6Sum

PARAMETERS [Forms]![frmSwitchboard]![txtStartDate] DateTime,
[Forms]![frmSwitchboard]![txtEndDate] DateTime;
SELECT qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent,
Sum(qryEventsReportCard.Twelve) AS SumOfTwelve, Sum(qryEventsReportCard.Six)
AS SumOfSix
FROM qryEventsReportCard
GROUP BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent
ORDER BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent;



2. qryEventsReportCard_Crosstab (This seems to be the offending record
source; it is based on the above two queries.

PARAMETERS [Forms]![frmSwitchboard]![txtUnit] Text ( 255 ),
[Forms]![frmSwitchboard]![txtEventType] Text ( 255 ),
[Forms]![frmSwitchboard]![txtInjury] Text ( 255 ),
[Forms]![frmSwitchboard]![txtStartDate] DateTime,
[Forms]![frmSwitchboard]![txtEndDate] DateTime;
TRANSFORM Count(qryEventsReportCard.EventEvent) AS CountOfEventEvent
SELECT qryEventsReportCard.EventBuilding AS Unit,
qryEventsReportCard.EventEvent AS Event, qryEventReportCard12_6Sum.SumOfSix
AS [6 Mo Tot], [SumOfSix]/6 AS [6 Mo Avg],
qryEventReportCard12_6Sum.SumOfTwelve AS [12 Mo Tot], [SumOfTwelve]/12 AS [12
Mo Avg]
FROM qryEventsReportCard INNER JOIN qryEventReportCard12_6Sum ON
(qryEventsReportCard.EventEvent = qryEventReportCard12_6Sum.EventEvent) AND
(qryEventsReportCard.EventBuilding = qryEventReportCard12_6Sum.EventBuilding)
WHERE (((qryEventsReportCard.EventDate) Between
[Forms]![frmSwitchboard]![txtStartDate] And
[Forms]![frmSwitchboard]![txtEndDate]))
GROUP BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent,
qryEventReportCard12_6Sum.SumOfSix, [SumOfSix]/6,
qryEventReportCard12_6Sum.SumOfTwelve, [SumOfTwelve]/12
ORDER BY qryEventsReportCard.EventBuilding, qryEventsReportCard.EventEvent
PIVOT qryEventsReportCard.MonthNumber;