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 14-25.

Figure 14-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 14-26.

Customer
- CustomerId - LastName -FirstName - MiddleName -Company - Address1 - Address2 - City - Region - PostalCode -WorkPhone -HomePhone - CellPhone -Email - PlanId
+Save () +RetrieveCustomers() +PopulatePropertiesFromRecordset() +PopulatePropertiesFromForm() +ClearObject()

Figure 14-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 14-26.

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

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

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

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

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

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.LastName = ""
    Me.FirstName = ""
    Me.MiddleName = ""
    Me.Company = ""


    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

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

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