Collections and Class Hierarchies

[VanceT]

I am relatively new to working in VBA in depth, and I am working on a
personal "hobby" project for a note card database (to replace the classic 3x5
note card). Incidentally, I'm using Access 2003.

I am trying to write the code for creating a class for the notes (clsNote)
that has a subclass of comments associated with it (clsComment). The object
model would be something like this:
Note
Comments
Comment

In the "General Declarations" section of clsNote, I have the following line:

Public Comment As clsComment

I have also set a small number of properties for each of the classes (e.g.,
ID, Created, Modified, etc.)

My problem begins when I try to create the objects. I can create the new
note object just fine, but creating a new comment for this is completely
escaping me.

I have a test subroutine that I have been using to figure out how to code
this. Here are the relevant lines of code that I have been using:

Public Sub Test()

Dim objNote As clsNote
Dim objComment As clsComment

Set objNote = New clsNote
Set objComment = New clsComment

With objNote
.ID = 1
.Created = #1/2/2003#
.Body = "This is the body of the note."
End With

With objComment
.ID = 2
.Created = Now()
.Body = "This is the body of the comment."
End With

Debug.Print objNote.ID
Debug.Print objNote.Created
Debug.Print objNote.Body

Debug.Print objNote.Comment.ID
Debug.Print objNote.Comment.Created
Debug.Print objNote.Comment.Body

End Sub

Any suggestions? Where am I making a mistake? Any help or direction would
be greatly appreciated.

Thanks.

VMT

 

[Dirk Goldgar]

In

I am relatively new to working in VBA in depth, and I am working on a
personal "hobby" project for a note card database (to replace the
classic 3x5 note card). Incidentally, I'm using Access 2003.

I am trying to write the code for creating a class for the notes
(clsNote) that has a subclass of comments associated with it
(clsComment). The object model would be something like this:
Note
Comments
Comment

In the "General Declarations" section of clsNote, I have the
following line:

Public Comment As clsComment

I have also set a small number of properties for each of the classes
(e.g., ID, Created, Modified, etc.)

My problem begins when I try to create the objects. I can create the
new note object just fine, but creating a new comment for this is
completely escaping me.

I have a test subroutine that I have been using to figure out how to
code this. Here are the relevant lines of code that I have been
using:

Public Sub Test()

Dim objNote As clsNote
Dim objComment As clsComment

Set objNote = New clsNote
Set objComment = New clsComment

With objNote
.ID = 1
.Created = #1/2/2003#
.Body = "This is the body of the note."
End With

With objComment
.ID = 2
.Created = Now()
.Body = "This is the body of the comment."
End With

Debug.Print objNote.ID
Debug.Print objNote.Created
Debug.Print objNote.Body

Debug.Print objNote.Comment.ID
Debug.Print objNote.Comment.Created
Debug.Print objNote.Comment.Body

End Sub

Any suggestions? Where am I making a mistake? Any help or direction
would be greatly appreciated.

Thanks.

VMT

I don't see anything that sets a Comment property of objNote to the
clsComment object you created. Assuming that clsNote has a Comment
property, I'd expect there to be a line:

Set objNote.Comment = objComment

Of course, that ignores the possibility that clsNote actually has a
Comments *collection*, in which case you'd need something along the
lines of

objNote.Comments.Add objComment

and then your simple code to print objNote.Comment would have to be
modified to loop through the members of objNote.Comments and print each
of them..

 

[VanceT]

Thanks for your quick reply Dirk.

I see that my earlier post was a little confused. My intention is to create
a *collection* of comments for each of the clsNote objects. I have tried to
find information on how to set this up but I am not having much luck.

I guess my questions are this:

How do I add a collection to a custom class? What procedures, declarations,
etc. are needed in the parent class module for this? What needs to be in the
child class, if anything?

How do I go about creating this new child object? I expect the answer here
is to use the "Add" method for the collection. Is there anything else I need
to know about working with these child objects?

Thanks again for the help.

VMT

 

[Dirk Goldgar]

In

Thanks for your quick reply Dirk.

I see that my earlier post was a little confused. My intention is to
create a *collection* of comments for each of the clsNote objects. I
have tried to find information on how to set this up but I am not
having much luck.

I guess my questions are this:

How do I add a collection to a custom class? What procedures,
declarations, etc. are needed in the parent class module for this?
What needs to be in the child class, if anything?

How do I go about creating this new child object? I expect the
answer here is to use the "Add" method for the collection. Is there
anything else I need to know about working with these child objects?

This isn't my strongest suit, but here's some rudimentary code and a
test routine:

'------ start of code for clsComment ------
Option Compare Database
Option Explicit

Dim mID As Variant
Dim mCreated As Variant
Dim mBody As Variant

Public Property Let ID(TheValue As Variant)

mID = CLng(TheValue)

End Property

Public Property Get ID() As Variant

ID = mID

End Property

Public Property Let Created(TheValue As Variant)

mCreated = CDate(TheValue)

End Property

Public Property Get Created() As Variant

Created = mCreated

End Property

Public Property Let Body(TheValue As Variant)

mBody = CStr(TheValue)

End Property

Public Property Get Body() As Variant

Body = mBody

End Property

Private Sub Class_Initialize()

mID = Null
mCreated = Null
mBody = Null

End Sub

'------ end of code for clsComment ------

'------ start of code for clsNote ------
Option Compare Database
Option Explicit

Dim mID As Variant
Dim mCreated As Variant
Dim mBody As Variant
Dim mComments As Collection

Public Property Let ID(TheValue As Variant)

mID = CLng(TheValue)

End Property

Public Property Get ID() As Variant

ID = mID

End Property

Public Property Let Created(TheValue As Variant)

mCreated = CDate(TheValue)

End Property

Public Property Get Created() As Variant

Created = mCreated

End Property

Public Property Let Body(TheValue As Variant)

mBody = CStr(TheValue)

End Property

Public Property Get Body() As Variant

Body = mBody

End Property

Public Property Get Comments() As Collection

Set Comments = mComments

End Property

Public Function AddComment( _
cID As Long, _
cCreated As Date, _
cBody As String) _
As clsComment

Dim objComment As clsComment

Set objComment = New clsComment

With objComment
.ID = cID
.Created = cCreated
.Body = cBody
End With

mComments.Add objComment, CStr(objComment.ID)

Set AddComment = objComment

End Function

Private Sub Class_Initialize()

mID = Null
mCreated = Null
mBody = Null
Set mComments = New Collection

End Sub

Private Sub Class_Terminate()

Set mComments = Nothing

End Sub
'------ end of code for clsNote ------

'------ start of test code ------
Sub TestNotes()

Dim objNote As clsNote
Dim c As clsComment

Set objNote = New clsNote

objNote.ID = 1
objNote.Created = Now()
objNote.Body = "This is the body of my note."

objNote.AddComment 1, Now(), "This my first comment."
objNote.AddComment 2, Now() + 1, "This my second comment."
objNote.AddComment 3, Now() + 3, "This my third comment."

Debug.Print "Note: " & objNote.ID, objNote.Created, objNote.Body

For Each c In objNote.Comments
Debug.Print "Comment: " & c.ID, c.Created, c.Body
Next c

End Sub
'------ end of test code ------

 

[VanceT]

This is perfect!

Thanks for the help, Dirk. This is just what I needed.

VMT