Launching a Report in Access 2000 (Article)

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

David Nishimoto

Version Compatibility: Visual Basic 6

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

<pre>
David Nishimoto
davepamn@relia.net
Access 2000: Launching a report
</pre>

It is often the case 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 with in a form and passed to the report as query criteria. This is the simplest method to control report output resulting from user selected data criteria.

<table border=1>
<tr>
<td colspan=3>
Table Transactions
</td>
</tr>
<tr>
<td colspan=3>
Fields and Types
</td>
</tr>
<tr>
<td></td><td>MemberId</td><td>Number</td>
</tr>
<tr>
<td></td><td>PostedDate</td><td>PostedDater</td>
</tr>
<tr>
<td></td><td>Amount</td><td>Double</td>
</tr>
<tr>
<td></td><td>Code</td><td>Number</td>
</tr>
</table>
<pre>
		
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 your 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
</pre>