Overview
It is often the case where a developer will need to create an Access 2000 reports application capable of data selection -- for example, all transactions for a member. The data is input as parameters within a form and then passed to the report as query criteria. This is the simplest method to control report output resulting from user-selected data criteria.
Table Transactions |
Fields and Types |
MemberId |
Number |
PostedDate |
PostedDater |
Amount |
Double |
Code |
Number |
Create a query called qryTransactions
Include memberid, posteddate, amount, and code in the query field list
Create a columar report called rptTransactions
Step 1: Use the report Wizard
Step 2: Select qryTransactions as the data source
Step 3: Select all fields
Step 4: Group on memberid
Step 5: Select Tabular type
Step 6: Name the report rptTransactions
Create a form called frmReportParameters
Step 1: Design View
Step 2: Add a text field called txtMemberId
Step 3: Change the label to read "Member Id"
Step 4: Add a button named "cmdLaunchTransactionReport" and labeled "Launch Report"
Step 5: Save the form as frmReportParameters
Launch the Code
Step 1: Make sure you're in form design
Step 2: Press the code button
Step 3: Add a Click event for the cmdLaunchTransactionReport button
The following code will limit data selection to records matching a specific member id. The report will open in preview mode. Only
transactions with a specific member id will be displayed.
Option Explicit
Option Compare Database
Private Sub cmdLaunchTransactionReport_Click()
On Error GoTo Err_cmdLaunchTransactionReport_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim sCriteria
sCriteria = "[MemberId]=" & txtMemberId
stDocName = "rptTransactions"
DoCmd.OpenReport stDocName, acViewPreview, , sCriteria, acWindowNormal
Exit_cmdLaunchTransactionReport_Click:
Exit Sub
Err_cmdLaunchTransactionReport_Click:
msgbox Err.Description
Resume Exit_cmdLaunchTransactionReport_Click
End Sub
Run the Form
Step 1: Press F5
Step 2: Enter in a Member Id
Step 3: Press the Launch Report Button
Step 4: Review your report
Back to Access 2000 How To's Series Home