Hi everyone,
I am looking for some advice on a problem experienced in my code. Firstly I would like to say that this code in its original format was lifted from another site (name of which I cannot recall) and based on the methodology (and VBA style of Java inheritance/extends of OOP), I figured I could expand it to meet my own requirements.
My own requirements being:
Create a class (I am calling Calendar 1st tier) which holds a collection of sub-classes (I am calling Machines 2nd tier) - this part works fine.
Then, within each (Machines 2nd tier) sub-class, use this sub-class to hold another collection of sub-classes (I am calling Employees 3rd tier) - this part falls over.
I have commented the code to show where the error manifests - it enters the Machine sub-class and fails to add the Employee class to its collection. What is strange is the code line that fails in the Machine Class is a replica of the Calendar class.
Once (or if) the code works, I will then create processes to extract data from database and populate. This is me just testing the methodology beforehand.
Any help or guidance is much appreciated. (Even if it is that VBA cannot support this type of class-in-class process - perhaps Java is best suited to OOP but for now I wish to test this in VBA)
Code as follows:
MODULE1
Code:
Option Compare Database
Public Sub calendarTest()
' // Attributes
Dim i As Long
Dim j As Long
Dim k As Long
' // Attribute - objects
' // 1ST TIER
Dim calendarList As Collection
Dim calendar As classCalendar
' // 2ND TIER
Dim machineList As Collection
Dim machine As classMachine
' // 3RD TIER
Dim employeeList As Collection
Dim employee As classEmployee
' // DATA TEST
Debug.Print "~~~ STORING DATA ~~~"
' // 1ST TIER
Set calendarList = New Collection
For i = 1 To 3
' // 2ND TIER
Set calendar = New classCalendar
calendar.DatePeriod = i
Debug.Print "~ Allocating machines for Calendar Period " & calendar.DatePeriod
Set machineList = New Collection
For j = 1 To 3
Set machine = New classMachine
machine.Name = "WC " & j
machineList.Add machine
' // 3RD TIER
Set employeeList = New Collection
For k = 1 To 2
Set employee = New classEmployee
employee.Name = "Operator " & k
Debug.Print "** Allocating employee for machine " & machine.Name
employeeList.Add employee
Next k
' // Add tier 3 to tier 2
Debug.Print "*** On Calendar Period " & calendar.DatePeriod & ", machine " & machine.Name & " has EmployeeList count of " & machine.employees.Count
' // *******************************************
' // *******************************************
' // Error occurs within this procedure call
Set machine.employeeAdd = employeeList
' // *******************************************
' // Comment this command out and the process works
' // although it will not have saved any employee data
' // *******************************************
Next j
' // Clear object
Set employeeList = Nothing
' // Add tier 2 to tier 1
Set calendar.machineAdd = machineList
Debug.Print "* On Calendar Period " & calendar.DatePeriod & " there are " & calendar.machines.Count & " machines allocated"
' // Add tier 1
calendarList.Add calendar
Next i
' // Clear object
Set machineList = Nothing
' // Write the data back out again
Debug.Print ' // Blank line
Debug.Print "~~~ RETRIEVING DATA ~~~"
For Each calendar In calendarList
Debug.Print "Calendar Date Period " & calendar.DatePeriod
Set machineList = calendar.machines
For Each machine In machineList
Debug.Print "* Machine: " & machine.Name
Set employeeList = machine.employees
For Each employee In employeeList
Debug.Print " ** Machine + Employee: " & machine.Name, employee.Name
Next
Next
Next
Set calendarList = Nothing
Set machineList = Nothing
Set employeeList = Nothing
End Sub
classCALENDAR
Code:
Option Compare Database
Private pDatePeriod As String
Private pmachineList As Collection
Private Sub Class_Initialize()
Set pmachineList = New Collection
End Sub
Public Property Get DatePeriod() As String
DatePeriod = pDatePeriod
End Property
Public Property Let DatePeriod(ByVal sDatePeriod As String)
pDatePeriod = sDatePeriod
End Property
Public Property Get machines() As Collection
Set machines = pmachineList
End Property
Public Property Set machineAdd(objCollection As Collection)
For Each machineID In objCollection
pmachineList.Add machineID
Next
End Property
classMACHINE
Code:
Option Compare Database
Private pmachineName As String
Private pemployeeList As Collection
Private Sub Class_Initialize()
Set pemployeeList = New Collection
End Sub
Public Property Get Name() As String
Name = pmachineName
End Property
Public Property Let Name(ByVal sName As String)
pmachineName = sName
End Property
Public Property Get employees() As Collection
Set employees = pemployeeList
End Property
Public Property Set employeeAdd(objCollection As Collection)
For Each EmployeeName In objCollection
pemployeesList.Add EmployeeName
Next
End Property
classEMPLOYEE
Code:
Option Compare Database
Private pemployeeName As String
Public Property Get Name() As String
Name = pemployeeName
End Property
Public Property Let Name(ByVal sName As String)
pemployeeName = sName
End Property
Private Sub Class_Initialize()
'Debug.Print TypeName(Me) & ": new employee created..."
End Sub