Dear All,

Can someone please explain why I get the error at the line rsTemp.MoveFirst
in the FillListBox() sub I get the error:

?err.Number
91
?err.Description
Object variable or With block variable not set

Which I understand what it means but not why? I have declared the Recordset
properly (I think). I have a sub that calls a function that returns the data.
I have checked this and it works fine.

Please put me out of my misery. Thanks again.

Alastair MacFarlane


Private Sub FillListBox()
Dim rsTemp As ADODB.Recordset
Set rsTemp = New ADODB.Recordset
Set rsTemp = nonAtt.GetDataMail()
rsTemp.MoveFirst
Do Until rsTemp.EOF
    lstNames.AddItem rsTemp!MailID & vbTab & rsTemp!Name & vbTab &
rsTemp!EMail
Loop
Set rsTemp = Nothing
End Sub


Public Function GetDataMail() As ADODB.Recordset
Dim db As ADODB.Connection
Dim adoPrimaryRS As ADODB.Recordset
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=\\sql01\Mail.mdb;"
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select EMail,MailID,Name from EMail", db, adOpenStatic,
adLockOptimistic
Set adoPrimaryRS = Nothing
Set db = Nothing
Set GetDataMail = adoPrimaryRS
End Function

Alastair MacFarlane wrote:
Sure? :)

Show quoteHide quoteNothing, since adoPrimaryRS will always be Nothing when assigned to
GetDataMail.

Swap round the two lines I have marked ** and see how that goes.

Larry,

Thanks again and bottom of the class for Alastair. Fancy setting the return
to nothing before returning the values. I must of done a basic Recordcount
within the function and convinced myself that the problem was not in the
function.

Thanks again.

Alastair MacFarlane

Show quoteHide quote

<snip>

Show quoteHide quoteThat brings up a long standing question in the mind (if any) of this
beginner.
I always wondered if I should be setting internal(local to sub) object
variables to nothing.
I've read that it is unnecessary since they go out of scope as soon as the
sub/function ends.
But I have an uneasy feeling that I should be cleaning up after myself, more
for the sake of basic principles than actual necessity, since it is more of
a requirement in other languages than vb and maybe a good habit to get into?
However since the variable is more or less a pointer to the memory location
which holds the object data (maybe that's not an accurate statement but it's
my understanding) it seems that I have in the past experienced the effect
that when I passed an object back as a return from a function and then later
set the internal variable to nothing, the object to which the variable
referred and which had been passed back to the calling proceedure was
actually set to nothing when the internal variable was set to nothing...but
then I don't have verifiable proof of that - maybe something else was going
on and I haven't had time to exhaustively test it out...
I've just more or less quit setting internal object variables to nothing...
Whats the definitive best practice in this context?
Thanks
Mark

And which is a mostly religous debate among more experienced VB developers!

That's true.

That is a part of why I do it.

Something else was going on.  In the case of a recordset, for example, if
you call .Close on the local procedure's variable then the recordset that is
returned is also closed because it's the same object.  Any change you make
to that object is seen no matter which reference you use to look at it.
That sort of thing I've seen confuse people at first and lead them to stop
releasing local variables.   Setting the local variable =Nothing does not
cause any problems because you can never set an object to Nothing, you can
only ever set a reference-to-an-object to Nothing.  There's a subtle but imp
ortant distinction.

There is no definitive best practice.  Some will tell you that it is just a
waste of time since the cleanup does it for you.  It's true that the cleanup
does handle it but any time difference is going to be trivial since it has
to be done at some point anyway.  The main reasons I do it (in no special
order) are:

* I find it makes the code easier to read and maintain the code since it
explicitly release all resources that it specifically allocates
* it makes the logic more transparent and easier to port to other languages
* having the explicit Set o=Nothing statement allows me to set a breakpoint
and step into the termination event if it is my own class and to stop
execution after the object is released but before the procedure exits and
I've often found that useful.  It lets me manually re-create the object as
needed to continue testing within the one procedure during debugging.

* relying on the VB garbage collection leads to relying on it the object to
clean up after itself and I've seen cases (admittedly few) where not
explicitly calling a Close method results in resources not being freed;
always doing explicit cleanup ensures that I've done all I can

for that last one I've seen the counter-argument that the logic should
extend to setting string variables =vbNullString and I see the point.  I do
erase dynamic arrays frequently (not as consistently as I probably should)
but strings, while they are pointers to objects of a kind, are not the same
as references to objects which can be quite complex with significant
resource allocations of their own.  Strings are essentially simple data
types like Longs and Integers and don't require explicit release.  It may
not be a fully consistent approach, but it's mine and I like it! <g>

The bottom line is: pick a style and stick with it.  I find advantages in
explicitly releasing object references in debugging and tracing through code
so I do it but as long as you understand how object references work in VB
it's not required.

Show quote Hide quote muchisimas gracias
Mark
Show quoteHide quote

Damn, you beat me to saying "Here we go again!" And I so wanted to call
someone and infidel....

AN infidel, AN. Dammit.

<snip>

Explicitly cleaning up oneself is ... kind of satisfying

One is (more) in control of the sequence in which things happen.

CommandButton!
Using shell command to map drive can't get redirect result to file
Modal form stops code in another form
INSERT INTO
how to split numeric part from letters in string
Shrink Wrap
Iterative GetObject
SCC API implementation
text files & strings
Rotating a graphic