This article was previously published under Q328320
On This Page
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.
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 Queries
under 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.
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 Queries
under 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.
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.
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.
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 (http://support.microsoft.com/kb/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.
Need More Help? Contact a Support professional by Email, Online or Phone.
Customer Service For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
Newsgroups Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
Back to the top
