Maybe you can initialize a counter to 0 in the GroupHeader, sum the
individual value in the Print event of the Group detail to the counter, and
display what is in the counter in the print event of the GroupFooter. Sure,
you can define 'n' counters, one per column, or an array of 'n' counters, if
you have to cumulate 'n' columns. Using the print event of the detail seems
to make irrelevant to consider the possible 'Retreat' actions (you will have
to if you were using the Format event of the details section).
Vanderghast, Access MVP
"aeg" <(E-Mail Removed)> wrote in message
news:77A57879-88BF-47AD-8BFD-(E-Mail Removed)...
> Hi
> I am trying to generate a crostab report but becuase the cloumn heading
> are
> changing based on the user selection, I had to generate the report
> headings/totals dynamically. I found the code below on microsoft site
> which
> works for most of the things i wanted to do (with slight modifications).
> However i couldn't work out how to addd sub-totals to my GroupFooter.
>
> Any help/comments greatly appreciated.
>
>
>>>>>>>>>>>>>>>>>>>>>
> Option Compare Database
> Option Explicit
>
> ' Constant for maximum number of columns
> ' create plus 1 for a Totals column.
> Const conTotalColumns = 11
>
> ' Variables for Database object and Recordset.
> Dim dbsReport As DAO.Database
> Dim rstReport As DAO.Recordset
>
> ' Variables for number of columns and row and report totals.
> Dim intColumnCount As Integer
>
> Dim lngRgColumnTotal(1 To conTotalColumns) As Long
> Dim lngReportTotal As Long
>
>
> Private Sub InitVars()
>
> Dim intX As Integer
>
> ' Initialize lngReportTotal variable.
> lngReportTotal = 0
>
> ' Initialize array that stores column totals.
> For intX = 1 To conTotalColumns
> lngRgColumnTotal(intX) = 0
> 'Debug.Print lngRgColumnTotal(intX)
> Next intX
>
> End Sub
>
>
> Private Function xtabCnulls(varX As Variant)
>
> 'Debug.Print varX
> ' Test if a value is null.
> If IsNull(varX) Then
> ' If varX is null, set varX to 0.
> xtabCnulls = 0
> Else
> ' Otherwise, return varX.
> xtabCnulls = varX
> End If
>
> End Function
>
>
> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> ' Put values in text boxes and hide unused text boxes.
>
> Dim intX As Integer
> ' Verify that you are not at end of recordset.
> If Not rstReport.EOF Then
> ' If FormatCount is 1, put values from recordset into text boxes
> ' in "Detail" section.
> If Me.FormatCount = 1 Then
> For intX = 1 To intColumnCount
> ' Convert Null values to 0.
> Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
> Next intX
>
> ' Hide unused text boxes in the "Detail" section.
> For intX = intColumnCount + 2 To conTotalColumns
> Me("Col" + Format(intX)).Visible = False
> Next intX
>
> ' Move to next record in recordset.
> rstReport.MoveNext
> End If
> End If
>
> End Sub
>
>
> Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
>
> Dim intX As Integer
> Dim lngRowTotal As Long
>
> ' If PrintCount is 1, initialize rowTotal variable.
> ' Add to column totals.
> If Me.PrintCount = 1 Then
>
> lngRowTotal = 0
>
> For intX = 3 To intColumnCount
> ' Starting at column 2 (first text box with crosstab value),
> ' compute total for current row in the "Detail" section.
> lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
>
> ' Add crosstab value to total for current column.
> lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" +
> Format(intX))
>
> Next intX
>
> ' Put row total in text box in the "Detail" section.
> Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
> ' Add row total for current row to grand total.
> lngReportTotal = lngReportTotal + lngRowTotal
> End If
> End Sub
>
>
> Private Sub Detail_Retreat()
>
> ' Always back up to previous record when "Detail" section retreats.
> rstReport.MovePrevious
>
> End Sub
>
>
> Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As Integer)
>
> 'some code to go in here to populate the group footer totals
>
>
> End Sub
>
>
> Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
> Integer)
>
> Dim intX As Integer
>
> ' Put column headings into text boxes in page header.
> For intX = 1 To intColumnCount
> Me("Head" + Format(intX)) = rstReport(intX - 1).Name
> Next intX
>
> ' Make next available text box Totals heading.
> Me("Head" + Format(intColumnCount + 1)) = "Total"
>
> ' Hide unused text boxes in page header.
> For intX = (intColumnCount + 2) To conTotalColumns
> Me("Head" + Format(intX)).Visible = False
> Next intX
>
> End Sub
>
>
> Private Sub Report_Close()
>
> On Error Resume Next
>
> ' Close recordset.
> rstReport.Close
>
> End Sub
>
>
> Private Sub Report_NoData(Cancel As Integer)
>
> MsgBox "No records match the criteria you entered.", vbExclamation, "No
> Records Found"
> rstReport.Close
> Cancel = True
>
> End Sub
>
>
> Private Sub Report_Open(Cancel As Integer)
>
> ' Create underlying recordset for report using criteria entered in
> ' EmployeeSalesDialogBox form.
>
> Dim intX As Integer
> Dim qdf As QueryDef
> Dim frm As Form
>
> ' Set database variable to current database.
> Set dbsReport = CurrentDb
> Set qdf = dbsReport.QueryDefs("qry_BatchDetailsCrosstab")
>
> ' Set parameters for query based on values entered in form.
> qdf.Parameters("Forms!frm_MenuSteps!cbx_BatchId") =
> Forms!frm_MenuSteps!cbx_BatchId
>
>
> ' Open Recordset object.
> Set rstReport = qdf.OpenRecordset()
>
> ' Set a variable to hold number of columns in crosstab query.
> intColumnCount = rstReport.Fields.Count
>
> End Sub
>
>
> Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
>
> Dim intX As Integer
>
> ' Put column totals in text boxes in report footer.
> ' Start at column 2 (first text box with crosstab value).
> For intX = 2 To intColumnCount
> Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
> Next intX
>
> ' Put grand total in text box in report footer.
> Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal
>
> ' Hide unused text boxes in report footer.
> For intX = intColumnCount + 2 To conTotalColumns
> Me("Tot" + Format(intX)).Visible = False
> Next intX
>
> End Sub
>
>
> Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
>
> ' Move to first record in recordset at the beginning of the report
> ' or when the report is restarted. (A report is restarted when
> ' you print a report from Print Preview window, or when you return
> ' to a previous page while previewing.)
> rstReport.MoveFirst
>
> 'Initialize variables.
> InitVars
>
> End Sub
|
Similar Threads
