Stopping code and cleaning up

[Lars Brownies]

I have a long sub that uses several functions. Every function returns a
boolean. When False, the sub is stopped, like:

if MyFunc1 then
'do something
else
exit sub
endif

However I have several recordsets opened during execution of the sub. I'm
not sure what the best way is to clean them up when there is premature sub
exit. Should I explicitly clean them before every 'exit sub' or should/can I
direct the code to the exit routine of the sub and clean them there once? I
noticed that resume always require an error statement. Or is it best to
create a function that closes the recordsets?

Thanks,
Lars

 

[Jeff Boyce]

I'm not clear what you mean by "clean them up"...?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Lars Brownies]

rs.close
set rs = nothing

Lars

I'm not clear what you mean by "clean them up"...?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Douglas J. Steele]

Sub MySub()
On Error GoTo ErrHandler

Dim rsCurr As DAO.Recordset

'... code

Cleanup:
On Error Resume Next
rsCurr.Close
Set rsCurr = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume Cleanup

End Sub

 

[Lars Brownies]

Thanks Douglas,
The problem is that if a function returns False it's not always because an
(VBA) error occured. It could also be that a file could not be found. Also,
íf an error occurs it's being dealt with in the function. So the function
only returns True of False and the ErrHandler in the sub is not triggered.
Or am I making the wrong assumptions? I would like somthing as the
following, which doesn't work since resume requires an error:

Sub MySub()
On Error GoTo ErrHandler

Dim rsCurr As DAO.Recordset

if not (MyFunc1) then
Resume Cleanup
endif

'other code

if not (MyFunc2) then
Resume Cleanup
endif

Cleanup:
On Error Resume Next
If rsCurr.State= adStateOpen then
rsCurr.Close
Endif
If not rsCurr Is Nothing then
Set rsCurr = Nothing
endif
Exit Sub

ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume Cleanup
End Sub

Hope this is more clear.
Lars

 

[gustavo]

I have a long sub that uses several functions. Every function returns a
boolean. When False, the sub is stopped, like:

if MyFunc1 then
'do something
else
exit sub
endif

However I have several recordsets opened during execution of the sub. I'm
not sure what the best way is to clean them up when there is premature sub
exit. Should I explicitly clean them before every 'exit sub' or should/can
I direct the code to the exit routine of the sub and clean them there
once? I noticed that resume always require an error statement. Or is it
best to create a function that closes the recordsets?

Thanks,
Lars

 

[Tom Wickerath]

Hi Lars,

....since resume requires an error....

Although it's kind of old fashioned, you could always use GoTo Cleanup
statements instead of Resume Cleanup.

I would not attempt to create a separate function to do this type of
cleanup. For one thing, unless your recordsets were declared at the module
(or global) level, attempting to close and set = nothing in a different
function would likely attract run-time errors.

Finally, you seem to be mixing DAO and ADO code:

Dim rsCurr As DAO.Recordset
:
:
Cleanup:
On Error Resume Next
If rsCurr.State= adStateOpen then

rsCurr.State should attract a compile error in this case. Rather than use On
Error Resume Next, which will not be honored if a particular user's VBE is
configured to Break on all errors (yes, I have seen this before), I use
cleanup code like this to close and destroy DAO recordsets, such as one
declared as "rs". I also set a db variable to nothing, if I had previously
used "Dim db As DAO.Database" and "Set db = CurrentDB()":

Private Function FunctionName() As ReturnType
On Error GoTo ProcError
:
Your code goes here
:
:
ExitProc:
If Not rs Is Nothing = True Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function

ProcError:
Msgbox Err.Number & ": " & Err.Description, _
vbCritical, "Error in FunctionName..."
Resume ExitProc
End Function


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Lars Brownies]

Thanks Tom.

I would not attempt to create a separate function to do this type of
cleanup. For one thing, unless your recordsets were declared at the module
(or global) level, attempting to close and set = nothing in a different
function would likely attract run-time errors.

In fact 2 of the 3 recordsets are declared at the module level. You say 'for
one thing'. Would there be other consequences?

Finally, you seem to be mixing DAO and ADO code:

Thanks for the pointer. I hadn't tested that code yet.

If Not rs Is Nothing = True Then

Is there a particular reason that you include the = True part. Or is that
for readibility?

Lars

 

[Tom Wickerath]

Hi Lars,

You say 'for one thing'. Would there be other consequences?

I suppose there is always the possibility of encountering a run-time error
in this called function, such that control would never return to the calling
procedure. I think it is a good coding practice to close recordsets in the
same procedure in which they were set. But, you're always free to create this
function, if thats what you want to do.

Is there a particular reason that you include the = True part. Or is that
for readibility?

Both readibility, and my coding style. I have always preferred to be
explicit, instead of relying on defaults.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Lars Brownies]

Thanks again for the tips!
Lars

Hi Lars,


I suppose there is always the possibility of encountering a run-time error
in this called function, such that control would never return to the
calling
procedure. I think it is a good coding practice to close recordsets in the
same procedure in which they were set. But, you're always free to create
this
function, if thats what you want to do.


Both readibility, and my coding style. I have always preferred to be
explicit, instead of relying on defaults.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[David W. Fenton]

I have always preferred to be
explicit, instead of relying on defaults.

Would you write:

If IsNull(Me!txtField) = True Then

I wouldn't, since I know that IsNull() returns a proper Boolean and
has no reference problems.

I would write:

If Not (rs Is Nothing) Then

The parens make clear what's being evaluated and adding = True to
that seems to me to add no readability.

The only caution I would add is that some people compare to True all
the time so as to avoid the implicit reference issue, but that's
true only of controls holding Boolean values, so this:

If Me!chkMyCheckBox Then

....is dangerous, while these:

If Me!chkMyCheckBox.Value Then
If (Me!chkMyCheckBox) Then
If Me!chkMyCheckBox = True Then

....are all perfectly fine in the first case, the value is tested
explicitely, and latter two, evaluation is forced. The reason If
Me!chkMyCheckBox is dangerous is because VBA defaults to ByRef
treatment of controls and objects, and VBA guesses wrong that you
want to do something with the control, rather than with the data it
bears.

But I would not thus suggest always comparing to True as the
solution. I would say that all you have to remember is to force an
evaluation when doing Boolean tests on controls bearing Boolean
values (which is not necessarily just checkboxes).

 

[James A. Fortune]

Would you write:

If IsNull(Me!txtField) = True Then

I wouldn't, since I know that IsNull() returns a proper Boolean and
has no reference problems.

I would write:

If Not (rs Is Nothing) Then

The parens make clear what's being evaluated and adding = True to
that seems to me to add no readability.

The only caution I would add is that some people compare to True all
the time so as to avoid the implicit reference issue, but that's
true only of controls holding Boolean values, so this:

If Me!chkMyCheckBox Then

...is dangerous, while these:

If Me!chkMyCheckBox.Value Then
If (Me!chkMyCheckBox) Then
If Me!chkMyCheckBox = True Then

...are all perfectly fine in the first case, the value is tested
explicitely, and latter two, evaluation is forced. The reason If
Me!chkMyCheckBox is dangerous is because VBA defaults to ByRef
treatment of controls and objects, and VBA guesses wrong that you
want to do something with the control, rather than with the data it
bears.

But I would not thus suggest always comparing to True as the
solution. I would say that all you have to remember is to force an
evaluation when doing Boolean tests on controls bearing Boolean
values (which is not necessarily just checkboxes).

David,

I found your post quite helpful. Since I don't rely on the default
property of the control I can dispense with the overcautious '= True'
in my code. For cleaning up, an old-fashioned GoSub...Return also
works if you have several exit points , but you're not likely to
save many lines of code over simply repeating the cleanup lines.

James A. Fortune
(e-mail address removed)

 

[ÇáãÓÊÔÇÑ ÇÈææÍíÏ]

I'm not clear what you mean by "clean them up"...?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned
in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.