Building the Class Module for the Objects

Next, turn your attention to writing the code that will implement the desired features of your Customer Service application. VBA code can be written in various places, such as class modules that are independent or associated with a form, as well as in standard modules. Just as in the prior case study, you will create the custom class module first, then the standard modules, and finally the code for the forms to call the other modules. An example of how the Project Explorer will look in the Visual Basic Editor when you're finished is shown in Figure 13.25.

Project - CustomerServrce

Lr 1

i_J

- iCustonerService [CustomerService)!

Microsoft Office Access Class Objects

1=11 Forrri_frnnCustoriners

1=11 Forrri_fmnSearch

ÇK®

Modules

A

nnodEusinessLogic

A

nnodDatabaseLoaic

ËFfil

Class Modules

dsCustomer

Figure 13.25

Figure 13.25

In this Customer Service application, you create one custom class module that will store the values for the current Customer.

An object diagram for the Customer class is shown in Figure 13.26.

Customer

-CustomerId

-LastName

-FirstName

-MiddleName

-Company

-Address1

-Address2

-City

-Region

-PostalCode

-WorkPhone

-HomePhone

-CellPhone

-Email

-PlanId

+RetrieveCustomers() +PopulatePropertiesFromRecordset() +PopulatePropertiesFromForm() +ClearObject()_

Figure 13.26

The properties correspond to the data elements on the View/Manage Customer Accounts form for the most part, except that the values for the Plan History records are not shown here. The methods represent various actions that should be taken on the object. You will also write numerous other procedures that are not in the class module, as you will see later.

Try It Out Building the clsCustomer Class

Let's get started and build the clsCustomer class module that will implement the object illustrated in Figure 13.26.

1. Add a new class module called clsCustomer. In the General Declarations section of the class, add the following code:

Option Compare Database Option Explicit

Const CLS_CUSTOMER = "clsCustomer"

Dim intCustomerIdVal As Integer Dim strLastNameVal As String Dim strFirstNameVal As String Dim strMiddleNameVal As String Dim strCompanyVal As String Dim strAddress1Val As String Dim strAddress2Val As String Dim strCityVal As String Dim strRegionVal As String Dim strPostalCodeVal As String

Dim strWorkPhoneVal As String Dim strHomePhoneVal As String Dim strCellPhoneVal As String Dim strEmailVal As String Dim intPlanIdVal As Integer

2. Add the various property procedures shown here to clsCustomer class module.

Public Property Get CustomerId() As Integer On Error Resume Next CustomerId = intCustomerIdVal End Property

Public Property Let CustomerId(ByVal Value As Integer) On Error Resume Next intCustomerIdVal = Value End Property

Public Property Get LastName() As String On Error Resume Next LastName = strLastNameVal End Property

Public Property Let LastName(ByVal Value As String) On Error Resume Next strLastNameVal = Value End Property

Public Property Get FirstName() As String On Error Resume Next FirstName = strFirstNameVal End Property

Public Property Let FirstName(ByVal Value As String) On Error Resume Next strFirstNameVal = Value End Property

Public Property Get MiddleName() As String On Error Resume Next MiddleName = strMiddleNameVal End Property

Public Property Let MiddleName(ByVal Value As String) On Error Resume Next strMiddleNameVal = Value End Property

Public Property Get Company() As String On Error Resume Next Company = strCompanyVal End Property

Public Property Let Company(ByVal Value As String) On Error Resume Next strCompanyVal = Value End Property

Public Property Get Address1() As String On Error Resume Next Address1 = strAddress1Val End Property

Public Property Let Address1(ByVal Value As String) On Error Resume Next strAddress1Val = Value End Property

Public Property Get Address2() As String On Error Resume Next Address2 = strAddress2Val End Property

Public Property Let Address2(ByVal Value As String) On Error Resume Next strAddress2Val = Value End Property

Public Property Get City() As String On Error Resume Next City = strCityVal End Property

Public Property Let City(ByVal Value As String) On Error Resume Next strCityVal = Value End Property

Public Property Get Region() As String On Error Resume Next Region = strRegionVal End Property

Public Property Let Region(ByVal Value As String) On Error Resume Next strRegionVal = Value End Property

Public Property Get PostalCode() As String On Error Resume Next PostalCode = strPostalCodeVal End Property

Public Property Let PostalCode(ByVal Value As String) On Error Resume Next strPostalCodeVal = Value End Property

Public Property Get WorkPhone() As String

On Error Resume Next

WorkPhone = strWorkPhoneVal

End Property

Public Property Let WorkPhone(ByVal Value

As String)

On Error Resume Next

strWorkPhoneVal = Value

End Property

Public Property Get HomePhone() As String

On Error Resume Next

HomePhone = strHomePhoneVal

End Property

Public Property Let HomePhone(ByVal Value

As String)

On Error Resume Next

strHomePhoneVal = Value

End Property

Public Property Get CellPhone() As String

On Error Resume Next

CellPhone = strCellPhoneVal

End Property

Public Property Let CellPhone(ByVal Value

As String)

On Error Resume Next

strCellPhoneVal = Value

End Property

Public Property Get Email() As String

On Error Resume Next

Email = strEmailVal

End Property

Public Property Let Email(ByVal Value As String)

On Error Resume Next

strEmailVal = Value

End Property

Public Property Get PlanId() As Integer

On Error Resume Next

PlanId = intPlanIdVal

End Property

Public Property Let PlanId(ByVal Value As

Integer)

On Error Resume Next

intPlanIdVal = Value

End Property

3. Add the RetrieveCustomers function shown here to the clsCustomer class module. Function RetrieveCustomers() As ADODB.Recordset On Error GoTo HandleError Dim rsCust As New ADODB.Recordset

If intCustomerLookupId > 0 Then

'if form is being opened for selected customer from frmSearch Set rsCust = ExecuteSPRetrieveRS("spRetrieveSelectedCustomer", _ intCustomerLookupId)

Else

'if form is being opened for all customer records Set rsCust = ExecuteSPRetrieveRS("spRetrieveAllCustomers") End If

'return the populated recordset Set RetrieveCustomers = rsCust

Exit Function

HandleError:

GeneralErrorHandler Err.Number, Err.Description, CLS_CUSTOMER, _

"RetrieveCustomers" Exit Function

End Function

4. Add the PopulatePropertiesFromRecordset procedure shown here to the clsProjects class module.

Sub

PopulatePropertiesFromRecordset(rsCust As ADODB.

Recordset)

On

Error GoTo HandleError

'Populate the object with the current record in

the

'recordset

Me.

CustomerId = rsCust!CustomerId

Me.

.LastName = FixNull(rsCust!LastName)

Me.

.FirstName = FixNull(rsCust!FirstName)

Me.

MiddleName = FixNull(rsCust!MiddleName)

Me.

.Company = FixNull(rsCust!Company)

Me.

.Address1 = FixNull(rsCust!Address1)

Me.

.Address2 = FixNull(rsCust!Address2)

Me.

City = FixNull(rsCust!City)

Me.

.Region = FixNull(rsCust!Region)

Me.

.PostalCode = FixNull(rsCust!PostalCode)

Me.

.WorkPhone = FixNull(rsCust!WorkPhone)

Me.

HomePhone = FixNull(rsCust!HomePhone)

Me.

.CellPhone = FixNull(rsCust!CellPhone)

Me.

.Email = FixNull(rsCust!Email)

Me.

.PlanId = rsCust!currentplanid

Exit Sub

HandleError:

GeneralErrorHandler Err.Number, Err.Description,

CLS_CUSTOMER, _

"PopulatePropertiesFromRecordset"

Exit Sub

End

Sub

5. Add the PopulatePropertiesFromForm procedure shown here to the clsCustomer class module.

Sub PopulatePropertiesFromForm() On Error GoTo HandleError

'Populate the object with the current record in the 'form

'if the customernum field is not empty (e.g. updating record) If Forms("frmCustomers")!txtCustomerNum <> "" Then

Me.CustomerId = Forms("frmCustomers")!txtCustomerNum

Else

'adding new record so id not assigned yet Me.CustomerId = 0 End If

Me.LastName = Forms("frmCustomers")!txtLName Me.FirstName = Forms("frmCustomers")!txtFName Me.MiddleName = Forms("frmCustomers")!txtMName Me.Company = Forms("frmCustomers")!txtCompany Me.Address1 = Forms("frmCustomers")!txtAddress1 Me.Address2 = Forms("frmCustomers")!txtAddress2 Me.City = Forms("frmCustomers")!txtCity Me.Region = Forms("frmCustomers")!txtRegion Me.PostalCode = Forms("frmCustomers")!txtPostalCode Me.WorkPhone = Forms("frmCustomers")!txtWorkPhone Me.HomePhone = Forms("frmCustomers")!txtHomePhone Me.CellPhone = Forms("frmCustomers")!txtCellPhone Me.Email = Forms("frmCustomers")!txtEmail Me.PlanId = Forms("frmCustomers")!cboPlan

Exit Sub

HandleError:

GeneralErrorHandler Err.Number, Err.Description, CLS_CUSTOMER, _

"PopulatePropertiesFromForm" Exit Sub

End Sub

6. Add the ClearObject procedure shown here to the clsCustomer class module.

Sub ClearObject()

On Error GoTo HandleError

'clear the values in the customer object

Me.CustomerId = 0

Me.FirstName = ""

Me.MiddleName = ""

Me.

.Address1 = ""

Me.

.Address2 = ""

Me.

City = ""

Me.

Region = ""

Me.

PostalCode = ""

Me.

.WorkPhone = ""

Me.

HomePhone = ""

Me.

CellPhone = ""

Me.

Email = ""

Me.

.PlanId = 0

Exit Sub

HandleError:

GeneralErrorHandler Err.Number, Err.Description, CLS_CUSTOMER, _

"ClearObject"

Exit Sub

End Sub

7. Add the Save procedure shown here to the clsCustomer class module.

Sub Save(blnAddMode As Boolean, rsCust As ADODB.Recordset)

On Error GoTo HandleError

Dim strSPname As String

'if adding a new record If blnAddMode = True Then strSPname = "spInsertCustomer"

Else

'if updating a record strSPname = "spUpdateCustomer" End If

'perform the insert or update

Call ProcessUpdate(strSPname, Me, rsCust)

Exit Sub

HandleError:

GeneralErrorHandler Err.Number, Err.Description, CLS_CUSTOMER, "Save" Exit Sub

End Sub

8. Make sure to keep saving your changes periodically so they are not lost.

Was this article helpful?

0 0

Post a comment