Connecting the User Interface to the Code

You are now ready to tie everything you have done so far together by adding the VBA code to the Customer Search and View/Manage Customer Accounts forms. Most of this code will be event procedures that fire when different buttons are clicked. Some of the code will also be local procedures that deal with user-interface-specific features. It just did not make sense to put these in a standard or class module.

The Customer Search Form

An example of the Customer Search form, called frmSearch, is shown in Figure 14-27 with some sample data populated. Keep it in mind as you write the code for the form.

Figure 14-27

Try It Out-Writing Code for the frmSearch

As previously mentioned, you are now ready to write the VBA code that will finish up the application. Start with the frmSearch form, and finish with the frmCustomers form.

Open the frmSearch form and select the Form_Load event for the form to bring up the Visual Basic Editor. Add the following code to the form:


Private Sub Form_Load()

    On Error GoTo HandleError
        'create new recordset
    Set rsSearch = New ADODB.Recordset
        Exit Sub


HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "Form_Load"
    Exit Sub

End Sub

Add the following code to the General Declarations section of the form:


Option Compare Database
Option Explicit
Const SEARCH_FORM = "frmSearch"
Dim rsSearch As ADODB.Recordset

Add the following Click event procedures for running the search:


Private Sub cmdSearch_Click()

    On Error GoTo HandleError
        'run the search
    Call RunSearch
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, _
            "cmdSearch_Click"
    Exit Sub

End Sub

Sub RunSearch()

    On Error GoTo HandleError
        Dim strSQL As String
        'get the SQL statement for the search
    strSQL = GetSQL
        'if the SQL statement was generated successfully
    If strSQL <> "ERROR" Then
        'execute the SQL statement against the
        'database and put results in recordset
        Set rsSearch = ProcessRecordset(strSQL)
            'load the search results into the list on the form
        PopulateListFromRecordset lstResults, rsSearch, 11
        End If
        Exit Sub


HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "RunSearch"
    Exit Sub

End Sub

Function GetSQL() As String

On Error GoTo HandleError

    Dim strSQL As String
    Dim strSQLWhereClause As String
    Dim blnPriorWhere As Boolean
        blnPriorWhere = False
        'generate the first part of the SQL Statement
    strSQL = BuildSQLSelectFrom()
        'build the where criteria based on the criteria filled in
    'by the user in one or more of the search fields on the form
    If txtCustomerNum <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtCustomerNum, "CustomerID")
    End If
    If txtPhone <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtPhone, "Phone")
    End If
    If txtLName <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtLName, "LastName")
    End If
    If txtFName <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtFName, "FirstName")
    End If
    If txtCompany <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtCompany, "Company")
    End If
    If txtAddress <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtAddress, "Address1")
    End If

    If txtCity <> "" Then


        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtCity, "City")
    End If
    If txtRegion <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtRegion, "Region")
    End If
    If txtPostalCode <> "" Then
        strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _
                            txtPostalCode, "PostalCode")
    End If
    If blnPriorWhere Then
        'build the final SQL statement with the Select…From…and
         'Where clause
        strSQL = strSQL & strSQLWhereClause
    Else
        MsgBox "You must enter at least one search criteria!"
        strSQL = "ERROR"
    End If
        'return the SQL statement
    GetSQL = strSQL

    Exit Function

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "GetSQL"
    Exit Function

End Function

Add the following procedures for clearing the search results:


Private Sub cmdClear_Click()

    On Error GoTo HandleError
        'clear the search fields
    Call ClearFields
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, _
           "cmdClear_Click"
    Exit Sub

End Sub 


Sub ClearFields()

    On Error GoTo HandleError
        'clear fields
    txtCustomerNum = ""
    txtLName = ""
    txtF Name = ""
    txtCompany = ""
    txtAddress = ""
    txtCity = ""
    txtRegion = ""
    txtPostalCode = ""
    txtPhone = ""

    Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "ClearFields"
    Exit Sub

End Sub

Private Sub Form_Unload(Cancel As Integer)

    On Error GoTo HandleError
        'reset lookup id to 0
    intCustomerLookupId = 0
        If Not rsSearch Is Nothing Then
        'close recordset
        rsSearch.Close
        Set rsSearch = Nothing
    End If
            Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "Form_Unload"
    Exit Sub
    End Sub

Private Sub 1stResults_DblClick(Cancel As Integer)

On Error GoTo HandleError

   'store the value of the selected customer id to be
   'used later by frmCustomers to open selected record
   intCustomerLookupId = lstResults.Column(0)
      'open frmCustomers
   DoCmd.OpenForm "frmCustomers"

    Exit Sub


HandleError:
    GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, _
           "lstResults_DblClick"
    Exit Sub

End Sub

The View/Manage Customer Accounts Form

An example of the View/Manage Customer Accounts form, called frmCustomers, is shown in Figure 14-28 with some sample data populated. Keep this form in mind as you write the code.

Figure 14-28

Try It Out-Writing Code for the frmCustomers

Now that you have finished with the VBA code for the frmSearch form, you can turn to the frmCustomers form.

Open the frmCustomers form and select the Form_Load event for the form to bring up the Visual Basic Editor. Add the following code to the form:


Private Sub Form_Load()

    On Error GoTo HandleError
        Set objCustomer = New clsCustomer
    Set rsCustomer = New ADODB.Recordset
        'lock the customer number field so user cannot modify it
    '(since assigned by the database)
    txtCustomerNum.Locked = True
        'set addmode to false since not adding yet
    blnAddMode = False
        'load the records from the database
    Call LoadRecords
        'populate plans combo box
    Call PopulatePlans
    Exit Sub


HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
            "Form_Load"
    Exit Sub

End Sub

Add the following code to the General Declarations section of the form:


Option Compare Database
Option Explicit
Const CUSTOMERS_FORM = "frmCustomers"
Dim objCustomer As clsCustomer
Dim rsCustomer As ADODB.Recordset
Dim rsHistory As ADODB.Recordset
Dim blnAddMode As Boolean

Add the following code behind the form for adding a new customer record:


Private Sub cmdAddNew_Click()

    On Error GoTo HandleError
        'clear the current controls to enable adding a new
    'customer record
    Call AddEmptyCustomerRecord
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "cmdAddNew_Click"
    Exit Sub

End Sub

Sub AddEmptyCustomerRecord()

    On Error GoTo HandleError
        'set add mode to true
    blnAddMode = True
        'clear the current values in the contacts object
    objCustomer.ClearObject
        'clear the current controls on the form so the
    'user can fill in values for the new record
    Call ClearCustomerControls
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "AddEmptyCustomerRecord"
    Exit Sub

End Sub

Sub ClearCustomerControls()

    On Error GoTo HandleError
        'clear the values in the controls on the form
    Me.txtCustomerNum = ""
    Me.txtLName = ""
    Me.txtFName = ""
    Me.txtMName = ""
    Me.txtCompany = ""
    Me.txtAddress1 = ""
    Me.txtAddress2 = ""
    Me.txtCity = ""
    Me.txtRegion = ""
    Me.txtPostalCode = ""
    Me.txtWorkPhone = ""
    Me.txtHomePhone = ""
    Me.txtCellPhone = ""
    Me.txtEmail = ""
    Me.cboPlan = ""
        'clear the plan history list box
    lstPlanH istory.RowSource = ""
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "ClearCustomerControls"
    Exit Sub

End Sub

Add the following procedures to the form for making updates to the data:


Private Sub cmdSave_Click()

    On Error GoTo HandleError
        Dim intCurCustomer As Integer
        'save the id of the current record if in update mode
    If Not blnAddMode Then
        intCurCustomer = objCustomer.CustomerId
    Else
        intCurCustomer = 0
    End If
            'populate object with current info on form
        objCustomer.PopulatePropertiesFromForm
            'save all changes to current record
        objCustomer.Save blnAddMode, rsCustomer
                'move back to the customer that was current before the requery
        If intCurCustomer > 0 Then
            'move back to the contact that was just updated
            rsCustomer.Find "[CustomerId] = " & intCurCustomer
            'refresh the history list box for the record
            Call RefreshHistory
        Else
            'if just added new record, move to the beginning of
            'the recordset
            Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)
            'populate the controls on the form with the current record
            Call PopulateCustomerControls
        End If
            Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "cmdSave_Click"
    Exit Sub

End Sub

Add the following code to the form for navigating through the customer records:


Private Sub cmdMoveFirst_Click()

    On Error GoTo HandleError
         'move to the first record in the local disconnected recordset
    Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)
         'populate the controls on the form with the current record
    Call PopulateCustomerControls
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "cmdMoveFirst_Click"
    Exit Sub

End Sub

Private Sub cmdMoveLast_Click()

    On Error GoTo HandleError
        'move to the last record in the local disconnected recordset
    Call MoveToLastRecord(rsCustomer, objCustomer, blnAddMode)
        'populate the controls on the form with the current record
    Call PopulateCustomerControls
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "cmdMoveLast_Click"
    Exit Sub

End Sub

Private Sub cmdMoveNext_Click()

    On Error GoTo HandleError
        'move to the next record in the local disconnected recordset
    Call MoveToNextRecord(rsCustomer, objCustomer, blnAddMode)
        'populate the controls on the form with the current record
    Call PopulateCustomerControls
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "cmdMoveNext_Click"
    Exit Sub

End Sub

Private Sub cmdMovePrevious_Click()
        On Error GoTo HandleError
        'move to the previous record in the local disconnected recordset
    Call MoveToPreviousRecord(rsCustomer, objCustomer, blnAddMode)
        'populate the controls on the form with the current record
    Call PopulateCustomerControls
        Exit Sub

HandleError:

    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "cmdMovePrevious_Click"
    Exit Sub

End Sub

Add the following code to the form for populating the fields on the form with the current customer record:


Sub PopulatePlans()
 
On Error GoTo HandleError

    'populate the Plans combo box with values from the database
    Dim rsPlans As New ADODB.Recordset
        'populate the list of plans from the database
    Set rsPlans = ExecuteSPRetrieveRS("spRetrievePlans", 0)

    cboPlan.RowSource = ""
    cboPlan.LimitToList = True
    cboPlan.ColumnCount = 2
    cboPlan.RowSourceType = "Value List"
    cboPlan.BoundColumn = 0
        Do While Not rsPlans.EOF
        'populate the plans combo box
        cboPlan.AddItem rsPlans!PlanId & ";" & rsPlans!PlanName
        rsPlans.MoveNext
    Loop
    Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "PopulatePlans"
    Exit Sub

End Sub


Sub LoadRecords()

    On Error GoTo HandleError
         'load the customer recordset
    Set rsCustomer = objCustomer.RetrieveCustomers
        'if the recordset is empty
    If rsCustomer.BOF And rsCustomer.EOF Then
        Exit Sub
    Else
        'populate the object with values in the recordset
        objCustomer.PopulatePropertiesFromRecordset rsCustomer

        Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)
 
        'populate the controls on the form with the current record
        Call PopulateCustomerControls
        End If
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, "LoadRecords"
    Exit Sub

End Sub

Sub PopulateCustomerControls()
        On Error GoTo HandleError
        'Populate the controls on the Customers form with the values of the
        'current record in the contacts object.
        If Not rsCustomer.BOF And Not rsCustomer.EOF Then
            Me.txtCustomerNum = objCustomer.CustomerId
            Me.txtLName = objCustomer.LastName
            Me.txtFName = objCustomer.FirstName
            Me.txtMName = objCustomer.MiddleName
            Me.txtCompany = objCustomer.Company
            Me.txtAddress1 = objCustomer.Address1
            Me.txtAddress2 = objCustomer.Address2
            Me.txtCity = objCustomer.City
            Me.txtRegion = objCustomer.Region
            Me.txtPostalCode = objCustomer.PostalCode
            Me.txtWorkPhone = objCustomer.WorkPhone
            Me.txtHomePhone = objCustomer.HomePhone
            Me.txtCellPhone = objCustomer.CellPhone
            Me.txtEmail = objCustomer.Email
            Me.cboPlan = objCustomer.PlanId
            'refresh the history list box
            Call RefreshHistory

        ElseIf rsCustomer.BOF Then
            'past beginning of recordset so move to first record
            Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)
        ElseIf rsCustomer.EOF Then
            'past end of recordset so move back to last record
            Call MoveToLastRecord(rsCustomer, objCustomer, blnAddMode)
        End If
        Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "PopulateCustomerControls"
    Exit Sub

End Sub

Add the following RefreshHistory procedure:


Sub RefreshHistory()

    On Error GoTo HandleError

    'populate the recordset for plan history list
    Set rsHistory = New ADODB.Recordset
    'get plan history for current customer
    Set rsHistory = ExecuteSPRetrieveRS("spRetrieveCustomerHistory", _
                    objCustomer.CustomerId)

    'Set rsHistory = objCustomer.RetrieveHistory(objCustomer.CustomerId)
    PopulateListFromRecordset Me.lstPlanHistory, rsHistory, 5
    rsHistory.Close
                Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _
           "RefreshHistory"
    Exit Sub

End Sub

Add the following code to the Form_Unload event procedure:


Private Sub Form_Unload(Cancel As Integer)

    On Error GoTo HandleError
        'reset lookup id to 0
    intCustomerLookupId = 0
        If Not rsCustomer Is Nothing Then
        'close recordset


        rsCustomer.Close
        Set rsCustomer = Nothing
    End If
            Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, "Form_Unload"
    Exit Sub
    End Sub

That is all the code for the application. So, it’s time now to resolve any typographical errors if you have not done so already.