object invalid or no longer set 1

[1DMF]

I can't work out why I'm getting that error for this code

    Dim vEmail As Variant
    For Each vEmail In oAEmails.Accounts(oContact.ARNo)
        Debug.Print vEmail   [b]<--- erroring here[/b]
    Next

the object oAEmails is as follows...

Option Compare Database
Option Explicit

Private rs As DAO.Recordset

public Property Get Accounts(ByVal sARNO As String) As Collection
    
    ' additional accounts emails
    Set Accounts = New Collection
    
    Set rs = CurrentDb.OpenRecordset("SELECT Email FROM Accounts_Emails WHERE AR_No = '" & sARNO & "'")
    
    Do While Not rs.EOF
        Accounts.Add rs.Fields("Email")
        rs.MoveNext
    Loop

    Set rs = Nothing
    
End Property

I get one record / email address as expected, I then seem unable to access it?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[SkipVought]

faq707-4594

Add a Watch on vEmail and see if its an OBJECT.

You might need to specify which property, like [highlight #FCE94F]vEmail.Value[/highlight].

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[1DMF]

I did actually already try '.value', but get the same error?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

... and normally when you only add a single argument to a collection and then loop it, you usualy only need the return.

ie..

Dim cMyCol As  New Collection
Dim vVal As Variant
cMyCol.Add "One"
cMyCol.Add "Two"
cMyCol.Add "Three"

For Each vVal In cMyCol
Debug.Print vVal
Next

You should get an output...

One
Two
Three

So what's changed?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[PHV]

And this ?

For Each vEmail In oAEmails.Accounts
    Debug.Print vEmail
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

Well, that just won't work. .Accounts is a parameterised GET function. No parameter, and it'll fail; you'll get an 'Argument not optional' compile error

 

[strongm]

By the way, this is one of the reasons that Microsoft have removed default properties in .net

The line that is actually causing the problem is:

Accounts.Add rs.Fields("Email")


You THINK that you are adding the contents of that field (i.e. .value, since that is the default property), but you are actually just adding a reference (pointer) to the recordset field. Normally not a problem, since referring to it should pull up the default property (twice in fact when working through a collection object, since .Value is the default property of a collection, and then .Value is the default property for the retrieved recordset field. However, since rs is a) Private to the class, it is not visible to the outside world, and b) gets set to Nothing before exiting, that is what causes the error that you are seeing (There's an additional error, masked by the main problem here, caused by the .MoveNext since this actually eventually changes what the pointer is pointing at to EOF)

(both) Easily fixed by:

Accounts.Add rs.Fields("Email").Value

 

[1DMF]

Simply, when you know why!

Thanks Mike, appreciated.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music