Crosstab Report's subreport

  • Thread starter Thread starter Joseph
  • Start date Start date
J

Joseph

I have a report based on the following code:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim dStart, DayOne, DayTwo, DayThree, DayFour, DayFive, DaySix, DaySeven
As Variant
Dim stSql String

dStart = Forms!PrintReport.Friday

DayOne = dStart
DayTwo = DateAdd("d", 1, DayOne)
DayThree = DateAdd("d", 2, DayOne)
DayFour = DateAdd("d", 3, DayOne)
DayFive = DateAdd("d", 4, DayOne)
DaySix = DateAdd("d", 5, DayOne)
DaySeven = DateAdd("d", 6, DayOne)

stSql = "TRANSFORM Max(MeritsMain2Total.Total) AS MaxOfTotal SELECT
Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm FROM
Dorms INNER JOIN ((Phase INNER JOIN (CadetsName INNER JOIN Cadets ON
CadetsName.CadetID = Cadets.CadetID) ON Phase.PhaseID = Cadets.PhaseID) INNER
JOIN MeritsMain2Total ON Cadets.CadetID = MeritsMain2Total.CadetID) ON
(Dorms.DormID = CadetsName.DormID) AND (Dorms.DormID = Cadets.DormID) GROUP
BY Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm PIVOT
Format([DTGofMerits],'Short Date') In ('" & DayOne & "','" & DayTwo & "','" &
DayThree & "','" & DayFour & "','" & DayFive & "','" & DaySix & "','" &
DaySeven & "');"
Me.RecordSource = stSql

Me.DayOne.ControlSource = DayOne
Me.DayOneLabel.Caption = DayOne
Me.DayTwo.ControlSource = DayTwo
Me.DayTwoLabel.Caption = DayTwo
Me.DayThree.ControlSource = DayThree
Me.DayThreeLabel.Caption = DayThree
Me.DayFour.ControlSource = DayFour
Me.DayFourLabel.Caption = DayFour
Me.DayFive.ControlSource = DayFive
Me.DayFiveLabel.Caption = DayFive
Me.DaySix.ControlSource = DaySix
Me.DaySixLabel.Caption = DaySix
Me.DaySeven.ControlSource = DaySeven
Me.DaySevenLabel.Caption = DaySeven

End_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume End_Report_Open

End Sub

And believe it or not, it works. The report is based on a saved crosstab,
but I wanted to limit the fields dynamically. I also have a subreport that
is based on a saved query("SELECT DISTINCTROW CadetAwards.CadetID,
AwardType.Abbrev, Count(CadetAwards.AwardType) AS CountOfAwardType
FROM CadetAwards INNER JOIN AwardType ON CadetAwards.AwardType =
AwardType.AwardType
GROUP BY CadetAwards.CadetID, AwardType.Abbrev, AwardType.AwardType
HAVING (((([AwardType].[AwardType])=6 Xor
([AwardType].[AwardType])>=10)<>False));"). Now I thought that I could just
easily set the link fields to show linked information (CadetID->CadetID), but
when I run the report, the subreport does not show any information.

How do I get the subreport to show? I understand that if there is not data
to show that it should be blank, but there is data.
 
It looks like you might have a crosstab that uses a week of dates as columns
headings. If this is the case, I think the solution at
http://www.tek-tips.com/faqs.cfm?fid=5466 is much more efficient and simpler
(no code required). It should work well as a subreport without having to
change any Record Sources.


--
Duane Hookom
MS Access MVP


Joseph said:
I have a report based on the following code:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim dStart, DayOne, DayTwo, DayThree, DayFour, DayFive, DaySix,
DaySeven
As Variant
Dim stSql String

dStart = Forms!PrintReport.Friday

DayOne = dStart
DayTwo = DateAdd("d", 1, DayOne)
DayThree = DateAdd("d", 2, DayOne)
DayFour = DateAdd("d", 3, DayOne)
DayFive = DateAdd("d", 4, DayOne)
DaySix = DateAdd("d", 5, DayOne)
DaySeven = DateAdd("d", 6, DayOne)

stSql = "TRANSFORM Max(MeritsMain2Total.Total) AS MaxOfTotal SELECT
Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm FROM
Dorms INNER JOIN ((Phase INNER JOIN (CadetsName INNER JOIN Cadets ON
CadetsName.CadetID = Cadets.CadetID) ON Phase.PhaseID = Cadets.PhaseID)
INNER
JOIN MeritsMain2Total ON Cadets.CadetID = MeritsMain2Total.CadetID) ON
(Dorms.DormID = CadetsName.DormID) AND (Dorms.DormID = Cadets.DormID)
GROUP
BY Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm
PIVOT
Format([DTGofMerits],'Short Date') In ('" & DayOne & "','" & DayTwo &
"','" &
DayThree & "','" & DayFour & "','" & DayFive & "','" & DaySix & "','" &
DaySeven & "');"
Me.RecordSource = stSql

Me.DayOne.ControlSource = DayOne
Me.DayOneLabel.Caption = DayOne
Me.DayTwo.ControlSource = DayTwo
Me.DayTwoLabel.Caption = DayTwo
Me.DayThree.ControlSource = DayThree
Me.DayThreeLabel.Caption = DayThree
Me.DayFour.ControlSource = DayFour
Me.DayFourLabel.Caption = DayFour
Me.DayFive.ControlSource = DayFive
Me.DayFiveLabel.Caption = DayFive
Me.DaySix.ControlSource = DaySix
Me.DaySixLabel.Caption = DaySix
Me.DaySeven.ControlSource = DaySeven
Me.DaySevenLabel.Caption = DaySeven

End_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume End_Report_Open

End Sub

And believe it or not, it works. The report is based on a saved crosstab,
but I wanted to limit the fields dynamically. I also have a subreport
that
is based on a saved query("SELECT DISTINCTROW CadetAwards.CadetID,
AwardType.Abbrev, Count(CadetAwards.AwardType) AS CountOfAwardType
FROM CadetAwards INNER JOIN AwardType ON CadetAwards.AwardType =
AwardType.AwardType
GROUP BY CadetAwards.CadetID, AwardType.Abbrev, AwardType.AwardType
HAVING (((([AwardType].[AwardType])=6 Xor
([AwardType].[AwardType])>=10)<>False));"). Now I thought that I could
just
easily set the link fields to show linked information (CadetID->CadetID),
but
when I run the report, the subreport does not show any information.

How do I get the subreport to show? I understand that if there is not
data
to show that it should be blank, but there is data.
Click to expand...
 
Back
Top