Hi all! I have created two class modules: Book and Bookshelf. The last one
is implemented as a collection of items of the first one. I have included
the function NewEnum() in the Bookshelf class, so I can iterate through the
elements of the collections using a "For Each" loop. The problem is that
code below comes up with a runtime error '438': "Object doesn't support this
property or method." However this code does work under VB6. What's wrong?!
How can I make it to work under Access XP? The only way I can get it to work
is using the second code "FormV2_Load()"

'This version does not work
Private Sub Form_Load()

Dim MyBookshelf As Bookshelf
Dim Item As Book

Me.List1.RowSourceType = "Value List"

Set MyBookshelf = New Bookshelf

For Each Item In MyBookshelf
Me.List1.AddItem Item.Title & vbTab & Item.ISBN
Next Item

Set Item = Nothing
Set MyBookshelf = Nothing

End Sub

'This version does work
Private Sub FormV2_Load()

Dim MyBookshelf As Bookshelf
Dim Item As Book
Dim c As Long

Me.List1.RowSourceType = "Value List"

Set MyBookshelf = New Bookshelf

For c = 1 To MyBookshelf.Count
Set Item = MyBookshelf.Item(c)
Me.List1.AddItem Item.Title & vbTab & Item.Author & vbTab &
Item.ISBN
Next

Set Item = Nothing
Set MyBookshelf = Nothing

End Sub

Below is the code I included inside Bookshelf to make sure I can use the
"For Each .. Next". This is the collection object enumerator, so it can know
how to iterate through the items in the collection:

' NewEnum must return the IUnknown interface of a
' collection's enumerator.
Public Function NewEnum() As IUnknown
Set NewEnum = mcolEmployees.[_NewEnum]
End Function

I appreciate it your assistance. Thanks!

VBA is not VB6! This might help:

http://p2p.wrox.com/topic.asp?TOPIC_ID=26259

To do it in a more "VBA" kind of a way, have the Books collection as a
public member of the Bookshelf class.

"Nando" <(E-Mail Removed)> wrote in message
newsb9ik.261869$(E-Mail Removed)...
> Hi all! I have created two class modules: Book and Bookshelf. The last one
> is implemented as a collection of items of the first one. I have included
> the function NewEnum() in the Bookshelf class, so I can iterate through
> the elements of the collections using a "For Each" loop. The problem is
> that code below comes up with a runtime error '438': "Object doesn't
> support this property or method." However this code does work under VB6.
> What's wrong?! How can I make it to work under Access XP? The only way I
> can get it to work is using the second code "FormV2_Load()"
>
> 'This version does not work
> Private Sub Form_Load()
>
> Dim MyBookshelf As Bookshelf
> Dim Item As Book
>
> Me.List1.RowSourceType = "Value List"
>
> Set MyBookshelf = New Bookshelf
>
> For Each Item In MyBookshelf
> Me.List1.AddItem Item.Title & vbTab & Item.ISBN
> Next Item
>
> Set Item = Nothing
> Set MyBookshelf = Nothing
>
> End Sub
>
> 'This version does work
> Private Sub FormV2_Load()
>
> Dim MyBookshelf As Bookshelf
> Dim Item As Book
> Dim c As Long
>
> Me.List1.RowSourceType = "Value List"
>
> Set MyBookshelf = New Bookshelf
>
> For c = 1 To MyBookshelf.Count
> Set Item = MyBookshelf.Item(c)
> Me.List1.AddItem Item.Title & vbTab & Item.Author & vbTab &
> Item.ISBN
> Next
>
> Set Item = Nothing
> Set MyBookshelf = Nothing
>
> End Sub
>
> Below is the code I included inside Bookshelf to make sure I can use the
> "For Each .. Next". This is the collection object enumerator, so it can
> know how to iterate through the items in the collection:
>
> ' NewEnum must return the IUnknown interface of a
> ' collection's enumerator.
> Public Function NewEnum() As IUnknown
> Set NewEnum = mcolEmployees.[_NewEnum]
> End Function
>
> I appreciate it your assistance. Thanks!
>

Thanks bcap! That did it! I'm so happy now. This is a summary of the steps
for any one reading out here:

1) Create your class then export it as text.
2) Delete the class from the project.
3) Edit the text file to implement the procedure attributes, inserting the
line "Attribute NewEnum.VB_UserMemId = -4" right below the function header
"NewEnum()."
4) Re-import the class.

Also I have been playing around a lot and I found it is lot easier to just
use the Class Builder utility under VB6, and import the .CLS files into
Access. Cool stuff! Thanks bcap!

"bcap" wrote:
> VBA is not VB6! This might help:
>
> http://p2p.wrox.com/topic.asp?TOPIC_ID=26259
>
> To do it in a more "VBA" kind of a way, have the Books collection as a
> public member of the Bookshelf class.

"bcap" wrote:

> To do it in a more "VBA" kind of a way, have the Books collection as a
> public member of the Bookshelf class.

How will that look like? I cannot picture it and I'm curious. This is a
sample of the interaction through code:


Dim MyBookshelf As Bookshelf
Dim Item As Book

Me.List1.RowSourceType = "Value List"

Set MyBookshelf = New Bookshelf

For Each Item In MyBookshelf
Me.List1.AddItem Item.Title & vbTab & Item.ISBN
Next Item

Set Item = Nothing
Set MyBookshelf = Nothing

"Nando" wrote:

> Thanks bcap! That did it! I'm so happy now. This is a summary of the steps
> for any one reading out here:
>
> 1) Create your class then export it as text.
> 2) Delete the class from the project.
> 3) Edit the text file to implement the procedure attributes, inserting the
> line "Attribute NewEnum.VB_UserMemId = -4" right below the function header
> "NewEnum()."
> 4) Re-import the class.
>
> Also I have been playing around a lot and I found it is lot easier to just
> use the Class Builder utility under VB6, and import the .CLS files into
> Access. Cool stuff! Thanks bcap!

As a note: I just found out that if you cut and paste the procedures, during
code editing, these member attributes may be lost, so the importing will
have to be done again.

Dim MyBookshelf As Bookshelf
Dim Item As Book

Me.List1.RowSourceType = "Value List"

Set MyBookshelf = New Bookshelf

For Each Item In MyBookshelf.Books
Me.List1.AddItem Item.Title & vbTab & Item.ISBN
Next Item

Set Item = Nothing
Set MyBookshelf = Nothing


"Nando" <(E-Mail Removed)> wrote in message
news:Mruik.134926$(E-Mail Removed)...
> "bcap" wrote:
>
>> To do it in a more "VBA" kind of a way, have the Books collection as a
>> public member of the Bookshelf class.
>
> How will that look like? I cannot picture it and I'm curious. This is a
> sample of the interaction through code:
>
>
> Dim MyBookshelf As Bookshelf
> Dim Item As Book
>
> Me.List1.RowSourceType = "Value List"
>
> Set MyBookshelf = New Bookshelf
>
> For Each Item In MyBookshelf
> Me.List1.AddItem Item.Title & vbTab & Item.ISBN
> Next Item
>
> Set Item = Nothing
> Set MyBookshelf = Nothing
>
>

"bcap" wrote:

> "Nando" <(E-Mail Removed)> wrote in message
> news:Mruik.134926$(E-Mail Removed)...
>> "bcap" wrote:
>>
>>> To do it in a more "VBA" kind of a way, have the Books collection as a
>>> public member of the Bookshelf class.
>>
>> How will that look like? I cannot picture it and I'm curious.

OK, following your recommendation I had to redesign my object oriented
model, so item iteraction makes more sense. I learned a lot on the way and
hopefully I got it right now.

Book 'Class
Title As String
Author As String
ISBN As String

Books 'Class/Collection
Item As Book
Count As Long
Add()
Remove()
NewEnum()

Bookshelf 'Class
Tag As String
Location As String
Books As Books

'Base on this Object Model I was playing to do this iteraction: (Great it
works!)

Private Sub Form_Load()

Dim MyBookshelf As Bookshelf
Dim Item As Book

Set MyBookshelf = New Bookshelf

With MyBookshelf

'add some test
.Tag = "A-D"
.Location = "South window"

.Books.Add "Learn VB in 5 minutes!", "LL. Liar", "1111111111"
.Books.Add "Mastering Access 2009", "MS Press", "2222222222"
.Books.Add "How to buy the best PC", "M. Dell", "3333333333"

'Print the books
For Each Item In MyBookshelf.Books
Debug.Print Item.Title & vbTab & Item.Author & vbTab & Item.ISBN
Next Item
Set Item = Nothing

End With

Set MyBookshelf = Nothing

End Sub

Terrific, looks good to me!

"Nando" <(E-Mail Removed)> wrote in message
news:XIMik.266679$(E-Mail Removed)...
> "bcap" wrote:
>
>> "Nando" <(E-Mail Removed)> wrote in message
>> news:Mruik.134926$(E-Mail Removed)...
>>> "bcap" wrote:
>>>
>>>> To do it in a more "VBA" kind of a way, have the Books collection as a
>>>> public member of the Bookshelf class.
>>>
>>> How will that look like? I cannot picture it and I'm curious.
>
> OK, following your recommendation I had to redesign my object oriented
> model, so item iteraction makes more sense. I learned a lot on the way and
> hopefully I got it right now.
>
> Book 'Class
> Title As String
> Author As String
> ISBN As String
>
> Books 'Class/Collection
> Item As Book
> Count As Long
> Add()
> Remove()
> NewEnum()
>
> Bookshelf 'Class
> Tag As String
> Location As String
> Books As Books
>
> 'Base on this Object Model I was playing to do this iteraction: (Great it
> works!)
>
> Private Sub Form_Load()
>
> Dim MyBookshelf As Bookshelf
> Dim Item As Book
>
> Set MyBookshelf = New Bookshelf
>
> With MyBookshelf
>
> 'add some test
> .Tag = "A-D"
> .Location = "South window"
>
> .Books.Add "Learn VB in 5 minutes!", "LL. Liar", "1111111111"
> .Books.Add "Mastering Access 2009", "MS Press", "2222222222"
> .Books.Add "How to buy the best PC", "M. Dell", "3333333333"
>
> 'Print the books
> For Each Item In MyBookshelf.Books
> Debug.Print Item.Title & vbTab & Item.Author & vbTab &
> Item.ISBN
> Next Item
> Set Item = Nothing
>
> End With
>
> Set MyBookshelf = Nothing
>
> End Sub
>
>

"bcap" wrote:
> Terrific, looks good to me!
>
>>>>> To do it in a more "VBA" kind of a way, have the Books collection as
>>>>> a public member of the Bookshelf class.
>>>>

Thanks a million!