Access 2000 How To's: Controlling Report Data

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 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