Microsoft KB Archive/328320

= How to create a dynamic crosstab report in Access 2002 =

Article ID: 328320

Article Last Modified on 8/3/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q328320



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

IN THIS TASK
SUMMARY
 * Create a Query That Is Named OrderDetailsExtended
 * Create a Query That Is Named EmployeeSales
 * Create a Form That Is Named EmployeeSalesDialogBox
 * Create a Report That Is Named EmployeeSales

REFERENCES



SUMMARY
You can use Microsoft Access 2002 to create dynamic reports that are based on parameter crosstab queries. You can also create reports to match a dynaset that is returned by such a query. Dynamic reports allow your customized reports to show only the most recently modified data. This gets rid of the need for fixed column headings and empty columns.

The following example uses starting dates and ending dates that are entered on a form as the parameters in a crosstab query. When a button on the form is chosen, Microsoft Visual Basic for Applications (VBA) functions run the crosstab query that creates a dynaset. The contents of the dynaset are then presented in a report.

In the following example, the report shows the employees that have sales for a certain period of time. The employees that appear in the report are based on the dates that are entered on the form. The steps below show how to create a dynamic crosstab report based on tables in the sample database Northwind.mdb.

The following new objects must be added to the database:
 * two queries
 * one form
 * one report
 * two functions

Each item is explained in a separate section that follows.

back to the top

Create a Query That Is Named OrderDetailsExtended
You can create a new select query that is based on the Order Details table and the Products table. These tables are already joined based on previously created relationships in the Northwind database. To create a new select query, follow these steps:  In the Database window, click Queries under Objects and then double-click Create query in Design view. Add the Order Details table and the Products table.  Drag the following fields to the query grid and then add the following values:   Field: OrderID Table: Order Details Field: ProductName Table: Products Field: ProductID Table: Order Details Field: UnitPrice Table: Order Details Field: Quantity Table: Order Details Field: Discount Table: Order Details Field: ExtendedPrice: CCur(CLng([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])*100)/100)  Save the query as OrderDetailsExtended and then close the query.

back to the top

Create a Query That Is Named EmployeeSales
You can create a new crosstab query that is based on the Employees table, the Orders table, the OrderDetailsExtended Query, and the Products table. These tables are already joined based on previously created relationships in the Northwind database. To create a new crosstab query, follow these steps:  In the Database window, click Queries under Objects and then double-click Create query in Design view.</li> Add the Employees table, the Orders table, the OrderDetailsExtended Query, and the Products table.</li> On the Query menu, click Crosstab Query.</li>  Drag the following fields to the query grid and then add the following values: <pre class="fixed_text">     Field: LastName Table: Employees Total: Group By        Crosstab: Column Heading Field: ProductName Table: Products Total: Group By        Crosstab: Row Heading Field: Order Amount: ExtendedPrice Table: OrderDetailsExtended Total: Sum Crosstab: Value Field: ShippedDate Table: Orders Total: Where Crosstab: Criteria: Between [Forms]![EmployeeSalesDialogBox]![BeginningDate] And [Forms]![EmployeeSalesDialogBox]![EndingDate] </li> From the Query menu, click Parameters.</li>  In the Parameters dialog box, add the following entries: <pre class="fixed_text">  Parameter: [Forms]![EmployeeSalesDialogBox]![BeginningDate] Data Type: Date/Time Parameter: [Forms]![EmployeeSalesDialogBox]![EndingDate] Data Type: Date/Time </li> Close the Parameters dialog box.</li> Save the query as EmployeeSales and then close the query.</li></ol>

back to the top

Create a Form That Is Named EmployeeSalesDialogBox
 In the Database window, click Forms, and then click New.</li> In the New Form dialog box, click Design View, and then click OK.</li>  Add two unbound text box controls with the following properties: <pre class="fixed_text">  Text Box 1: ControlName: BeginningDate Text Box 2: ControlName: EndingDate </li>  Add a command button to the form with the following properties. If the Command Button Wizard starts, click Cancel. <pre class="fixed_text">  Name: Command4 Caption: &quot;Employee Sales Crosstab&quot; </li>  Set the OnClick property of the command button to the following event procedure: Private Sub Command4_Click Dim stDocName As String Dim accobj As AccessObject On Error GoTo Err_Command4_Click

stDocName = &quot;EmployeeSales&quot; 'This function closes the report if the report is open and then re-opens the report. Set accobj = Application.CurrentProject.AllReports.Item(stDocName) If accobj.IsLoaded Then If accobj.CurrentView = acCurViewPreview Then DoCmd.Close acReport, stDocName DoCmd.OpenReport stDocName, acPreview End If Else DoCmd.OpenReport stDocName, acPreview End If

Exit_Command4_Click: Exit Sub

Err_Command4_Click: MsgBox Err.Description Resume Exit_Command4_Click

End Sub

</li> Close the Microsoft Visual Basic Editor.</li> Save the form as EmployeeSalesDialogBox and then close the form.</li></ol>

back to the top

Create a Report That Is Named EmployeeSales
Note When you put the text boxes on the report for steps 4, 5, and 6, put them horizontally.  <li>In the Database window, click Reports, and then click New.</li> <li>In the New Report dialog box, click Design View, click to select the EmployeesSales query in the Choose the table or query where the object's data comes from check box, and then click OK.

Note When you receive an Enter Parameter Value dialog box, click Cancel.</li> <li>To add a report footer section, click Report Header/Footer on the View menu.</li> <li>Assume that there are nine records in the Employees table. Then, in the page header, create 11 unbounded text box controls without labels. Set the Name property of the left text box to &quot;Head1&quot; and the Name property of the next text box to &quot;Head2&quot;. Continue until you complete &quot;Head11&quot;.</li> <li>In the &quot;Detail&quot; section, create 11 unbounded text box controls without labels (one text box for the row heading, nine text boxes for the maximum number of employees in the Northwind database Employees table, and one text box for the row total). Set the Name property of the left text box to &quot;Col1&quot; and the Name property of the next text box to &quot;Col2&quot;. Continue until you complete &quot;Col11&quot;. Set the Format property of the text boxes &quot;Col2&quot; through &quot;Col11&quot; to Standard.</li> <li>In the report footer, create 11 unbounded text box controls without labels. Set the Name property of the left text box to &quot;Tot1&quot;. In the Control Source property of Tot1, type =&quot;Totals:&quot;. Set the Name property of the remaining text boxes that display the column totals to &quot;Tot2&quot; through &quot;Tot11&quot;. Set the Format property of text boxes &quot;Tot2&quot; through &quot;Tot11&quot; to Standard.</li> <li> On the View menu, click Code.

You see the VBA code window for the report.

Type or paste the following code to the code window:NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

' Constant for maximum number of columns EmployeeSales query would ' create plus 1 for a Totals column. Here, you have 9 employees. 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 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

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 &quot;Detail&quot; section. If Me.FormatCount = 1 Then For intX = 1 To intColumnCount ' Convert Null values to 0. Me(&quot;Col&quot; + Format(intX)) = xtabCnulls(rstReport(intX - 1)) Next intX ' Hide unused text boxes in the &quot;Detail&quot; section. For intX = intColumnCount + 2 To conTotalColumns Me(&quot;Col&quot; + 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 = 2 To intColumnCount ' Starting at column 2 (first text box with crosstab value), ' compute total for current row in the &quot;Detail&quot; section. lngRowTotal = lngRowTotal + Me(&quot;Col&quot; + Format(intX))

' Add crosstab value to total for current column. lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me(&quot;Col&quot; + Format(intX)) Next intX ' Put row total in text box in the &quot;Detail&quot; section. Me(&quot;Col&quot; + 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 &quot;Detail&quot; section retreats. rstReport.MovePrevious

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(&quot;Head&quot; + Format(intX)) = rstReport(intX - 1).Name Next intX

' Make next available text box Totals heading. Me(&quot;Head&quot; + Format(intColumnCount + 1)) = &quot;Totals&quot;

' Hide unused text boxes in page header. For intX = (intColumnCount + 2) To conTotalColumns Me(&quot;Head&quot; + 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 &quot;No records match the criteria you entered.&quot;, vbExclamation, &quot;No Records Found&quot; 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 frm = Forms!EmployeeSalesDialogBox ' Open QueryDef object. Set qdf = dbsReport.QueryDefs(&quot;EmployeeSales&quot;) ' Set parameters for query based on values entered ' in EmployeeSalesDialogBox form. qdf.Parameters(&quot;Forms!EmployeeSalesDialogBox!BeginningDate&quot;) _ = frm!BeginningDate qdf.Parameters(&quot;Forms!EmployeeSalesDialogBox!EndingDate&quot;) _ = 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 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(&quot;Tot&quot; + Format(intX)) = lngRgColumnTotal(intX) Next intX

' Put grand total in text box in report footer. Me(&quot;Tot&quot; + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer. For intX = intColumnCount + 2 To conTotalColumns Me(&quot;Tot&quot; + 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 </li> <li> The following event procedures are set for the report. <pre class="fixed_text">  Report/Section         Property          Setting Report                OnOpen            [Event Procedure] OnClose          [Event Procedure] OnNoData         [Event Procedure] Report Header         OnFormat          [Event Procedure] Page Header           OnFormat          [Event Procedure] Detail Section        OnFormat          [Event Procedure] OnPrint          [Event Procedure] OnRetreat        [Event Procedure] Report footer         OnPrint           [Event procedure] </li> <li>Save the Report as EmployeeSales. When you are prompted to enter the parameter values, click Cancel and then close the report.</li></ol>

After you create the new database objects that are specified earlier, you can open the EmployeeSalesDialogBox form. You can enter starting dates and ending dates on the form. Use a date range from 7/10/1996 through 05/06/1998.

After you enter the date range, click Employee Sales Crosstab on the form to preview your dynamic report.

back to the top

<div class="references_section">