Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Dec 12, 2002

Access 2000 How To's: Controlling Report Data

By David Nishimoto


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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM