

' ----------------------------------------------------------------
' Statements to be entered in the Immediate Window
' (See book pages 143-144)
' ----------------------------------------------------------------

set myTestCollection = New Collection 
myTestCollection.Add "first member" 
myTestCollection.Add "second member" 
myTestCollection.Add "third member" 
?myTestCollection.Count
?myTestCollection.Item(1)
?myTestCollection(1)
myTestCollection.Remove 1
For Each m in myTestCollection : myTestCollection.Remove 1 : Next
?myTestCollection.Count


' ----------------------------------------------------------------
' Hands-On 8-1
' ----------------------------------------------------------------

' ensure that there is only one Option Base 1 statement
' at the top of the module

Option Base 1


Sub NewEmployees()
    ' declare the employees collection
    Dim colEmployees As New Collection
    ' declare a variable to hold each element of a collection
    Dim emp As Variant
    
    ' Add 3 new employees to the collection
    With colEmployees
        .Add Item:="John Collins", Key:="128634456"
        .Add Item:="Mary Poppins", Key:="223998765"
        .Add Item:="Karen Loza", Key:="120228876", Before:=2
    End With
    
    ' list the members of the collection
    For Each emp In colEmployees
        Debug.Print emp
    Next
    
    MsgBox "There are " & colEmployees.Count & " employees."
End Sub



' ----------------------------------------------------------------
' Hands-On 8-2
' ----------------------------------------------------------------

Sub NewEmployees2()
    ' declare the employees collection
    Dim colEmployees As New Collection
    ' declare a variable to hold each element of a collection
    Dim emp As Variant
    
    ' Add 3 new employees to the collection
    With colEmployees
        .Add Item:="John Collins", Key:="128634456"
        .Add Item:="Mary Poppins", Key:="223998765"
        .Add Item:="Karen Loza", Key:="120228876", Before:=2
    End With
    
    ' list the members of the collection
    For Each emp In colEmployees
        Debug.Print emp
    Next
    
    MsgBox "There are " & colEmployees.Count & " employees."

   ' remove the third element from the collection
   colEmployees.Remove 3
   MsgBox colEmployees.Count & " employees remain."

End Sub


' ----------------------------------------------------------------
' Custom Project 8-1 (step 1)
' There is no VBA code for this step.
' ----------------------------------------------------------------

' ----------------------------------------------------------------
' Custom Project 8-1 (step 2 - Enter in the CEmployee Class module)
' ----------------------------------------------------------------

' Ensure that there is only one Option Explicit statement
' at the top of the module

Option Explicit 

' declarations
Private m_LastName As String
Private m_FirstName As String
Private m_Salary As Currency
Private m_ID As String


' ----------------------------------------------------------------
'Custom Project 8-1 (step 3 - Enter in the CEmployee Class module)  
' ----------------------------------------------------------------

Property Get ID() As String
    ID = m_ID
End Property

Property Get LastName() As String
    LastName = m_LastName
End Property

Property Get FirstName() As String
    FirstName = m_FirstName
End Property

Property Get Salary() As Currency
    Salary = m_Salary
End Property


' ----------------------------------------------------------------
' Custom Project 8-1 (Step 4 - Enter in the CEmployee Class module)
' ----------------------------------------------------------------

Property Let ID(ref As String)
    m_ID = ref
End Property

Property Let LastName(L As String)
    m_LastName = L
End Property

Property Let FirstName(F As String)
    m_FirstName = F
End Property

Property Let Salary(ByVal dollar As Currency)
    m_Salary = dollar
End Property

' ----------------------------------------------------------------
' Custom Project 8-1 (Step 5 - Enter in the CEmployee Class module)
' ----------------------------------------------------------------

Public Function CalcNewSalary(choice As Integer, curSalary As Currency, _
    amount As Long) As Currency
    Select Case choice
        Case 1 ' by percent
            CalcNewSalary = curSalary + ((curSalary * amount) / 100)
        Case 2 ' by amount
            CalcNewSalary = curSalary + amount
    End Select
End Function

' ----------------------------------------------------------------
' Custom Project 8-1 (Step 6 - Enter in the EmpOperations Standard module)
' ----------------------------------------------------------------

' Ensure that there is only one Option Explicit statement
' at the top of the module

Option Explicit

Dim emp As New CEmployee
Dim CEmployee As New Collection

' ----------------------------------------------------------------
' Custom Project 8-1 (Step 7 - no code in this step)
' Please follow the instructions in the book.
' ----------------------------------------------------------------


' ----------------------------------------------------------------
' Custom Project 8-1 (Step 8 - code for the frmEmployeeSalaries form)
' ----------------------------------------------------------------

' ensure that there is only one Option Explicit statement
' at the top of the module

Option Explicit

' variable declarations
Dim choice As Integer
Dim amount As Long

Private Sub UserForm_Initialize()
    txtLastName.SetFocus
    cmdUpdate.Enabled = False
    cmdDelete.Enabled = False
    lboxPeople.Enabled = False
    frChangeSalary.Enabled = False
    frChangeSalary.Value = 0
    frSalaryMod.Enabled = False
    frSalaryMod.Value = 0
    txtRaise.Enabled = False
    txtRaise.Value = ""
End Sub

Private Sub Form_Load()
    Call UserForm_Initialize
End Sub

Private Sub cmdAdd_Click()
    Dim strLast As String
    Dim strFirst As String
    Dim curSalary As Currency
   
    'Validate data entry
    If IsNull(txtLastName.Value) Or txtLastName.Value = "" _
        Or IsNull(txtFirstName.Value) Or txtFirstName.Value = "" _
        Or IsNull(txtSalary.Value) Or txtSalary.Value = "" Then

        MsgBox "Enter Last Name, First Name and Salary."
        txtLastName.SetFocus
        Exit Sub
    End If
    If Not IsNumeric(txtSalary) Then
        MsgBox "You must enter a value for the Salary."
    txtSalary.SetFocus
        Exit Sub
    End If
    If txtSalary < 0 Then
        MsgBox "Salary cannot be a negative number."
        Exit Sub
    End If
    
    'assign text box values to variables
    strLast = txtLastName
    strFirst = txtFirstName
    curSalary = txtSalary
    
    'enable buttons and other controls
    cmdUpdate.Enabled = True
    cmdDelete.Enabled = True
    lboxPeople.Enabled = True
    frChangeSalary.Enabled = True
    frSalaryMod.Enabled = True
    txtRaise.Enabled = True
    txtRaise.Value = ""
    lboxPeople.Visible = True
    
    'enter data into the CEmployees collection
    EmpOperations.AddEmployee strLast, strFirst, curSalary
    
    'update list box
    lboxPeople.RowSource = GetValues
    
    'delete data from text boxes
    txtLastName = ""
    txtFirstName = ""
    txtSalary = ""
    txtLastName.SetFocus
End Sub
Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub cmdUpdate_Click()
    Dim numOfPeople As Integer
    Dim colItem As Integer
    
    ' validate user selections
    If frChangeSalary.Value = 0 Or frSalaryMod.Value = 0 Then
        MsgBox "You must choose the appropriate option button in " & vbCr _
            & " the 'Salary Modification' and 'Change the Salary for' areas.", _
            vbOKOnly, "Insufficient selection"
        Exit Sub
    ElseIf Not IsNumeric(txtRaise) Or txtRaise = "" Then
        MsgBox "You must enter a number."
        txtRaise.SetFocus
        Exit Sub
    ElseIf frSalaryMod.Value = 1 And lboxPeople.ListIndex = -1 Then
        MsgBox "Click the employee name.", , "Missing selection in the List box"
        Exit Sub
    End If
    
    If frSalaryMod.Value = 1 And lboxPeople.ListIndex = -1 Then
        MsgBox "Enter data or select an option."
        Exit Sub
    End If
    ' get down to calculations
    amount = txtRaise
    colItem = lboxPeople.ListIndex + 1
    If frChangeSalary.Value = 1 And frSalaryMod.Value = 1 Then
        ' by percent, one employee
        choice = 1
        numOfPeople = 1
    ElseIf frChangeSalary.Value = 2 And frSalaryMod.Value = 1 Then
        ' by amount, one employee
        choice = 2
        numOfPeople = 1
    ElseIf frChangeSalary.Value = 1 And frSalaryMod.Value = 2 Then
        ' by percent, all employees
        choice = 1
        numOfPeople = 2
    ElseIf frChangeSalary.Value = 2 And frSalaryMod.Value = 2 Then
        ' by amount, all employees
        choice = 2
        numOfPeople = 2
    End If
    
    UpdateSalary choice, amount, numOfPeople, colItem
    lboxPeople.RowSource = GetValues
End Sub

Private Sub cmdDelete_Click()
    ' make sure an employee is highlighted in the list box control
    If lboxPeople.ListIndex > -1 Then
        DeleteEmployee lboxPeople.ListIndex + 1
        If lboxPeople.ListCount = 1 Then
            lboxPeople.RowSource = GetValues
            UserForm_Initialize
        Else
            lboxPeople.RowSource = GetValues
        End If
    Else
        MsgBox "Click the item you want to remove."
    End If
End Sub


' ----------------------------------------------------------------
' Custom Project 8-1 (Step 8 - code for the EmpOperations Standard module)
' ----------------------------------------------------------------

' Ensure that the top of the module contains the following four statements
Option Compare Database
Option Explicit
Dim emp As New CEmployee
Dim CEmployee As New Collection


Sub AddEmployee(empLast As String, empFirst As String, _
    empSalary As Currency)
    With emp
        .ID = SetEmpId
        .LastName = empLast
        .FirstName = empFirst
        .Salary = CCur(empSalary)
        If .Salary = 0 Then Exit Sub
        CEmployee.Add emp
    End With
End Sub

Function SetEmpId() As String
    Dim ref As String
    
    Randomize
    ref = Int((99999 - 10000) * Rnd + 10000)
    SetEmpId = ref
End Function

Function GetValues()
    Dim myList As String
    
    myList = ""
    For Each emp In CEmployee
        myList = myList & emp.ID & ";" & _
            emp.LastName & ";" & _
            emp.FirstName & "; $" & _
            Format(emp.Salary, "0.00") & ";"
    Next emp
    GetValues = myList
End Function

Sub UpdateSalary(choice As Integer, myValue As Long, _
    peopleCount As Integer, colItem As Integer)
    Set emp = New CEmployee
    
    If choice = 1 And peopleCount = 1 Then
        CEmployee.Item(colItem).Salary = _
            emp.CalcNewSalary(1, CEmployee.Item(colItem).Salary, myValue)
    ElseIf choice = 1 And peopleCount = 2 Then
        For Each emp In CEmployee
            emp.Salary = emp.Salary + ((emp.Salary * myValue) / 100)
        Next emp
    ElseIf choice = 2 And peopleCount = 1 Then
        CEmployee.Item(colItem).Salary = _
            CEmployee.Item(colItem).Salary + myValue
    ElseIf choice = 2 And peopleCount = 2 Then
        For Each emp In CEmployee
            emp.Salary = emp.Salary + myValue
        Next emp
    Else
        MsgBox "Enter data or select an option."
    End If
End Sub

Sub DeleteEmployee(colItem As Integer)
    Dim getcount As Integer

    CEmployee.Remove colItem
End Sub


' ----------------------------------------------------------------
' Custom Project 8-1 (Step 9 - no code in this step)
' Please follow the instructions in the book.
' ----------------------------------------------------------------


' ----------------------------------------------------------------
' Custom Project 8-1 (Step 10 - no code in this step)
' Please follow the instructions in the book.
' ----------------------------------------------------------------

