How to create a dynamic crosstab report in Access 2002

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

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.


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:
  1. In the Database window, click Queriesunder Objects and then double-click Create query in Design view.
  2. Add the Order Details table and the Products table.
  3. 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)
  4. Save the query as
    OrderDetailsExtended and then close the query.

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:
  1. In the Database window, click Queriesunder Objects and then double-click Create query in Design view.
  2. Add the Employees table, the Orders table, the OrderDetailsExtended Query, and the Products table.
  3. On the Query menu, click Crosstab Query.
  4. Drag the following fields to the query grid and then add the following values:

    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]
  5. From the Query menu, click
    Parameters.
  6. In the Parameters dialog box, add the following entries:

    Parameter: [Forms]![EmployeeSalesDialogBox]![BeginningDate]
    Data Type: Date/Time

    Parameter: [Forms]![EmployeeSalesDialogBox]![EndingDate]
    Data Type: Date/Time
  7. Close the Parameters dialog box.
  8. Save the query as EmployeeSales and then close the query.

Create a Form That Is Named EmployeeSalesDialogBox

  1. In the Database window, click Forms, and then click New.
  2. In the New Form dialog box, click
    Design View, and then click OK.
  3. Add two unbound text box controls with the following properties:

    Text Box 1: ControlName: BeginningDate
    Text Box 2: ControlName: EndingDate
  4. Add a command button to the form with the following properties. If the Command Button Wizard starts, click Cancel.

    Name: Command4
    Caption: "Employee Sales Crosstab"
  5. 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 = "EmployeeSales"


    '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

  6. Close the Microsoft Visual Basic Editor.
  7. Save the form as
    EmployeeSalesDialogBox and then close the form.

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.
  1. In the Database window, click Reports, and then click New.
  2. 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.
  3. To add a report footer section, click Report Header/Footer on the View menu.
  4. 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 "Head1" and the
    Name property of the next text box to "Head2". Continue until you complete "Head11".
  5. In the "Detail" 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 "Col1" and the Name property of the next text box to "Col2". Continue until you complete "Col11". Set the
    Format property of the text boxes "Col2" through "Col11" to Standard.
  6. In the report footer, create 11 unbounded text box controls without labels. Set the Name property of the left text box to "Tot1". In the Control Source property of Tot1, type
    ="Totals:". Set the Name property of the remaining text boxes that display the column totals to "Tot2" through "Tot11". Set the Format property of text boxes "Tot2" through "Tot11" to Standard.
  7. 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 "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 = 2 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 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)) = "Totals"

    ' 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 frm = Forms!EmployeeSalesDialogBox
    ' Open QueryDef object.
    Set qdf = dbsReport.QueryDefs("EmployeeSales")
    ' Set parameters for query based on values entered
    ' in EmployeeSalesDialogBox form.
    qdf.Parameters("Forms!EmployeeSalesDialogBox!BeginningDate") _
    = frm!BeginningDate
    qdf.Parameters("Forms!EmployeeSalesDialogBox!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 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
  8. The following event procedures are set for the report.

    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]
  9. Save the Report as EmployeeSales. When you are prompted to enter the parameter values, click
    Cancel and then close the report.
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.




References

For additional information about downloading a working copy of a sample database, click the following article number to view the article in the Microsoft Knowledge Base:
248674 ACC2000: Orders and Developer Solutions Sample Databases Available on the Microsoft Developer Network (MSDN)

Follow these steps to find the sample:
  1. Open the sample database Solutions9.mdb.
  2. In the Select a Category of Examples list, click
    Create advanced reports.
  3. In the Select an Example list, click Create a crosstab report with dynamic column headings, and then click
    OK.
Proprietăți

ID articol: 328320 - Ultima examinare: 3 aug. 2004 - Revizie: 1

Feedback