Show Chart Total in Report Header

[Alex]

A button on my user input form runs the following code, which opens a report,
sets query filters and controls in an unbound report. There’s a chart in the
report created from a crosstab query “ChartCodeLengthNHâ€. My customer has
requested a total count of all data in the chart; just one control containing
a grand total. Since I couldn’t use the ChartCodeLengthNH crosstab query, I
created an additional select query called "TotalCountCodeLengthNH", which has
a filter set by the code and a form that contains the total value. I then
created a control in my report where the total shows where the data source
=[Forms]![TotalCountsNH]![CountOfPeopleSoftID].

My problem is that using the code the way it is below, the report opens and
then several controls in the report header are set. But, the query
TotalCountCodeLengthNH filter doesn’t get set until the end of the code when
the report is already opened. I’ve tried closing then reopening the report,
but the controls at the top of the report are missing. It works fine the
first time, but if I close the report and add a different beginning and
ending date in the user input form, the total that shows in the report is the
total of the last date range.

I hope this makes sense. I’ve been hitting my head for 2 days and can’t
figure out a resolution. I will take any advise on how to get a control in
the report to show the totals. Thank you.

Private Sub RunChartCodeLength_Click()
Dim db As DAO.Database
Dim qdf_Chart As DAO.QueryDef
Dim qdf_Chart1 As DAO.QueryDef
Dim adf_Unfiltered As DAO.QueryDef
Dim sql1 As String
Dim sql2 As String
Dim VarItem As Variant
Dim StrGender As String
Dim StrEthnic As String
Dim strEducation As String
Dim strCode As String
Dim strJobTitle As String
Dim strLength As String
Dim strGroup As String
Dim strCounty As String
Dim datBegin As Date
Dim datEnd As Date
Dim strFilter As String
Dim StrFilterChart

Set db = CurrentDb
Set qdf_Unfiltered = db.QueryDefs("ChartCodeLengthNH")
Set qdf_Chart = db.QueryDefs("ChartCodeLengthNH")
Set qdf_Chart1 = db.QueryDefs("TotalCountCodeLengthNH")

'Check for Begin and End Date
If Len(Me.cmdBegin.Value & "") = 0 Then
MsgBox "You must type a beginning Hire Date"
Exit Sub
End If

If Len(Me.cmdEnd.Value & "") = 0 Then
MsgBox "You must type an ending Hire Date"
Exit Sub
End If

'Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "ReportCodeLengthNH") <>
acObjStateOpen Then
DoCmd.OpenReport "ReportCodeLengthNH", acViewPreview
End If

'Build Criteria string from Gender Listbox
For Each VarItem In Me.cmdGender.ItemsSelected
StrGender = StrGender & ",'" & Me.cmdGender.ItemData(VarItem) & "'"
Next VarItem
If Len(StrGender) = 0 Then
StrGender = "Like'*'"
Else
StrGender = Right(StrGender, Len(StrGender) - 1)
StrGender = "IN(" & StrGender & ")"
End If

'Build Criteria string from County Listbox
For Each VarItem In Me.cmdCounty.ItemsSelected
strCounty = strCounty & ",'" & Me.cmdCounty.ItemData(VarItem) & "'"
Next VarItem
If Len(strCounty) = 0 Then
strCounty = "Like'*'"
Else
strCounty = Right(strCounty, Len(strCounty) - 1)
strCounty = "IN(" & strCounty & ")"
End If

'Build Criteria string from Ethnic Listbox
For Each VarItem In Me.cmdEthnic.ItemsSelected
StrEthnic = StrEthnic & ",'" & Me.cmdEthnic.ItemData(VarItem) & "'"
Next VarItem
If Len(StrEthnic) = 0 Then
StrEthnic = "Like'*'"
Else
StrEthnic = Right(StrEthnic, Len(StrEthnic) - 1)
StrEthnic = "IN(" & StrEthnic & ")"
End If

'Build Criteria string from Education Listbox
For Each VarItem In Me.cmdEducation.ItemsSelected
strEducation = strEducation & ",'" & Me.cmdEducation.ItemData(VarItem) & "'"
Next VarItem
If Len(strEducation) = 0 Then
strEducation = "Like'*'"
Else
strEducation = Right(strEducation, Len(strEducation) - 1)
strEducation = "IN(" & strEducation & ")"
End If

'Build Criteria string from Code Listbox
For Each VarItem In Me.cmdCode.ItemsSelected
strCode = strCode & ",'" & Me.cmdCode.ItemData(VarItem) & "'"
Next VarItem
If Len(strCode) = 0 Then
strCode = "Like'*'"
Else
strCode = Right(strCode, Len(strCode) - 1)
strCode = "IN(" & strCode & ")"
End If

'Build Criteria string from JobTitle Listbox
For Each VarItem In Me.cmdJob.ItemsSelected
strJobTitle = strJobTitle & ",'" & Me.cmdJob.ItemData(VarItem) & "'"
Next VarItem
If Len(strJobTitle) = 0 Then
strJobTitle = "Like'*'"
Else
strJobTitle = Right(strJobTitle, Len(strJobTitle) - 1)
strJobTitle = "IN(" & strJobTitle & ")"
End If

'Build Criteria string from Length Category Listbox
For Each VarItem In Me.cmdLength.ItemsSelected
strLength = strLength & ",'" & Me.cmdLength.ItemData(VarItem) & "'"
Next VarItem
If Len(strLength) = 0 Then
strLength = "Like'*'"
Else
strLength = Right(strLength, Len(strLength) - 1)
strLength = "IN(" & strLength & ")"
End If

'Build Criteria string from Group Category Listbox
For Each VarItem In Me.cmdGroup.ItemsSelected
strGroup = strGroup & ",'" & Me.cmdGroup.ItemData(VarItem) & "'"
Next VarItem
If Len(strGroup) = 0 Then
strGroup = "Like'*'"
Else
strGroup = Right(strGroup, Len(strGroup) - 1)
strGroup = "IN(" & strGroup & ")"
End If


'Build Beginning and Ending date parameter
If Not IsNull(Me.cmdBegin) Then
datBegin = Me.cmdBegin
End If
If Not IsNull(Me.cmdEnd) Then
datEnd = Me.cmdEnd
End If

'Build filter string [name] is the table field name
strFilter = "[GenderDesc]" & StrGender & _
"AND [EthnicDescription]" & StrEthnic & _
"AND [EducationDescription]" & strEducation & _
"AND

" & strCode & _
"AND [JobTitle]" & strJobTitle & _
"AND [LengthCategory]" & strLength & _
"AND [GroupDescription]" & strGroup & _
"AND [County]" & strCounty & _
"AND [HireDate] Between #" & datBegin & "# and #" & datEnd & "#"


'Apply the filter and switch it on
With Reports![ReportCodeLengthNH]
..Filter = strFilter
..FilterOn = True
..TitleDate.Value = "New Hire Report for " & Me.cmdBegin.Value & "-" & _
Me.cmdEnd.Value
If StrGender = "Like'*'" Then .TitleGender.Value = "All Genders" Else
..TitleGender.Value = "Gender: " & StrGender
If strJobTitle = "Like'*'" Then .TitleJobTitle.Value = "All Job Title" Else
..TitleJobTitle.Value = "Job Title: " & strJobTitle
If strEducation = "Like'*'" Then .TitleEducation.Value = "All Education
Levels" Else .TitleEducation.Value = "Education: " & strEducation
If strLength = "Like'*'" Then .TitleLength.Value = "All Length of Service"
Else .TitleLength.Value = "Length of Service: " & strLength
If strGroup = "Like'*'" Then .TitleGroup.Value = "All Groups" Else
..TitleGroup.Value = "Groups: " & strGroup
If StrEthnic = "Like'*'" Then .TitleEthnic.Value = "All Ethnic Groups" Else
..TitleEthnic.Value = "Ethnics: " & StrEthnic
If strCode = "Like'*'" Then .TitleCode.Value = "All Codes" Else
..TitleCode.Value = "Codes: " & strCode
If strCounty = "Like'*'" Then .TitleCounty.Value = "All Counties" Else
..TitleCounty.Value = "Counties: " & strCounty

End With

qdf_Chart.SQL = "TRANSFORM Count(NewHireQuery.PeopleSoftID) AS
CountOfPeopleSoftID SELECT NewHireQuery.LengthCategory FROM
DistinctLengthCategoryNH INNER JOIN NewHireQuery ON
DistinctLengthCategoryNH.LengthCategory = NewHireQuery.LengthCategory WHERE"
& strFilter & " GROUP BY NewHireQuery.LengthCategory,
DistinctLengthCategoryNH.LengthOrder ORDER BY
DistinctLengthCategoryNH.LengthOrder PIVOT NewHireQuery.Code"

qdf_Chart1.SQL = "SELECT Count(NewHireQuery.PeopleSoftID) AS
CountOfPeopleSoftID FROM NewHireQuery INNER JOIN DistinctLengthCategory ON
NewHireQuery.LengthCategory = DistinctLengthCategory.LengthCategory WHERE" &
strFilter & ""

End Sub