Results 1 to 7 of 7
  1. #1
    jc1104 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Aug 2015
    Posts
    4

    Question Classes with sub-classes and sub-classes with sub-classes

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Theres no point in building all these sub classes. Access uses the tables, linked to other tables (sub tables)
    Theres no need to build all this code when the TABLE is the CLASS.
    pull your data from a query, the collection is the table.

  3. #3
    jc1104 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Aug 2015
    Posts
    4
    Thanks Ranman for your reply. Yeah, agree with your comments, and will be a lot easier to do using your method.
    I was originally trying this in Excel VBA and moved into Access because I recognized I was effectively building tables and relationships. I thought I would try something basic excluding Access tables for now and to begin using classes in VBA.

    I agree there is no point in this example "in building all these sub classes" as your suggestion is the best solution to "solve" the problem.

    I do also wish to satisfy my understanding of the problem and my inner curiosity (+ develop my problem solving with regards to coding) and am still wondering as to why the code doesn't work as I would expect.
    Any insight as to why this error occurs would be most helpful.

    Thanks again

  4. #4
    jc1104 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Aug 2015
    Posts
    4

    Question Solved!

    SOLVED!
    After taking a break from looking at it and debugging line by line, then returning to the code, I have noticed a school-boy error in the code.

    My code:
    Code:
        For Each EmployeeName In objCollection        pemployeesList.Add EmployeeName
        Next
    didn't match my variable declaration pemployeeList (without the plural 's' in employee)

    Corrected EMPLOYEE class is:
    Code:
    Public Property Set employeeAdd(objCollection As Collection)
    
    
        For Each EmployeeName In objCollection
            pemployeeList.Add EmployeeName
        Next
    
    
    End Property
    Thanks to everyone who had a look at the problem.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It took me a while too because it was throwing the error in the standard module. That's the way Classes are in Access. I did not notice the typo until I started using the watch window.

    BTW, is this a school assignment? Pretty cool example you have there.

  6. #6
    jc1104 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Aug 2015
    Posts
    4
    I was looking at it too much. Couldn't see the wood from the trees as it were.

    School is a very distant memory for me, 20 years ago now. The term school-boy error I referred to was me chastising myself for making an error as if I were a school-boy learning. (And we are always learning...)

    I use Access at work and have built many queries (some simple, some quite complex union and cross-tabs), I do a bit of programming in VBA for work purposes and have started to re-visit my Java training from about 8 years ago and trying to get back into things. I don't have Java Development software so for now I thought I would develop on an existing Access and Excel VBA projects by setting up some classes. I do think Java will be easier with proper object inheritance but for now I am restricted to VBA.

    I prefer to use real-life examples to work with and generally my problem solving is decent (although I just couldn't see this issue when I posted).
    Ultimately I wish to fully port into Java and use the methodology to create a simple nested class program to display a graphical version of the debug printouts my code produces.
    The example was really just the building blocks of understanding nested classes in VBA and in my process flowchart it should have worked.

    Thanks for the compliment of a cool example, maybe it is because it reflects real life requirement and I tried to display it as simple as possible for all to understand what was going on.
    Hopefully come in handy for others who are attempting something similar.

    Ranman's solution earlier is better by all accounts, however I wanted to discover why my nested classes method wasn't working.
    (Not sure which method has the lowest overheads, probably Ranman's again, but this isn't a concern for me at the moment for what I was trying to achieve.)

    Thanks.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    OK, I was kinda hoping it was a school assignment. Often, students post questions here when they get stuck. Sometimes the assignments are difficult to relate to and not because a specific assignment is, simply, an abstract idea.

    I like your example because I believe it is a great example of how to use VBA to create a Class Factory Method. Perhaps it has a place in the code examples forum here.

    I am sure you are happy with your current IDE that you use for Java but I thought I would mention that Visual Studio Community is the newest free edition of Visual Studio. It would be useful if you are interested in .NET frameworks. Also, Java is now a first class citizen. Just be aware that 'Visual Studio Online' is a misnomer and 'Community' is the free version. 'Online' is for teams to collaborate online, it is not an online version of your IDE (basically source control). Also, it is easy to get overwhelmed by all of the different technologies that you can integrate with Visual Studio. So, if you do look at it, try to keep your eye on the ball. For instance, it is easy to get distracted by something like XAML when XAML may not be what you need.

    https://www.visualstudio.com/product...o-community-vs


    .

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 12-10-2014, 02:36 PM
  2. Trying not to Schedule Classes around Lunch Time
    By boywonder381 in forum Programming
    Replies: 3
    Last Post: 09-12-2014, 03:43 AM
  3. Classes not taken
    By sdc1234 in forum Queries
    Replies: 1
    Last Post: 02-20-2014, 02:16 PM
  4. Replies: 0
    Last Post: 01-30-2012, 01:14 PM
  5. what does this mean: [classes]![cDate]
    By rankhornjp in forum Programming
    Replies: 6
    Last Post: 11-09-2011, 02:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums