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.

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

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.