Passing collection objs as parameters

[Guest]

I am having problems passing a collection object to other class modules.
First, I define the collection object in a separate class module (i.e. Dim
foo As Collection). Next, I create an instance of the object (i.e., Set foo
= new Collection) in the “Private Sub Form_Load()†routine of my Form
Module.

Next I pass the 'foo' object to a standard class module (i.e.,
clsFormManager – Public Sub getTableItems). The getTableItems sub passes the
'foo' object to another class module (clsDBManager – Public Sub readDBTable).
The readDBTable routine opens the database and adds items to the 'foo'
collection object and returns.

When I return to the getTableItems routine and try to access the data from
the 'foo' collection, I get the following run time error: “Run-Time Error
‘3420 – Object is no longer validâ€

All the variables are passed by references. Can anyone help with the bug?

Thanks,

Eddie Eytchison

 

[Jeff Boyce]

Eddie

You've done a very good job of describing, in strong technical detail, how
you are trying to satisfy a requirement.

Unfortunately, I have no idea what the underlying requirement might be. I
raise this point because you might receive other suggested approaches to
doing what needs to be done, or confirmation that the "collections" approach
you've taken is the most appropriate solution ...

 

[Guest]

Jeff thanks for taking my question. My goal is to read and filtered data
from a database and save it for later use. Unfortunately, I am new to MS
Access and Databases in general. I am developing my application without any
good references or experience.

Since I have a MS Degree in Computer Science and know how to program, I am
developing the entire application in VBA (In college I never took any DB
classes, too bad)

Fortunately, I was able to solve the problem. I isolated the problem by
commenting-out most the code and focusing on the areas where the “Containerâ€
is filled and accessed.

The container was filled with values from a database table. The table
contains three fields, two “number†fields (primary/foreign keys) and “textâ€
field (i.e. name). The error occurred, when I tried to read the container
values after filling it with “number†and “text†data from the database
table. To fix the problem the “number†values had to be cast to Integers
using the CInt() function and the “text†values had to be cast to Strings
using the CStr() function.

I don’t know why this fixed the problem, but it did. I am open to any
suggestions.

Thanks,

Eddie

 

[Dirk Goldgar]

Jeff thanks for taking my question. My goal is to read and filtered
data from a database and save it for later use. Unfortunately, I am
new to MS Access and Databases in general. I am developing my
application without any good references or experience.

Since I have a MS Degree in Computer Science and know how to program,
I am developing the entire application in VBA (In college I never
took any DB classes, too bad)

Fortunately, I was able to solve the problem. I isolated the problem
by commenting-out most the code and focusing on the areas where the
"Container" is filled and accessed.

The container was filled with values from a database table. The table
contains three fields, two "number" fields (primary/foreign keys) and
"text" field (i.e. name). The error occurred, when I tried to read
the container values after filling it with "number" and "text" data
from the database table. To fix the problem the "number" values had
to be cast to Integers using the CInt() function and the "text"
values had to be cast to Strings using the CStr() function.

I don't know why this fixed the problem, but it did. I am open to
any suggestions.

It wouldn't surprise me to find that you had inadvertently inserted
Field *objects* into your collection, rather than their values.

 

[Guest]

Hi Dick,

I am not sure what you mean "Field Objects", can you explain? Also, a
correction to my last comments, I am not using "containers" but "collection"
objects.

Thanks,

Eddie

 

[Dirk Goldgar]

Yes, I'd figured that you meant collections, not containers.

Now, I'm just guessing about all this, because you didn't post any of
the relevant code, but I suspect that you loaded up your collection from
a recordset that you'd opened on a table, using code something like
this:

Dim foo As Collection

' ...
Dim rs As DAO.Recordset ' or could be ADODB.Recordset

Set foo = New Collection
Set rs = CurrentDb.OpenRecordset("Bar")

Do Until rs.EOF
foo.Add rs!SomeField
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Now remember that a Collection can hold objects, not just simple types.
The way the above code is written, what is actually stored in the
collection foo is *not* the value of SomeField for each record in rs;
instead, what is stored there are multiple objects of type Field, with
all the properties of a Field object. However, each of those Field
objects is dependent on the parent recordset rs to which it belongs. In
fact, closer examination would show that, at any given time, the Value
properties of all the field objects in foo are the same -- the value of
SomeField in the current record of rs. When rs is closed, though, those
Field objects are no longer valid. If you try to dereference any member
of the collection at this point, you'll get error 3420: Object invalid
or no longer set.

If you want to store the *values* of SomeField, from all the records in
rs, in the collection, you have to specify that specifically when adding
the item to the collection:

foo.Add rs!SomeField.Value

That way, what gets stored in the collection is not the field itself,
but the result of evaluating its Value property.

 

[Guest]

Hi Dick,

Thank you so very much for the feedback. You are correct. I did not append
the ".value" on the recordset field. I changed the code and took off the
casting function and everything worked. Your're great, thanks

Since programming in MS Access is new for me, can you suggest some good
resources (i.e., books, websites, etc)

Regards,

Eddie

 

[Dirk Goldgar]

Hi Dick,

Thank you so very much for the feedback. You are correct. I did not
append the ".value" on the recordset field. I changed the code and
took off the casting function and everything worked. Your're great,
thanks

You're welcome. I'm glad my guess panned out.

Since programming in MS Access is new for me, can you suggest some
good resources (i.e., books, websites, etc)

Regards,

Eddie

As web sites go, I'd say the Access Web ( www.mvps.org/access ) is
probably the best place to start. It has answers to almost all the
frequently asked how-to questions, and some pretty sophisticated stuff
that maybe isn't asked all that often, but is a lot of fun to know
about. Also, it has links to a great many other good Access sites.

The best book I've seen for an Access developer is _Access <version>
Developer's Handbook_, by Litwin, Getz, et. al.(Sybex). There are
versions for Access 97, 2000, and 2002 -- the last two come in two
volumes, "Desktop" and :"Enterprise". If you use Google Groups to
search the Access newsgroups for book suggestions, you'll see the books
that are most frequently recommended, and can decide which of them look
best for you.

 

[Guest]

Hi Dirk,

Sorry about misspelling your first name. I just realized my error; my
father's name is Dick. Have a great weekend, Dirk

Once again, thanks for all your help

 

[Dirk Goldgar]

Hi Dirk,

Sorry about misspelling your first name. I just realized my error; my
father's name is Dick. Have a great weekend, Dirk

Once again, thanks for all your help

You're welcome. It was my pleasure.

 

[Guest]

Oh sweet Jesus, Dirk. You are a genius! I was having the same problem as
bakery guy.

I think your one-sentence reply is the best one I have EVER had the pleasure
of learning from! It not only made sense of the "non-sense" error message
but also pointed out the obvious that was sooooo close I couldn't see it.

This thread is pretty old, so I'm not sure you'll see this praise, but I'm
sure you'll feel some sort of good karma, chi, or whatever good feelings
transcend well through unseen internet postings.