HandsOn Writing the Report Open Event Procedure

1. Open the Acc2003_Chap25.mdb file from the book's downloadable files or, if you'd like to start from scratch, create a new Microsoft Office Access database. This database should contain tables, queries, forms, and reports from the sample Northwind database. You can import these objects by choosing File | Get External Data | Import, or you can create a copy of the Northwind database to be used for this chapter's hands-on exercises.

2. Create a tabular report based on the Northwind database's Customers table. Save this report as rptCustomers.

3. Choose View | Design View to switch to the Design view of the rptCustomers report. In the Report Header area, click the report title (label control) to select it.

4. If the Report properties sheet does not appear on the screen, click the Properties button on the toolbar. Click the All tab and type lblCustomers as the Name property and enter Customers as the Caption property for the selected label control.

5. In the Report properties sheet, click the Event tab. Click next to the On Open event property and choose [Event Procedure] from the drop-down box. Click the Build button (...).

Part IV

6. Access opens the Visual Basic Editor window and writes the stub of the Report_Open event procedure. Complete the code of the Report_Open event procedure as shown below.

Private Sub Report_Open(Cancel As Integer) Dim strCustName As String Dim strSQL As String Dim strWHERE As String

On Error GoTo ErrHandler strSQL = "SELECT * from Customers"

strCustName = InputBox("Type the first letter of " & _

" the Company Name or type an asterisk (*) to view" & _ " all companies.", "Show All /Or Filter")

If strCustName = "" Then

Cancel = True ElseIf strCustName = "*" Then Me.RecordSource = strSQL Me.lblCustomers.Caption = "All Customers"

Else strCustName = & Trim(strCustName) & "*" &

strWHERE = " WHERE CompanyName Like " _

& strCustName & "" Debug.Print strSQL Debug.Print strWHERE Me.RecordSource = strSQL & strWHERE Me.lblCustomers.Caption = "Selected Customers" & _ " (" & UCase(strCustName) & ")"

End If Exit Sub ErrHandler:

MsgBox Err.Description

End Sub

7. Switch to the rptCustomers report's Design view and choose File | Print Preview. A message box will appear where you can enter an asterisk (*) to view all customers or the first letter of a company name if you'd like to limit your records. To cancel the report, click Cancel or press the Esc key.

0 0

Post a comment