Prices Start from

£25

1000 Words 24hrs Delivery!

Order Your Assignment

Delivered on-time or your money back

Request a call back

Start a live chat

Business Analytics Programming

Contents

Introduction to VBA application. 3

Impact on Business. 4

Higher efficiency. 4

Customer Satisfaction. 4

User manual 5

Entering a Complaint. 5

Editing Complaint. 9

Viewing report. 12

Technical Documentation. 15

Worksheets. 15

Forms. 15

Modules. 16

Appendix – VBA Code. 17

Workbook_Open. 17

Workbook_BeforeClose. 17

frmClose. 18

frmComplaint. 19

frmEdit. 31

mdlChart. 41

mdlMain. 48

mdlReports. 66

Introduction to VBA Application

The following section seeks to: outline and introduce the VBA application, evidence the reasons for its creation and state its core objectives.

Testy Taffy, the company has been in business for almost 100 years. The company was known as the Candy Company until 1990 when the company changed its name to Company to reflect its decision to discontinue all of its product lines other than taffy. Since 1990, Company has positioned itself as a leader in gourmet taffy. The company primarily does business in the US, although in recent years it has picked up some key international accounts. The company prides itself in using the finest ingredients and in processing the taffy in a manner that keeps the product fresh and soft. Company currently produces over 60 flavours and a variety of standard assortments.

For my project, I created a system that will help the company to manage customer complaints. The system uses Microsoft Excel as the user interface and Microsoft Access to store the data. When a complaint is received, the employee receiving the complaint will open the application by clicking on the file on his or her desktop. The employee will enter the required information, such as Customer Number, date of the complaint, Invoice Number, Item Number, complaint category etc along with the customer’s description of the complaint. The employee also has access to a chart summarizing historical complaints in case he or she needs to reference that information.

After the employee records the complaint, an email is sent to the appropriate individual(s) in the company. The email includes the complaint information along with a chart showing historical complaints.

When the individual receives the email, he or she will take the necessary action to resolve the complaint. Once the complaint is resolved, the employee will record the resolution in the customer complaint system. At the end of each week, the sales manager will use a report to review all complaints that have not been resolved within 5 days. He will also review complaints that have been resolved and change the status of the complaints from open to closed.

The customer complaint system will help the company to better understand complaint trends by providing a centralized location to store all complaint data. The system will further help the company by creating an instantaneous way for employees to be made aware of complaints that resulted from process failures in their respective departments. Additionally, the system will provide a framework for holding the respective managers accountable for resolving process failures that led to the complaint. Finally, the system provides a set of reports to enable employees to quickly view complaint data and to follow up on unresolved items.

Impact on Business

Firstly, this application is aimed to have a standard centralized mechanism to handle and resolve customer complaints. As the company would not gain much from using this application, and should perhaps consider using available sources with no additional capital or cost required hence to use Microsoft Excel and Microsoft Access for their greater requirements. However, this application has 2 major advantages:

Higher efficiency

Manually communicating with the customer can be a demanding job. With this application, viewing their complaints and working on them within just a few clicks.Sending them the acknowledgement of the complaint, updating post-resolution will help employees to work quickly.

Customer Satisfaction

Customer’s satisfaction gets improved by getting know that their complaints are getting noticed and also are getting resolved so that they can revisit which help the business growth.

User Manual

The sections that follow describe the functionality of the complaint system. The functionality of the system is broken into three sections: entering complaints, editing a complaint, and viewing reports.

Entering a Complaint

When the employee opens the program to record a complaint, the program generates a historical chart that users can view either when entering a complaint or when looking at the historical complaint report.

Once the program is open, the user can open the form to record the complaint by clicking either the record new complaint button on the ribbon or by clicking the top button displayed on the spreadsheet as illustrated below

Upon clicking the button, the program loads the list of customers into the complaint form, sets up the list of potential email recipients, generates worksheets that will temporarily store the complaint data, and retrieves a unique complaint number from the customer complaint database. 

Once the complaint form is open, the user can begin to enter the required information. Since likely, the user will not have the customer’s customer number memorized, the user can simply begin to type the customer’s name into the search field and the program will automatically complete the rest of the name as shown in the screenshot below

The user cannot record a complaint without entering at least one item. Additionally, the user cannot enter an item unless they have selected at least one complaint category. If the user attempts to add an item without selecting a complaint category, a message will display as shown in the screenshot below.

If the user attempts to record a complaint without adding at least one item, the message shown in the below screenshot will appear.

When the user clicks the add item button, the item data is recorded on a sheet in the workbook and the appropriate recipients are added to the email list based on the complaint category the user selected. An item appears in the list that contains the information the user entered. This is shown in the below screenshot.

If the user needs to remove an item that he or she has entered, the user does so by clicking on the remove item button. At any point, after the complaint form is open, the user can view a chart that details the company’s historical complaints. The user may reference this to understand how prevalent a certain type of complaint is. 

After the user has entered all necessary information, he or she clicks on the record button to process the complaint. When the user clicks the record button, the remaining data on the form is transferred to a worksheet. This data, along with the previously recorded item data, is then inserted into the database. The historical chart is updated, the program sends an email to the previously designated recipients, and the form is closed. An example of the email received by the recipients is shown below. This process is a key feature of the system. This email allows the user who enters the complaint to easily inform the necessary people in the company of the complaint. The email also makes the recipient accountable for investigating why the complaint occurred and making a resolution. Lastly, the email provides the recipient with a visual summary of how prevalent the particular type of complaint has been.

Editing Complaint

After the recipient resolves the complaint, he or she will access the system and update the complaint by clicking on the edit complaint button either on the ribbon or on the worksheet as illustrated below.

Once the edit form is open, the user has several options for finding the complaint as shown in the below screenshot.

If the user has the complaint number, he or she can simply enter that number into the Complaint No. field and press the find button adjacent to the text box. The user can follow a similar process to lookup up the complaint by the invoice number. If the user only remembers the customer’s name, he or she can enter the customer’s name and then cycle through the complaints listed for the customer until he or she finds the correct complaint. If the user reaches the last complaint about a given customer and then clicks the Next button again, the message below will display.

Once the user finds the complaint, he or she can record the resolution along with his or her initials. When the user clicks on the update button, the database will be updated.

Other users can also use the edit form to update complaint information as needed. Periodically, the sales manager will access the user form to review open complaints and to close them. In order to close a complaint, the sales manager must double click on the text box next to the status label. Upon double-clicking on the text box, a form will appear for the user to enter a password. If the user enters an incorrect password, the following message will be displayed.

The user will then be taken back to the edit form. This feature is essential to ensuring that only authorized employees can close out complaints. Additionally, this process allows management to review customer complaints. Once the user enters the correct password, the status of the complaint is changed to closed. The user must click update to save the change.

Viewing Report

Users can use the complaint system to view the following reports: past due complaints and historical complaints. To view the past due to complaints form, the user must click on the view past due complaints button on either the ribbon or the worksheet as shown below.

The user will then be taken to the following report.

The message box instructs the user to select a complaint in column A and then click on the edit complaint button to either view or edit the complaint. This report is important because it allows managers to see which complaints have not been resolved within five days of being received. The manager can look at the complaint and can contact the appropriate employee to find out why the employee has not resolved the complaint.

When the manager selects a complaint number and clicks the edit complaint button, the system opens the edit form and displays the information for the selected complaint as shown below.

Users can also view a report that displays a chart of historical complaints. To access this report, the user clicks on the view historical complaints button on the worksheet as shown below.

When the report opens, a chart showing historical complaints is displayed. The user can use the pivot slicers to filter the data by month and by complaint category as shown below.

This report allows users to analyze how prevalent complaints of each category are. It also allows them to see which types of complaints are most prevalent.

Technical Documentation

Worksheets

Workbook:

        Activate the homepage, hide the Past Due sheet and create a Historical and Analysis sheet as those are temporary.

wksHomePage:

Home Page is the main page of the application form where the user access the application

wksUpdate:

Used for data dump from Access database

wksPastDue:

To view the past due data, and to work upon all those complaints which are pending

wksCustomerData:

To view and use Customer information to lookup and links

Forms

frmClose :

This form is used to get the password from the user while closing any complaints

frmComplaint:

This form is used to enter a new complaint

frmEdit:

This form is used to update/edit/close the entered complaints

frmHistoricalGraph:

This form is used to show a graphical representation of the current status of the complaints so far

Modules

mdlMain:

Launches the initial user form and contains all the anchors, public variables and public arrays used in the application.

mdlChart:

For creating charts, pivots of entered complaints

mdlReports:

                This module contains all the procedures which create the reporting for viewing of past due complaints

Appendix – VBA Code

Workbook_Open

Private Sub Workbook_Open()   

    wksHomePage.Select

    Application.DisplayAlerts = False   

    On Error Resume Next   

    wksPastDue.Visible = xlSheetHidden   

    generateHistChart

    Application.DisplayFormulaBar = False   

    wksHomePage.Select   

    With ActiveWindow

        .DisplayHeadings = False

        .DisplayHorizontalScrollBar = False

        .DisplayVerticalScrollBar = False   

    End With   

End Sub

Workbook_BeforeClose

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.DisplayAlerts = False

End Sub

frmClose

Option Explicit

Private Sub cmdEnter_Click()

    Dim pWord As String

    Dim cResponse As String

    If frmClose.txtPassword.Value <> “P@ssw0rd” Then

        MsgBox (“You are not authorized to close this complaint”)

    Else

        frmEdit.txtStatus.Locked = False

        If frmEdit.txtStatus.Value = “Closed” Then

            frmEdit.txtStatus.Value = “Open”

        Else

            frmEdit.txtStatus.Value = “Closed”

        End If

        frmEdit.txtStatus.Locked = True

    End If

    frmClose.txtPassword = “”

    frmClose.Hide

End Sub

frmComplaint

Option Explicit

Private Sub cmdHistorical_Click()

    generateHistChart

    frmHistoricalGraph.show

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Application.DisplayAlerts = False

    On Error Resume Next

    Sheets(“complaintHeader”).Delete

    On Error Resume Next

    Sheets(“complaintDetail”).Delete

    Application.DisplayAlerts = True

End Sub

Private Sub cmbSearch_Change()

    frmComplaint.txtCustNo = cmbSearch.Value

End Sub

Private Sub cmdAdd_Click()

    Dim strItem(1, 4)               As String

    Dim intCatCount                 As Integer

    Dim strCategories               As String

    Dim strEntry                    As String

    Dim strEntryLen                 As Integer

    Dim x                           As Integer

    Dim strCatArray(9)              As String

    Dim y                           As Integer

    Dim intRowCount                 As Integer

    Dim intBoxesChecked             As Integer

    strCatArray(1) = “Billing”

    strCatArray(2) = “Delivery”

    strCatArray(3) = “Labeling”

    strCatArray(4) = “Wrong Product”

    strCatArray(5) = “Oozing Taffy”

    strCatArray(6) = “Hard Taffy”

    strCatArray(7) = “Design”

    strCatArray(8) = “Foreign Object”

    strCatArray(9) = “Health Concern”

    strItem(0, 0) = txtItem

    strItem(0, 1) = txtAffected

    strItem(0, 2) = txtLot

        If cbBilling = True Then

            strCategories = “1”

            ‘Sheet4.Range(“B1”).Value = 1

            ‘Sheet4.Range(“B2”).Value = 1

            send(0) = sList(0)

            send(1) = sList(1)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbDelivery = True Then

            strCategories = strCategories & “2”

            ‘Sheet4.Range(“B3”).Value = 1

            send(1) = sList(1)

            send(2) = sList(2)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbLabel = True Then

            strCategories = strCategories & “3”

            ‘Sheet4.Range(“B3”).Value = 1

            ‘Sheet4.Range(“B4”).Value = 1

            ‘Sheet4.Range(“B5”).Value = 1

             send(1) = sList(1)

             send(2) = sList(2)

             send(3) = sList(3)

             send(4) = sList(4)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbWrongProduct = True Then

            strCategories = strCategories & “4”

            ‘Sheet4.Range(“B1”).Value = 1

            ‘Sheet4.Range(“B3”).Value = 1

             send(0) = sList(0)

             send(1) = sList(1)

             send(2) = sList(2)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbOozing = True Then

            strCategories = strCategories & “5”

            ‘Sheet4.Range(“B4”).Value = 1

            ‘Sheet4.Range(“B5”).Value = 1

             send(1) = sList(1)

             send(3) = sList(3)

             send(4) = sList(4)

             send(5) = sList(5)

             send(6) = sList(6)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbHard = True Then

            strCategories = strCategories & “6”

            ‘Sheet4.Range(“B4”).Value = 1

            ‘Sheet4.Range(“B5”).Value = 1

             send(1) = sList(1)

             send(3) = sList(3)

             send(4) = sList(4)

             send(5) = sList(5)

             send(6) = sList(6)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbDesign = True Then

            strCategories = strCategories & “7”

            ‘Sheet4.Range(“B4”).Value = 1

            ‘Sheet4.Range(“B5”).Value = 1

             send(1) = sList(1)

             send(3) = sList(3)

             send(4) = sList(4)

             send(5) = sList(5)

             send(6) = sList(6)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbForeign = True Then

            strCategories = strCategories & “8”

            ‘Sheet4.Range(“B4”).Value = 1

            ‘Sheet4.Range(“B5”).Value = 1

             send(1) = sList(1)

             send(3) = sList(3)

             send(4) = sList(4)

             send(5) = sList(5)

             send(6) = sList(6)

            intBoxesChecked = intBoxesChecked + 1

        End If

        If cbHealth = True Then

            strCategories = strCategories & “9”

            ‘Sheet4.Range(“B6”).Value = 1

             send(1) = sList(1)

             send(5) = sList(5)

            intBoxesChecked = intBoxesChecked + 1

        End If

     If intBoxesChecked > 0 Then

     strItem(0, 3) = strCategories

     strEntryLen = Len(strCategories)

         For x = 1 To strEntryLen

            strEntry = strItem(0, 0) & “,” & strItem(0, 1) & “,” & strItem(0, 2) & “,” & Mid(strItem(0, 3), x, 1)

            For y = 1 To 9

                If Right(Trim(strEntry), 1) = y Then

                    strEntry = strItem(0, 0) & “,” & strItem(0, 1) & “,” & strItem(0, 2) & “,” & strCatArray(y)

                   If Sheets(“complaintDetail”).Cells(1, 1).Value = “” Then

                       Sheets(“complaintDetail”).Cells(1, 1).Value = frmComplaint.txtComplaintNum.Value

                       Sheets(“complaintDetail”).Cells(1, 2).Value = strItem(0, 0)

                       Sheets(“complaintDetail”).Cells(1, 3).Value = strItem(0, 1)

                       Sheets(“complaintDetail”).Cells(1, 4).Value = strItem(0, 2)

                       Sheets(“complaintDetail”).Cells(1, 5).Value = strCatArray(y)

                   Else

                       intRowCount = Sheets(“complaintDetail”).Cells(Rows.Count, 1).End(xlUp).Row

                       Sheets(“complaintDetail”).Cells(intRowCount + 1, 1).Value = frmComplaint.txtComplaintNum.Value

                       Sheets(“complaintDetail”).Cells(intRowCount + 1, 2).Value = strItem(0, 0)

                       Sheets(“complaintDetail”).Cells(intRowCount + 1, 3).Value = strItem(0, 1)

                       Sheets(“complaintDetail”).Cells(intRowCount + 1, 4).Value = strItem(0, 2)

                       Sheets(“complaintDetail”).Cells(intRowCount + 1, 5).Value = strCatArray(y)

                   End If

                End If

            Next y

            With lstItems

            .AddItem (strEntry)

            End With

        Next

        txtItem = “”

        txtAffected = “”

        txtLot = “”

        cbBilling = False

        cbDelivery = False

        cbLabel = False

        cbWrongProduct = False

        cbOozing = False

        cbHard = False

        cbDesign = False

        cbForeign = False

        cbHealth = False

    Else

        MsgBox “You must select at least one complaint category to continue”, , “Selection Required”

    End If

End Sub

Private Sub cmdCancel_Click()

    Dim x                   As Integer

    Dim intlCount              As Integer

    intlCount = lstItems.ListCount

    For x = intlCount – 1 To 0 Step -1

        With lstItems

            .RemoveItem (x)

        End With

    Next

    txtItem = “”

    txtAffected = “”

    txtLot = “”

    cbBilling = False

    cbDelivery = False

    cbLabel = False

    cbWrongProduct = False

    cbOozing = False

    cbHard = False

    cbDesign = False

    cbForeign = False

    cbHealth = False

    txtDesc = “”

    txtComplaintNum = “”

    txtCustNo = “”

    txtComplaintDate = “”

    txtInvoiceNo = “”

    cmbSearch = “”

    Application.DisplayAlerts = False

    Sheets(“complaintHeader”).Delete

    Sheets(“complaintDetail”).Delete

    Application.DisplayAlerts = True

    frmComplaint.Hide

    wksHomePage.Select

End Sub

Private Sub cmdRecord_Click()

    Dim x As Integer

    Dim intlCount As Integer

    If frmComplaint.lstItems.ListCount = 0 Then

        MsgBox “You must enter an item and click add the item to continue”, , “Input Required”

    Else

        Sheets(“complaintHeader”).Cells(1, 1).Value = frmComplaint.txtComplaintNum

        Sheets(“complaintHeader”).Cells(1, 2).Value = frmComplaint.txtCustNo

        Sheets(“complaintHeader”).Cells(1, 3).Value = frmComplaint.txtComplaintDate.Value

        Sheets(“complaintHeader”).Cells(1, 4).Value = frmComplaint.txtInvoiceNo

        Sheets(“complaintHeader”).Cells(1, 5).Value = frmComplaint.txtDesc

        data

        generateHistChart

        sendEmail

        intlCount = lstItems.ListCount

        For x = intlCount – 1 To 0 Step -1

            With lstItems

                .RemoveItem (x)

            End With

        Next

        txtDesc = “”

        txtComplaintNum = “”

        txtCustNo = “”

        txtInvoiceNo = “”

        cmbSearch = “”

        frmComplaint.Hide

        Application.DisplayAlerts = False

        Sheets(“complaintHeader”).Delete

        Sheets(“complaintDetail”).Delete

        Application.DisplayAlerts = True

        wksHomePage.Select

    End If

End Sub

Private Sub cmdRemove_Click()

    Dim x As Integer

    With lstItems

        For x = 0 To .ListCount – 1

            If .Selected(x) Then

                .RemoveItem (x)

            End If

        Next

    End With

End Sub

frmEdit

Option Explicit

Private Sub cmdFindInvoiceNo_Click()

    Dim intCount        As Integer

    Dim m               As Integer

    intCount = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row – 1

    For m = 1 To intCount

        If wksUpdate.Cells(m + 1, 4).Value = frmEdit.txtInvoiceNo Then

            frmEdit.txtCustomerNo = wksUpdate.Cells(m + 1, 2).Value

            frmEdit.txtComplaintDate = wksUpdate.Cells(m + 1, 3).Value

            frmEdit.txtComplaintNo = wksUpdate.Cells(m + 1, 1).Value

            frmEdit.txtDesc = wksUpdate.Cells(m + 1, 5).Value

            frmEdit.txtStatus = wksUpdate.Cells(m + 1, 6).Value

            frmEdit.txtResolution = wksUpdate.Cells(m + 1, 7).Value

            frmEdit.txtInitials = wksUpdate.Cells(m + 1, 8).Value

        End If

    Next

    If frmEdit.txtCustomerNo.Value = “” Then

       MsgBox (“Enter a valid invoice number”)

    End If

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    nextCustRow = 0

    custNo = “”

    nextDateRow = 0

    cDay = “”

    nextStatRow = 0

    cStatus = “”

    If editSource = “fromChart” Then

        Sheets(“PastDue”).Select

    Else

        wksHomePage.Select

    End If

End Sub

Private Sub cmbEditSearch_Change()

    frmEdit.txtCustomerNo = frmEdit.cmbEditSearch.Value

End Sub

Private Sub cmdFindCompNo_Click()

    Dim intCount            As Integer

    Dim y                   As Integer

    intCount = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row – 1

    For y = 1 To intCount

        If wksUpdate.Cells(y + 1, 1).Value = frmEdit.txtComplaintNo Then

            frmEdit.txtCustomerNo = wksUpdate.Cells(y + 1, 2).Value

            frmEdit.txtComplaintDate = wksUpdate.Cells(y + 1, 3).Value

            frmEdit.txtInvoiceNo = wksUpdate.Cells(y + 1, 4).Value

            frmEdit.txtDesc = wksUpdate.Cells(y + 1, 5).Value

            frmEdit.txtStatus = wksUpdate.Cells(y + 1, 6).Value

            frmEdit.txtResolution = wksUpdate.Cells(y + 1, 7).Value

            frmEdit.txtInitials = wksUpdate.Cells(y + 1, 8).Value

        End If

    Next

    If frmEdit.txtCustomerNo.Value = “” Then

       MsgBox (“Enter a valid complaint number”)

    End If

End Sub

Private Sub cmdNextCust_Click()

    If nextCustRow = 0 Then

        nextCustRow = 2

    Else

        nextCustRow = nextCustRow + 1

    End If

    Do Until wksUpdate.Cells(nextCustRow, 2).Value = “”

        If wksUpdate.Cells(nextCustRow, 2).Value = frmEdit.txtCustomerNo.Value Then

            frmEdit.txtComplaintNo = wksUpdate.Cells(nextCustRow, 1).Value

            frmEdit.txtCustomerNo = wksUpdate.Cells(nextCustRow, 2).Value

            frmEdit.txtComplaintDate = wksUpdate.Cells(nextCustRow, 3).Value

            frmEdit.txtInvoiceNo = wksUpdate.Cells(nextCustRow, 4).Value

            frmEdit.txtDesc = wksUpdate.Cells(nextCustRow, 5).Value

            frmEdit.txtStatus = wksUpdate.Cells(nextCustRow, 6).Value

            frmEdit.txtResolution = wksUpdate.Cells(nextCustRow, 7).Value

            frmEdit.txtInitials = wksUpdate.Cells(nextCustRow, 8).Value

            custNo = frmEdit.txtCustomerNo.Value

            Exit Do

        End If

        nextCustRow = nextCustRow + 1

    Loop

    If wksUpdate.Cells(nextCustRow, 2).Value = “” And custNo = “” Then

        MsgBox (“There are no complaints listed for this customer.”)

        nextCustRow = 0

    ElseIf wksUpdate.Cells(nextCustRow, 2).Value = “” And custNo <> “” Then

        MsgBox (“There are no more complaints listed for this customer.”)

        nextCustRow = 0

        custNo = “”

        frmEdit.txtComplaintNo = “”

        frmEdit.txtComplaintDate = “”

        frmEdit.txtInvoiceNo = “”

        frmEdit.txtDesc = “”

        frmEdit.txtStatus = “”

        frmEdit.txtResolution = “”

        frmEdit.txtInitials = “”

    End If

End Sub

Private Sub cmdNextStatus_Click()

    If nextStatRow = 0 Then

        nextStatRow = 2

    Else

        nextStatRow = nextStatRow + 1

    End If

    Do Until wksUpdate.Cells(nextStatRow, 6).Value = “”

        If wksUpdate.Cells(nextStatRow, 6).Value = “Open” Then

            frmEdit.txtComplaintNo = wksUpdate.Cells(nextStatRow, 1).Value

            frmEdit.txtCustomerNo = wksUpdate.Cells(nextStatRow, 2).Value

            frmEdit.txtComplaintDate = wksUpdate.Cells(nextStatRow, 3).Value

            frmEdit.txtInvoiceNo = wksUpdate.Cells(nextStatRow, 4).Value

            frmEdit.txtDesc = wksUpdate.Cells(nextStatRow, 5).Value

            frmEdit.txtStatus = wksUpdate.Cells(nextStatRow, 6).Value

            frmEdit.txtResolution = wksUpdate.Cells(nextStatRow, 7).Value

            frmEdit.txtInitials = wksUpdate.Cells(nextStatRow, 8).Value

            cStatus = frmEdit.txtStatus

            Exit Do

        End If

        nextStatRow = nextStatRow + 1

    Loop

    If wksUpdate.Cells(nextStatRow, 6).Value = “” And cStatus = “” Then

        MsgBox (“There are no open complaints.”)

        nextStatRow = 0

    ElseIf wksUpdate.Cells(nextStatRow, 3).Value = “” And cStatus <> “” Then

        MsgBox (“There are no more complaints listed for this status.”)

        nextStatRow = 0

        cStatus = “”

        frmEdit.txtComplaintNo = “”

        frmEdit.txtCustomerNo = “”

        frmEdit.txtComplaintDate = “”

        frmEdit.txtInvoiceNo = “”

        frmEdit.txtDesc = “”

        frmEdit.txtStatus = “”

        frmEdit.txtResolution = “”

        frmEdit.txtInitials = “”

    End If

End Sub

Private Sub cmdUpdate_Click()

Dim cn                      As New ADODB.Connection

Dim sql                     As String

    On Error Resume Next ‘ these lines should allow you to connect.  Depending on your version of windows, only one will execute without error.

    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    On Error GoTo 0

    sql = ” UPDATE ComplaintHeader ” _

& ” SET ComplaintNo ='” & frmEdit.txtComplaintNo _

& “‘, CustomerNo ='” & frmEdit.txtCustomerNo _

& “‘, ComplaintDate ='” & frmEdit.txtComplaintDate _

& “‘, InvoiceNo ='” & frmEdit.txtInvoiceNo _

& “‘, ComplaintDesc ='” & frmEdit.txtDesc _

& “‘, Status ='” & frmEdit.txtStatus _

& “‘, Resolution ='” & frmEdit.txtResolution _

& “‘, Initials ='” & frmEdit.txtInitials & “‘ WHERE ComplaintNo ='” & frmEdit.txtComplaintNo & “‘;”

    sql = Replace(sql, frmEdit.txtDesc.Value, Replace(frmEdit.txtDesc.Value, “‘”, “””))

    sql = Replace(sql, frmEdit.txtResolution.Value, Replace(frmEdit.txtResolution.Value, “‘”, “””))

    cn.Execute sql

    cn.Close

    frmEdit.txtComplaintNo = “”

    frmEdit.txtCustomerNo = “”

    frmEdit.txtComplaintDate = “”

    frmEdit.txtInvoiceNo = “”

    frmEdit.txtDesc = “”

    frmEdit.txtStatus = “”

    frmEdit.txtResolution = “”

    frmEdit.txtInitials = “”

    frmEdit.cmbEditSearch = “”

    edit

    nextCustRow = 0

    custNo = “”

    nextDateRow = 0

    cDay = “”

    nextStatRow = 0

    cStatus = “”

    frmEdit.Hide

    If editSource = “fromChart” Then

        Sheets(“PastDue”).Select

    Else

        wksHomePage.Select

    End If

End Sub

Private Sub txtStatus_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    frmClose.show

End Sub

mdlChart

Option Explicit

Sub dpChart()

    Dim ch                  As Chart

    Dim sh                  As Shape

    Dim objBtnEdit          As Object

    Dim objBtnClose         As Object

    Dim intRowNum           As Integer

        ‘to count the number of rows

        intRowNum = Sheets(“PastDue”).Cells(Rows.Count, 1).End(xlUp).Row

        ‘to create a table

        ActiveSheet.ListObjects.Add(xlSrcRange, Range(“$A$1”).CurrentRegion, , xlYes).Name = _

        “daysPassed”

        Range(“daysPassed[ComplaintDate]”).NumberFormat = “m/d/yyyy”

        ‘to create a chart

        Set sh = Sheets(“PastDue”).Shapes.AddChart

        Set ch = sh.Chart

        ch.SetSourceData Source:=Range(“PastDue!$A$1:$B$” & intRowNum)

        ch.ChartTitle.Caption = “Days Passed”

        ch.ChartType = xlColumnClustered

        With sh.Fill

            .Visible = msoTrue

            .ForeColor.ObjectThemeColor = msoThemeColorText2

            .ForeColor.TintAndShade = 0

            .ForeColor.Brightness = 0.6000000238

            .Transparency = 0

            .Solid

        End With

        With ch.SeriesCollection(1).Format.Fill

            .Visible = msoTrue

            .ForeColor.RGB = RGB(255, 0, 0)

            .Transparency = 0

            .Solid

        End With

        With ch.SeriesCollection(1).Format.ThreeD

            .BevelTopType = msoBevelCircle

            .BevelTopInset = 6

            .BevelTopDepth = 6

        End With

        ch.SetElement (msoElementLegendRight)

        sh.Top = Range(“A2”).Top

        sh.Left = Range(“F1”).Left

        sh.Height = “300”

        sh.Width = “500”

        Set objBtnEdit = ActiveSheet.Buttons.Add(252, 7.5, 65.25, 38.5)

        objBtnEdit.OnAction = “viewPastDue”

        objBtnEdit.Characters.Text = “Edit Complaint”

        With objBtnEdit.Characters(Start:=1, Length:=14).Font

            .Name = “Calibri”

            .FontStyle = “Regular”

            .Size = 11

            .ColorIndex = 1

        End With

        ActiveSheet.Shapes(objBtnEdit.Name).ScaleHeight 1.2105263158, msoFalse, _

        msoScaleFromTopLeft

        ActiveSheet.Shapes(objBtnEdit.Name).IncrementTop 0.75

        Set objBtnClose = ActiveSheet.Buttons.Add(252, 75, 65.25, 38.5)

        objBtnClose.OnAction = “closePastDue”

        objBtnClose.Characters.Text = “Close”

        With objBtnClose.Characters(Start:=1, Length:=14).Font

            .Name = “Calibri”

            .FontStyle = “Regular”

            .Size = 11

            .ColorIndex = 1

        End With

        ActiveSheet.Shapes(objBtnClose.Name).ScaleHeight 1.2105263158, msoFalse, _

        msoScaleFromTopLeft

        ActiveSheet.Shapes(objBtnClose.Name).IncrementTop 0.75

    Range(“A2”).Select

End Sub

Sub buildHistorical()

    Sheets.Add.Name = “Historical”

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        Range(“Analysis!$A$1”).CurrentRegion, Version:=xlPivotTableVersion14).CreatePivotTable _

        TableDestination:=”Historical!R1C1″, TableName:=”PivotTable1″, DefaultVersion _

        :=xlPivotTableVersion14

    Sheets(“Historical”).Select

    Cells(1, 1).Select

    ActiveSheet.Shapes.AddChart.Select

    ActiveChart.ChartType = xlColumnClustered

    ActiveChart.SetSourceData Source:=Range(“Historical!$A$1:$C$18”)

    ActiveSheet.Shapes(“Chart 1”).IncrementLeft 192

    ActiveSheet.Shapes(“Chart 1”).IncrementTop 15

    histChart

End Sub

Sub histChart()

    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“ComplaintCategory”)

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“ComplaintDate”)

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables(“PivotTable1”).AddDataField ActiveSheet.PivotTables( _

        “PivotTable1”).PivotFields(“ComplaintNo”), “Count of ComplaintNo”, xlCount

    Range(“B2”).Select

    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _

        False, True, False, False)

    ActiveSheet.ChartObjects(“Chart 1”).Activate

    ActiveSheet.Shapes(“Chart 1”).IncrementLeft -639

    ActiveSheet.Shapes(“Chart 1”).IncrementTop -111.75

    ActiveSheet.ChartObjects(“Chart 1”).Activate

    ActiveSheet.Shapes(“Chart 1”).ScaleWidth 1.4145833333, msoFalse, _

        msoScaleFromTopLeft

    ActiveSheet.Shapes(“Chart 1”).ScaleHeight 1.5173611111, msoFalse, _

        msoScaleFromTopLeft

    ActiveChart.SetElement (msoElementChartTitleAboveChart)

    ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(“PivotTable1”), _

        “ComplaintDate”).Slicers.Add ActiveSheet, , “ComplaintDate”, “ComplaintDate”, _

        60, 323.25, 144, 198.75

    ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables(“PivotTable1”), _

        “ComplaintCategory”).Slicers.Add ActiveSheet, , “ComplaintCategory”, _

        “ComplaintCategory”, 97.5, 360.75, 144, 198.75

    ActiveChart.ChartTitle.Text = “Historical Complaints”

    ActiveSheet.Shapes.Range(Array(“ComplaintCategory”)).Select

    ActiveSheet.Shapes(“ComplaintCategory”).IncrementLeft 177

    ActiveSheet.Shapes(“ComplaintCategory”).IncrementTop -95.25

    ActiveSheet.Shapes.Range(Array(“ComplaintDate”)).Select

    ActiveSheet.Shapes(“ComplaintDate”).IncrementLeft 373.5

    ActiveSheet.Shapes(“ComplaintDate”).IncrementTop -57

    With ActiveSheet.Shapes(“Chart 1”).Fill

        .Visible = msoTrue

        .ForeColor.ObjectThemeColor = msoThemeColorAccent1

        .ForeColor.TintAndShade = 0

        .ForeColor.Brightness = 0

        .Solid

    End With

    With ActiveSheet.Shapes(“Chart 1”).Fill

        .Visible = msoTrue

        .ForeColor.ObjectThemeColor = msoThemeColorText2

        .ForeColor.TintAndShade = 0

        .ForeColor.Brightness = 0.8000000119

        .Transparency = 0

        .Solid

    End With

    ActiveSheet.ChartObjects(“Chart 1”).Activate

    ActiveChart.ShowAllFieldButtons = False

    ActiveSheet.ChartObjects(“Chart 1”).Activate

    ActiveChart.SeriesCollection(1).Select

    ActiveChart.SetElement (msoElementDataLabelInsideEnd)

    ActiveChart.SeriesCollection(2).Select

    ActiveChart.SetElement (msoElementDataLabelInsideEnd)

    ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

mdlMain

Option Explicit

Public send(8)              As String

Public sList(8)             As String

Public formName             As String

Public nextCustRow          As Integer

Public custNo               As String

Public nextDateRow          As Integer

Public cDay                 As Variant

Public nextStatRow          As Integer

Public cStatus              As String

Public pdCompNo             As String

Public editSource           As String

Sub sendEmail()

    Dim OLApp               As Object

    Dim rnglRange           As Range

    Dim custName            As Variant

    Dim custNum             As Variant

    Dim arg4                As Boolean

    Dim intColumn           As Integer

    Dim itemProbs           As String

    Dim x                   As Integer

    Dim strArray            As Variant

    Dim sendTo              As String

    Dim y                   As Integer

    ‘to create vlookup for customer name

    Set rnglRange = wksCustomerData.Range(“A2”).CurrentRegion

        custNum = frmComplaint.txtCustNo.Value

        arg4 = False

        intColumn = 2

        custName = Application.WorksheetFunction.VLookup(custNum, rnglRange, intColumn, arg4)

    ‘ to create list of items

    With frmComplaint.lstItems

        For x = 0 To frmComplaint.lstItems.ListCount – 1

            strArray = Split(frmComplaint.lstItems.List(x), “,”)

            itemProbs = itemProbs & ” ” & Chr(149) & ” Item ” & strArray(0) & ” had ” & strArray(1) & ” case(s) of lot number ” _

& strArray(2) & ” affected by ” & LCase(strArray(3)) & ” issues

        Next

    End With

        ‘ to open outlook and send message

        On Error Resume Next

        Set OLApp = GetObject(, “Outlook.Application”)

        On Error GoTo 0

        If OLApp Is Nothing Then

            openOutlook

            Exit Sub

        Else

            For y = 0 To UBound(send)

                If send(y) <> “” Then

                    With OLApp.CreateItem(olMailItem)

                        .To = send(y)

                        .Subject = “Complaint ” & frmComplaint.txtComplaintNum

                        .Attachments.Add ThisWorkbook.Path & “\temp.GIF”

                        .HTMLBody = “Attention: ” & Application.WorksheetFunction.Proper(Left(send(y), Application.WorksheetFunction.Find(“@”, send(y)) – 1)) _

& “

Complaint ” & frmComplaint.txtComplaintNum _

& ” has been recorded for ” & custName _

& ” regarding invoice ” & frmComplaint.txtInvoiceNo & “

” _

& custName & ” described the complaint as follows:

” _

& frmComplaint.txtDesc & “

” & “The issues below were recorded for the following products:

” & itemProbs _

& “
Please take the necessary action to resolve these concerns and record the resolution by: ” & Date + 3

                        .send

                    End With

                End If

            Next y

        End If

    ‘to clear email recipient list

    send(0) = “”

    send(1) = “”

    send(2) = “”

    send(3) = “”

    send(4) = “”

    send(5) = “”

    send(6) = “”

    send(7) = “”

    send(8) = “”

End Sub

Sub openOutlook()

    Dim newSecond As Variant

    Dim newMinute As Variant

    Dim newHour As Variant

    Dim waitTime As Variant

    Shell (“Outlook”)

    ‘to wait while program opens

    newHour = Hour(Now())

    newMinute = Minute(Now())

    newSecond = Second(Now()) + 15

    waitTime = TimeSerial(newHour, newMinute, newSecond)

    Application.Wait waitTime

    sendEmail

End Sub

Sub openForm()

    formName = “New”

    setup

    frmComplaint.show

End Sub

Sub setup()

    ‘to initialize email addresses

    sList(0) = “[email protected]

    sList(1) = “[email protected]

    sList(2) = “[email protected]

    sList(3) = “[email protected]

    sList(4) = “[email protected]

    sList(5) = “[email protected]

    sList(6) = “[email protected]

    ‘to delete sheets if necessary

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    On Error Resume Next

    Sheets(“complaintHeader”).Delete

    On Error Resume Next

    Sheets(“complaintDetail”).Delete

    On Error Resume Next

    Sheets.Add.Name = “complaintHeader”

    On Error Resume Next

    Sheets.Add.Name = “complaintDetail”

    loadCustomers

    nextID

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

    wksHomePage.Select

End Sub

Sub data()

    Dim cn                  As New ADODB.Connection

    Dim sql                 As String

    Dim dCount              As Integer

    Dim p                   As Integer

    Application.ScreenUpdating = False

    On Error Resume Next ‘ these lines should allow you to connect.  Depending on your version of windows, only one will execute without error.

    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    On Error GoTo 0

    ‘to insert data into complaint header table

    sql = ” INSERT INTO ComplaintHeader(ComplaintNo, CustomerNo, ComplaintDate, InvoiceNo, ComplaintDesc, Status)” _

& ” values(‘” & Sheets(“complaintHeader”).Cells(1, 1).Value & “‘,'” & Sheets(“complaintHeader”).Cells(1, 2).Value & “‘,'” & Sheets(“complaintHeader”).Cells(1, 3).Value _

& “‘,'” & Sheets(“complaintHeader”).Cells(1, 4).Value & “‘,'” & Trim(Sheets(“complaintHeader”).Cells(1, 5).Value) & “‘, ‘Open’);”

    sql = Replace(sql, Trim(Sheets(“complaintHeader”).Cells(1, 5).Value), Replace(Trim(Sheets(“complaintHeader”).Cells(1, 5).Value), “‘”, “””))

    cn.Execute sql

    Sheets(“complaintHeader”).Cells(1, 1).EntireRow.Delete

    dCount = Sheets(“complaintDetail”).Cells(Rows.Count, 1).End(xlUp).Row

    For p = 1 To dCount

        ‘to insert data into detail table

        sql = ” INSERT INTO ComplaintDetail(ComplaintNo, ItemNo, ComplaintCategory, QuantityAffected, LotNumber)” _

& ” values(‘” & Sheets(“complaintDetail”).Cells(1, 1).Value & “‘,'” & Sheets(“complaintDetail”).Cells(1, 2).Value & “‘,'” & Sheets(“complaintDetail”).Cells(1, 5).Value _

& “‘,'” & Sheets(“complaintDetail”).Cells(1, 3).Value & “‘,'” & Sheets(“complaintDetail”).Cells(1, 4).Value & “‘);”

        cn.Execute sql

        Sheets(“complaintDetail”).Cells(1, 1).EntireRow.Delete

    Next

    cn.Close

    Application.ScreenUpdating = True

End Sub

Sub edit()

    Dim cn                  As New ADODB.Connection

    Dim sql                 As String

    Dim r                   As Integer

    Dim rs                  As ADODB.Recordset

    Dim rNum                As Integer

    Application.ScreenUpdating = False

    wksUpdate.Visible = True

    wksUpdate.Select

    rNum = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row

    If wksUpdate.Cells(2, 1).Value <> “” Then

        Rows(“2:” & rNum).Delete

    End If

    On Error Resume Next ‘ these lines should allow you to connect.  Depending on your version of windows, only one will execute without error.

    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    On Error GoTo 0

    sql = ” SELECT * FROM ComplaintHeader;”

    Set rs = cn.Execute(sql)

    r = 2

    rs.MoveFirst

    Do Until rs.EOF

        Cells(r, 1).Value = rs.Fields(“ComplaintNo”)

        Cells(r, 2).Value = rs.Fields(“CustomerNo”)

        Cells(r, 3).Value = rs.Fields(“ComplaintDate”)

        Cells(r, 4).Value = rs.Fields(“InvoiceNo”)

        Cells(r, 5).Value = rs.Fields(“ComplaintDesc”)

        Cells(r, 6).Value = rs.Fields(“Status”)

        Cells(r, 7).Value = rs.Fields(“Resolution”)

        Cells(r, 8).Value = rs.Fields(“Initials”)

        rs.MoveNext

        r = r + 1

    Loop

    cn.Close

    wksUpdate.Visible = False

    Application.ScreenUpdating = True

End Sub

Sub openEdit()

    Dim intCount              As Integer

    Dim y                   As Integer

    formName = “Edit”

    edit

    loadCustomers

    If editSource = “fromChart” Then

        intCount = wksUpdate.Cells(Rows.Count, 1).End(xlUp).Row – 1

        For y = 1 To intCount

            If wksUpdate.Cells(y + 1, 1).Value = pdCompNo Then

                frmEdit.txtComplaintNo = pdCompNo

                frmEdit.txtCustomerNo = wksUpdate.Cells(y + 1, 2).Value

                frmEdit.txtComplaintDate = wksUpdate.Cells(y + 1, 3).Value

                frmEdit.txtInvoiceNo = wksUpdate.Cells(y + 1, 4).Value

                frmEdit.txtDesc = wksUpdate.Cells(y + 1, 5).Value

                frmEdit.txtStatus = wksUpdate.Cells(y + 1, 6).Value

                frmEdit.txtResolution = wksUpdate.Cells(y + 1, 7).Value

                frmEdit.txtInitials = wksUpdate.Cells(y + 1, 8).Value

            End If

        Next

        If frmEdit.txtCustomerNo.Value = “” Then

           MsgBox (“Enter a valid complaint number”)

        End If

        Sheets(“PastDue”).Select

    End If

    frmEdit.show

End Sub

Sub nextID()

    Dim strStamp                As String

    Dim cn                      As New ADODB.Connection

    Dim sql                     As String

    ‘Dim r                       As Integer

    Dim rs                      As ADODB.Recordset

    strStamp = “New”

    On Error Resume Next ‘ these lines should allow you to connect.  Depending on your version of windows, only one will execute without error.

    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    On Error GoTo 0

    sql = ” INSERT INTO ComplaintID(Stamp)” _

& ” values(‘” & strStamp & “‘);”

    cn.Execute sql

    sql = ” SELECT MAX(ID) AS ID FROM ComplaintID;”

    Set rs = cn.Execute(sql)

    rs.MoveFirst

    frmComplaint.txtComplaintNum.Value = “C” & rs.Fields(“ID”)

    cn.Close

End Sub

Sub loadCustomers()

    Dim intRowNumber            As Integer

    Dim strCustomers()          As String

    Dim x                       As Integer

    Dim c                       As Integer

    wksCustomerData.Visible = True

    wksCustomerData.Select

    intRowNumber = wksCustomerData.Cells(Rows.Count, 1).End(xlUp).Row – 1

    ReDim strCustomers(intRowNumber, 2)

    c = 1

    For x = 0 To UBound(strCustomers)

        strCustomers(x, 0) = Cells(c, 2).Value

        strCustomers(x, 1) = Cells(c, 1).Value

        c = c + 1

    Next x

    If formName = “New” Then

        With frmComplaint.cmbSearch

            .List = strCustomers

        End With

    Else

        With frmEdit.cmbEditSearch

            .List = strCustomers

        End With

    End If

    wksCustomerData.Visible = False

    Application.ScreenUpdating = True

    wksHomePage.Select

End Sub

Sub viewPastDue()

    Sheets(“PastDue”).Select

    pdCompNo = ActiveCell.Value

    editSource = “fromChart”

    openEdit

    editSource = “”

End Sub

Sub closePastDue()

    wksHomePage.Activate

    wksPastDue.Visible = xlSheetHidden

End Sub

Sub generateHistChart()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    On Error Resume Next

    Sheets(“Analysis”).Delete

    On Error Resume Next

    Sheets(“Historical”).Delete

    Application.DisplayAlerts = True

    historical

    loadChart

    Application.ScreenUpdating = True

End Sub

Sub loadChart()

Dim CurrentChart            As Chart

Dim Fname                   As String

    Set CurrentChart = Sheets(“Historical”).ChartObjects(1).Chart

        Fname = ThisWorkbook.Path & “\temp.gif”

        CurrentChart.Export Filename:=Fname, FilterName:=”GIF”

        frmHistoricalGraph.Image1.Picture = LoadPicture(Fname)

End Sub

Sub viewHistoricalChart()

    Sheets(“Historical”).Visible = True

    Sheets(“Historical”).Select

End Sub

Sub rNew(control As IRibbonControl)

    openForm

End Sub

Sub rEdit(control As IRibbonControl)

    openEdit

End Sub

Sub rPastDue(control As IRibbonControl)

    pastDueComplaints

End Sub

Sub rHistorical(control As IRibbonControl)

    viewHistoricalChart

End Sub

Sub rCloseHistorical(control As IRibbonControl)

    ActiveWorkbook.SlicerCaches(“Slicer_ComplaintDate”).ClearManualFilter

    ActiveWorkbook.SlicerCaches(“Slicer_ComplaintCategory”).ClearManualFilter

    Sheets(“Historical”).Visible = False

End Sub

Sub rDeletePastDue(control As IRibbonControl)

    Application.DisplayAlerts = False

    On Error Resume Next

    Sheets(“PastDue”).Delete

    Application.DisplayAlerts = True

End Sub

mdlReports

Option Explicit

Sub pastDueComplaints()

    Dim cn                  As New ADODB.Connection

    Dim sql                 As String

    Dim rs                  As Recordset

    Dim intCount            As Integer

    Application.ScreenUpdating = False

    On Error Resume Next ‘ these lines should allow you to connect.  Depending on your version of windows, only one will execute without error.

    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    On Error GoTo 0

    sql = ” SELECT ComplaintNo, ComplaintDate, Resolution, (DATE() – ComplaintDate) AS Days ” _

& ” FROM ComplaintHeader WHERE Resolution = “””” ORDER BY (DATE() – ComplaintDate) DESC; “

    Set rs = cn.Execute(sql)

    rs.MoveFirst

    With wksPastDue

        .Visible = xlSheetVisible

        .Activate

        .Columns.EntireColumn.Delete

        .Cells(1, 1).Value = “ComplaintNo”

        .Cells(1, 2).Value = ” Days Passed”

        .Cells(1, 3).Value = “ComplaintDate”

    End With

    intCount = 2

    Do Until rs.EOF

    If rs.Fields(“Days”) > 5 Then

        wksPastDue.Cells(intCount, 1).Value = rs.Fields(“ComplaintNo”)

        wksPastDue.Cells(intCount, 2).Value = rs.Fields(“Days”)

        wksPastDue.Cells(intCount, 3).Value = rs.Fields(“ComplaintDate”)

        intCount = intCount + 1

        End If

        rs.MoveNext

    Loop

    cn.Close

    dpChart

    Application.ScreenUpdating = True

    MsgBox (“To edit or view a complaint, select the cell in the table for the complaint that you want view and click the Edit Complaint button”)

End Sub

Sub historical()

    Dim cn          As New ADODB.Connection

    Dim sql         As String

    Dim rs          As Recordset

    Dim intCount    As Integer

    On Error Resume Next ‘ these lines should allow you to connect.  Depending on your version of windows, only one will execute without error.

    cn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & ThisWorkbook.Path & “\Customer_Complaint.accdb” & “;”

    On Error GoTo 0

    sql = ” SELECT d.ComplaintNo AS ComplaintNo, h.CustomerNo AS CustomerNo, h.ComplaintDate AS ComplaintDate,  ” _

& ” d.ItemNo AS ItemNo, d.ComplaintCategory AS ComplaintCategory, d.QuantityAffected AS QuantityAffected, ” _

& ” d.LotNumber AS LotNumber, h.Status AS Status ” _

& ” FROM ComplaintHeader h INNER JOIN ComplaintDetail d ON h.ComplaintNo = d.ComplaintNo; “

    Set rs = cn.Execute(sql)

    rs.MoveFirst

    Sheets.Add.Name = “Analysis”

        Cells(1, 1).Value = “ComplaintNo”

        Cells(1, 2).Value = “CustomerNo”

        Cells(1, 3).Value = “ComplaintDate”

        Cells(1, 4).Value = “ItemNo”

        Cells(1, 5).Value = “ComplaintCategory”

        Cells(1, 6).Value = “QuantityAffected”

        Cells(1, 7).Value = “LotNumber”

        Cells(1, 8).Value = “Status”

    intCount = 2

    Do Until rs.EOF

        Cells(intCount, 1).Value = rs.Fields(“ComplaintNo”)

        Cells(intCount, 2).Value = rs.Fields(“CustomerNo”)

        Cells(intCount, 3).Value = rs.Fields(“ComplaintDate”)

        Cells(intCount, 4).Value = rs.Fields(“ItemNo”)

        Cells(intCount, 5).Value = rs.Fields(“ComplaintCategory”)

        Cells(intCount, 6).Value = rs.Fields(“QuantityAffected”)

        Cells(intCount, 7).Value = rs.Fields(“LotNumber”)

        Cells(intCount, 8).Value = rs.Fields(“Status”)

        intCount = intCount + 1

        rs.MoveNext

    Loop

    cn.Close

    Columns(“C:C”).NumberFormat = “m/d/yyyy”

    buildHistorical

    Sheets(“Analysis”).Visible = False

    Sheets(“Historical”).Visible = False

End Sub