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

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

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

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

Comment and Contribute


(Maximum characters: 1200). You have characters left.



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