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