Custom Project Sinking Events in a Standalone Class Module

Online Data Entry Jobs

Online Data Entry Jobs

Get Instant Access

Part 1: File Preparation

1. Open the Acc2003_Chap27.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 the Customers, Products, Suppliers, and Categories tables from the sample Northwind database. You can import these objects by choosing File | Get External Data | Import.

Part IV

2. In the Database window, click the Forms object button and double-click Create form by using wizard. Use the Form Wizard dialogs to create a new form based on the Customers table. Select all the fields from the Customers table and specify Columnar layout, Blueprint style, and frmCustomers as the form's title. After you click Finish, the newly designed frmCustomers form will appear in the Form view as shown in Figure 27-1.

Figure 27-1: The frmCustomers form is used in Custom Project 27-1 to demonstrate how an object's event can be handled outside of the form class module.

frmCustomers

IIP!®

Customer ID 15135

Company Name

1 Alfreds Futterkiste

j

Contact Name

1 Maria Anders

Contact Title

(Sales Representative

Address

|0bere Str. 57

I

rifw

¡Berlin

.«y

Region

1 1

Postal Code

[13308

Country

y er many

Phorie

|û2D-00Tfl3î1

Fax

[030-007G5J5

1 Record: [MJ < |

nutria °f "

3. Close the frmCustomers form created in step 2 above.

Part 2: Creating the cRecordLogger Class

1. In the Database window, press Alt+F11 to activate the Visual Basic Editor window.

2. Insert a new class module by choosing Insert | Class Module. A new class called Class1 will appear in the Project Explorer window. Use the Properties window to change the name of the class to cRecordLogger (Figure 27-2).

Figure 27-2: Use the Name property in the Properties window to change the name of the class module.

fcfttsoft visual Basic Acc2003_Chap27 [cRccordLoggpi (Codo)]

fcfttsoft visual Basic Acc2003_Chap27 [cRccordLoggpi (Codo)]

1 0b Ecfc Iflew insert Cetuo Bun lods fidd-îns Sïndow t£eip j Type a qjestlco fm hafci - . fi1 x

ISu-_H 1 - U i M JfTi" . » J

1

PniH-u - A. 1 2003 rhdii?7 Q

|(Genef*l) |(Br:clw«tinn*) j*]

af0 B

Option Compare Dacaäase A

- ^ Acc2003_Chap27 (Acc2Q03_Chap27) a dass Kodutei

3J CP.eL-rd.üijjer 1- 14 dtw/rriiio (ACWZMAIN)

Properties - cRetardLogger

cRecordLoqqer dassModiie Afrhatwtic 1 categcrizid 1

ûfflBaaû-Lcjœi

[nstaidrq l - Private

éjtlu ±r\

Figure 27-2: Use the Name property in the Properties window to change the name of the class module.

Event Programming in Forms and Reports

3. In the cRecordLogger class module's Code window, enter the following module-level variable declaration, just below the Option Compare Database statement:

Private WithEvents m_frm As Access.Form

Now that you've declared the object variable WithEvents, the variable name m_frm appears in the Object box in your class module (Figure 27-3). When you select this variable from the drop-down list, the valid events for that object will appear in the Procedure box (Figure 27-4). By choosing an event from the Procedure drop-down list, an empty procedure stub will be added to the class module where you can write your code for handling the selected event. By default, Access adds the Load event procedure stub after selecting an object from the Object drop-down list.

Figure 27-4: The Procedure drop-down list in the cRecordLogger's Code window lists the valid events for the object declared with the WithEvents keyword.

Figure 27-3: The Object drop-down list in the cRecordLogger's Code window lists the m_frm object variable that was declared using the WithEvents keyword.

Figure 27-4: The Procedure drop-down list in the cRecordLogger's Code window lists the valid events for the object declared with the WithEvents keyword.

4. In the cRecordLogger class module's Code window, enter the following Property procedure just below the variable declaration:

Public Property Set Form(cur_frm As Form) Set m_frm = cur_frm m_frm.AfterUpdate = "[Event Procedure]" End Property

In order to sink events in the class module, you must tell the class which specific form's events the class should be responding to. You do this by

Part IV

writing the Property Set procedure. Recall from Chapter 8 that Property Set procedures are used to assign a reference to an object. Therefore, the statement

Set m_frm = cur_frm will assign the current form (passed in the cur_frm variable) to the m_frm object variable declared in step 3 above.

Pointing the object variable (m_frm) at the object (cur_frm) isn't enough. Access will not raise the event unless the object's Event property is set to "[Event Procedure]". Therefore, the second statement in the procedure above m_frm.AfterUpdate = "[Event Procedure]"

will ensure that Access knows that it must raise the form's AfterUpdate event.

5. Choose Tools | References and add the Microsoft Scripting Runtime Library to the class module. You will need this library to gain access to the File System in the next step.

6. In the cRecordLogger class module's Code window, enter the following event procedure:

Private Sub m_frm_AfterUpdate() Dim fso As FileSystemObject Dim myFile As Object Dim strFileN As String On Error Resume Next

Set fso = New FileSystemObject strFileN = "C:\MyCust.txt"

Set myFile = fso.GetFile(strFileN)

If Err.number = 0 Then ' open text file

Set myFile = fso.OpenTextFile(strFileN, 8)

Else

' create a text file

Set myFile = fso.CreateTextFile(strFileN) End If myFile.WriteLine UCase(m_frm.Controls(0)) & _ " Created on: " & Date & " " & Time & _ " (Form: " & m_frm.Name & ")"

myFile.Close Set fso = Nothing

MsgBox "See the audit trail in " & strFileN & "." End Sub

The code inside the m_frm_AfterUpdate event procedure will be executed after Access finds that the form's AfterUpdate property is set to "[Event Procedure]". This code tells Access to open or create a text file named

Event Programming in Forms and Reports

C:\MyCust.txt and write a line consisting of the value of the first control on the form (m_frm.Controls(0)), the date and time the record was inserted or modified, and the name of the form.

Figure 27-5: A custom cRecordLogger class is used to handle a form's AfterUpdate event.

7. Save the code that you wrote in the class module by clicking the Save button on the toolbar or choosing File | Save. When the Save As dialog box appears with cRecordLogger in the text box, click OK.

For the events to actually fire now that you've written the code to handle the event in the standalone class module, you need to instantiate the class and pass it the object whose events you want to track. This requires that you write a couple of lines of code in your form's class module.

Part 3: Creating an Instance of the Custom Class in the Form's Class Module

1. In the Database window, select the frmCustomers form you created in step 2 of Part 1 and click the Design button.

2. While frmCustomers is in Design view, choose View | Code.

3. In the frmCustomers Code window, enter the following code: Private clsRecordLogger As cRecordLogger

Private Sub Form_Open(Cancel As Integer) Set clsRecordLogger = New cRecordLogger

Part IV

Set clsRecordLogger.Form = Me End Sub

Private Sub Form_Close()

Set clsRecordLogger = Nothing End Sub

To instantiate a custom class module, we begin by declaring a module-level object variable, clsRecordLogger, as the name of our custom class, cRecordLogger. You can choose any name you wish for your variable name.

Next, we instantiate the class in the Form_Open event procedure by using the following Set statement:

Set clsRecordLogger = New cRecordLogger

Notice that you must use the New keyword to create a new object of a particular class. By setting the reference to an actual instance of the object when the form first opens, we ensure that the object refers to an actual object by the time the event is first fired.

The second statement in the Form_Open event procedure

Set clsRecordLogger.Form = Me sets the Form property defined by the Property procedure in the class module (see step 4 of Part 2) to the Form object whose events we want to sink. The Me keyword represents the current instance of the Form class.

When you are done pointing the object variable to the instance of the custom class, it is a good idea to release the variable reference. We've done this by setting the object variable clsRecordLogger to Nothing in the Form_Close event procedure. The complete code entered in the frmCustomers form class module is shown in Figure 27-6.

i». Acc2Q03_ Chap27 - Form^frmCustomers (Coi|p)

BEB

| I Form ^J | Close

Option Compare Database Option Explicit

T

Private cIsRecordLoqqer As c Record Loqq er

Private Sub Form_Open[Cancel As Integer) Set clsRecordLogger = New cRecordLogger Set clsRecordLogger.Form — Me End Sub

Sei clsRecordLogger = Nothing End Sub

UJs^LJ JJTI

Figure 27-6: To sink form events in a custom class module, you must enter some code in the form class module.

Figure 27-6: To sink form events in a custom class module, you must enter some code in the form class module.

4. Save the code you entered in step 3 above by clicking the Save button on the toolbar.

5. Close the frmCustomers form.

Event Programming in Forms and Reports

Now that all the code has been written in the standalone class module and in the form class module, it's time to test our project.

Part 4: Testing the cRecordLogger Custom Class

1. Open the frmCustomers form in Form view.

2. Choose Records | Data Entry.

3. Enter MARSK in the Customer ID text box and Marski Enterprises in the Company Name text box (Figure 27-7). Press the record selector on the left side of the form to save the record.

Figure 27-7: The frmCustomers form in the Data Entry mode is used for testing out the custom cRecordLogger class.

frmCustomers

B0B

J

Customer ID

|marsk

Company Name

Im a iski Enterprises

Z!_

Contact Name

f ' 1

Contact Title

1

Address

1

City

I I

Region

! !

Postal Code

1 1

Country

! !

Phone

1 f

1 !

| Fteccrd: [mj |

i Lfcj>.i„H d 1

1

When you save the newly entered record, a message box appears with the text, "See the audit trail in C:\MyCust.txt." Recall that this message was programmed inside the m_frm_AfterUpdate() event procedure in the cRecordLogger class module. It looks like our custom class has successfully sunk the AfterUpdate event. The form's AfterUpdate event was propagated to the custom class module.

Click OK to close the message box.

Activate Windows Explorer and open the C:\MyCust.txt file.

The MyCust.txt file (Figure 27-8) displays the record log. You may want to revise the m_frm_AfterUpdate() event procedure so that you can track whether a record was created or modified.

C MyCust.txt - Notepad

BPS

1 File Edfc Format View help

----' 1

Figure 27-8: The MyCust.txt file is used by the cRecordLogger custom class for tracking record additions.

Figure 27-8: The MyCust.txt file is used by the cRecordLogger custom class for tracking record additions.

6. Close the C:\MyCust.txt file.

Part IV

7. Add few more records to the frmCustomers form and check out the MyCust.txt file.

8. Close the frmCustomers form.

Now that you know how to sink the form's AfterUpdate event outside the form class module, you can use the same idea to sink other form events in a class module and make your code easier to implement and maintenance free. Just remember that if you want to sink events in a standalone class module, you must write code in two places: in your class module and in your form or report class module. The class module must contain a module-level WithEvents variable declaration and you must set the reference to an actual instance of the object in the form or report module.

Part 5: Using the cRecordLogger Custom Class with Another Form

The code you've written so far in this project is ready for reuse in another Microsoft Access form. In the remaining steps, we will hook it up to the frmProducts form. Let's begin by creating this form.

1. In the Database window, choose the Forms object button and click the New button. In the New Form window, choose AutoForm: Columnar, choose Products from the drop-down list, and click OK to complete your selections.

2. When the Products form appears, click the Save button on the toolbar. In the Save As dialog box, type frmProducts for the form name and click OK.

3. Choose View | Design View to open frmProducts in Design view.

4. Choose View | Code to activate the frmProducts Code window.

5. Copy the code from the frmCustomers Code window to the frmProducts Code window. The code in the frmProducts Code window should match Figure 27-6 shown earlier.

6. In the frmProducts Code window, replace all the references to the object variable clsRecordLogger with clsRecordLogger2.

^^ Note: To quickly perform this operation, position the cursor inside the first clsRecordLogger variable name and choose Edit | Replace. The Find What text box should automatically display the name of the variable you want to replace. Type clsRecordLogger2 in the Replace With text box and click the Replace All button. Click OK to confirm the replacement of four variable names. Click Cancel to exit the Replace dialog box.

7. Save and close the frmProducts form.

8. Open the frmProducts form in Form view.

9. Choose Records | Data Entry.

10. Enter Delicious Raisins in the Product Name text box and press the record selector on the left side of the form to save the record.

Event Programming in Forms and Reports

At this point you should receive the custom message about the audit trail that you defined in the AfterUpdate event procedure within the custom cRecordLogger class module. This indicates that the AfterUpdate event that was raised by the form when you saved the newly entered record was successfully propagated to the custom class module.

11. Click OK to close the message box.

12. Close the frmProducts form.

13. Open the C:\MyCust.txt file to view the record log. You may want to choose a more generic name for your record log text file since it will be tracking various types of information.

Was this article helpful?

0 0

Post a comment