Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Custom Classes - Heirarchy

Posted on 2011-03-15
8
Medium Priority
?
922 Views
Last Modified: 2013-11-27
I'm trying to implement custom Class Modules in Microsoft Access 2003 (VBA).

I would like to build my classes such that they have what I would call "heirarchy" e.g.:
   Staff.RegionalManager.Name

I have built the "Staff" level (clsStaff) and the "Regional Manager" level (clsRM) with a Property Get for ".Name".  
I'm having unbelievable difficulty in making them line up behind the periods embedding one
inside the next.   More than that, I can't seem to find example code online.  
I don't know what that method of class definition is called to drive Intellisense to prompt
 for the next level.

I've tried searching on:

Implements / Inherits
Base Class
Parent
Subclassing
Heirarchy
Class levels

I think if I knew what to call it, I could follow online examples posted by others.
How do you describe what I see in VBA all the time with Automation?   Multiple levels
of classes period after period after period:

Excel.Application.xxx.yyyy.value

I am not trying to create these levels with Custom Collections.  They are single pieces of info
with increasing specificity at each level.

Sorry.  I know my question is inelegant, but I'm pretty good with VBA and this one has me stumped because I can't even look up an example.
0
Comment
Question by:cfetter2
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:ErezMor
ID: 35141226
there is no mechanism to make this "effect". an object will ONLY show it's public properties behind a dot/period.
and therefore, one way to go is create the "child" object as a public variable inside the parent object.
another, simpler way, since you said it's all "flat" with no collections involved, you can create user defined types instead of classes, they look the way you're after and they are easier to create/manage
0
 
LVL 58
ID: 35141342
<<I think if I knew what to call it, I could follow online examples posted by others.
How do you describe what I see in VBA all the time with Automation?>>

  Can't do it.    VBA is an object based language, not object orientated one.

  What your seeing it not a class heirarchy at work, but simply objects.

JimD.

0
 

Author Comment

by:cfetter2
ID: 35141530
For anyone who stumbles across this in the future...

Found it.   I guess asking the question prompted me to think of the term "nesting"...  Not sure why I couldn't think of that over that past week and a half.

This gives me "nested" classes without any Collections.   I can display the .Name property (hard coded for this example) two levels deep.

(I removed extraneous code for clarity)

Fired by a button click on my Form:

 
Private Sub cmdTestRM_Click()
    Dim Staff As clsStaff
    
    Set Staff = New clsStaff
    
    MsgBox Staff.TerritoryRM.Name
End Sub

Open in new window


In clsRM:

 
Option Compare Database
Option Explicit

'**********
' Public Properties
'**********

'**********
' Private Variables
'**********
Private pName As String

'**********
' Initialize / Terminate
'**********

Private Sub Class_Initialize()
    pName = "Dan Molloy"
End Sub

'---------------------
' PROPERTIES

'**********
' Name Property
'**********
Property Get Name() As String
    Name = pName
End Property
Property Let Name(pstrNewValue As String)
    pName = pstrNewValue
End Property

'----------------------
' METHODS

'----------------------
' PRIVATE PROC / FUNCTIONS

Open in new window


In clsStaff:

 
Option Compare Database
Option Explicit

'**********
' Public Properties
'**********

'**********
' Private Variables
'**********
Private pobjRM As New clsRM

'**********
' Initialize / Terminate
'**********

'---------------------
' PROPERTIES
'**********
' TerritoryRM Property
'**********
Public Property Get TerritoryRM() As clsRM
    Set TerritoryRM = pobjRM
End Property
Public Property Let TerritoryRM(ByVal objNewValue As clsRM)
    Set pobjRM = objNewValue
End Property

'----------------------
' METHODS

'----------------------
' PRIVATE PROC / FUNCTIONS

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 58
ID: 35141664
<<This gives me "nested" classes without any Collections.   >>

  You nesting objects, not classes.  What you have is an object pointer.

 JimD.
0
 

Author Comment

by:cfetter2
ID: 35142295
Jim,

I'm putting these in "Class Modules", not "Modules" or "Forms" of MSAccess.   In these Class Modules, I'm defining Let and Get Properties as well as Methods (Subs and Functions).  I redacted a bunch of the code for simplicity in the example I posted.  

To my understanding, Let and Get cannot be replicated anywhere outside of Class Modules in VBA.

Intellisense doesn't just allow me the Parameters of the Subs and Functions, but exposes all the Properties and Methods.  I'm not being belligerent in any way...    What am I missing?   How are these not Classes?

Thanks for the insight,

Chris
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 35142506
Chris,

<<What am I missing?   How are these not Classes>>

  Yes they are classes, but they don't inherit from one another.  When you speak of a class heirarchy, base classes, sub-classing, your talking about one class that is built on top of another.  That cannot be done in VBA.  That's why your searches turned up nothing.

  What your doing here is creating objects with these classes, then associating the objects with each other through a property of the class.  This:

Public Property Let TerritoryRM(ByVal objNewValue As clsRM)
    Set pobjRM = objNewValue
End Property

   is accepting a pointer to an object (an instance of the clsRM) and storing it as a property.  This would be a "parent" property.

   What your doing is creating an object heirarchy, not a class one.

JimD.


0
 

Author Comment

by:cfetter2
ID: 35142600
Great info.   Thanks so much.
0
 
LVL 58
ID: 35142619
<<Great info.   Thanks so much>>

 No problem.

JimD.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question