If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dymanic Reports
Does anybody have any examples for Dynamic Reports??
Meaning I have a report with Month/Year accross the top. ie. 1/2005, 2/2005, 3/2005, 4/2005. If I change my date range the I would like the report to change its title to 2/2006, 3/2006, 4/2006, 5/2006 and so on. My problem is how to you make a dynamic title header that changes with a cross tab query grouped my month and year with field name change? Thank you in advance for your help. Mike |
#2
|
|||
|
|||
Dymanic Reports
The titles can be changes by reading the criteria for the change from an
external source, such as a dialog form, but your example is not a title change, it is a column header change. To do that you need to use code and build columns for the maximum number of columns in the report. Not for the faint at heart. Something like this from a working Xtab report: Option Compare Database 'Use database order for string comparisons. Option Explicit ' Constant for maximum number of columns qryResidence_XTab1 query would ' create plus 1 for a Totals column. Const conTotalColumns = 13 ' Variables for Database object and Recordset. Dim dbsReport As Database Dim rstReport As 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 Detail1_Format(Cancel As Integer, FormatCount As Integer) ' Place values in text boxes and hide unused text boxes. Dim intX As Integer ' Verify that not at end of recordset. If Not rstReport.EOF Then ' If FormatCount is 1, place 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 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 Detail1_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 = 2 To intColumnCount ' Starting at column 2 (first text box with crosstab value), ' compute total for current row in 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 ' Place row total in text box in 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 Detail1_Retreat() ' Always back up to previous record when detail section retreats. rstReport.MovePrevious End Sub 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 Next intX End Sub Private Sub Report_Activate() ' Hide built-in Print Preview toolbar. DoCmd.ShowToolbar "Print Preview", acToolbarNo End Sub Private Sub Report_Close() On Error Resume Next ' Close recordset. rstReport.Close End Sub Private Sub Report_Deactivate() ' Show built-in Print Preview toolbar. DoCmd.ShowToolbar "Print Preview", acToolbarWhereApprop 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 ' frmDateSelector form. Dim intX As Integer Dim qdf As QueryDef Dim frm As Form ' Don't open report if frmDateSelector form isn't loaded. If Not (IsLoaded("frmDateSelector")) Then Cancel = True MsgBox "To preview or print this report, you must open " _ & "frmDateSelector in Form view.", vbExclamation, _ "Must Open Dialog Box" Exit Sub End If ' Set database variable to current database. Set dbsReport = CurrentDb Set frm = Forms!frmDateSelector ' Open QueryDef object. Set qdf = dbsReport.QueryDefs("qryResidence_XTab") ' Set parameters for query based on values entered ' in frmDateSelector form. qdf.Parameters("Forms!frmDateSelector!BeginningDat e") _ = frm!BeginningDate qdf.Parameters("Forms!frmDateSelector!EndingDate") _ = frm!EndingDate ' 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 ReportFooter4_Print(Cancel As Integer, PrintCount As Integer) Dim intX As Integer ' Place 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 ' Place 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 ReportHeader3_Format(Cancel As Integer, FormatCount As Integer) ' Move to first record in recordset at beginning of report ' or when 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 Dim intX As Integer ' Put column headings into text boxes in report 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)) = "Totals" ' Hide unused text boxes in report header. For intX = (intColumnCount + 2) To conTotalColumns Me("Head" + Format(intX)).Visible = False Next intX End Sub Private Function xtabCnulls(varX As Variant) ' 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 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Michael Kintner" wrote in message ... Does anybody have any examples for Dynamic Reports?? Meaning I have a report with Month/Year accross the top. ie. 1/2005, 2/2005, 3/2005, 4/2005. If I change my date range the I would like the report to change its title to 2/2006, 3/2006, 4/2006, 5/2006 and so on. My problem is how to you make a dynamic title header that changes with a cross tab query grouped my month and year with field name change? Thank you in advance for your help. Mike |
Thread Tools | |
Display Modes | |
|
|