Avoiding Errors in Error Handling 1

[Petemush]

For all of my procedures I have just before the end a label generally name

Exit_ProcedureName

and a few more commands including

Exit Sub.

After that I have a label

Err_ProcedureName

followed by some error handling ending with

Resume Exit_ProcedureName

In some procedures I use recordsets and querydefs so after the Exit_ProcedureName label I set about closing any that have been used. If there is an error then the recordsets and querydefs still get closed.

The problem is, what happens if an error occurs before a querydef or recordset has been set. I will then get an error in the Exit_ProcedureName part of the code.

I tried to get round this by checking that the recordsets and querydefs were not nothing before I closed them, using IsNothing as recommended in Access Help. But IsNothing doesn't exist and I can't use = nothing since this generates an error.

Sooooo, any ideas how I can avoid errors in my error handling?

Cheers,

Pete

PS - Example code below


**Start of code**

..whole load of code using a querydef qdf..

Exit_ProcedureName:
Set db = nothing
qdf.close
Set qdf = nothing
Exit Sub

Err_ProcedureName:
MsgBox "Error: " & Err.Number & " - " & Err.Description & "blah blah"
Resume Exit_ProcedureName

End Sub

**end code**

 

[Junior1544]

try the isnull...

if isnull(db) then
'nothing
else
set db = nothing
end if

just an idea... junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

oh, yea... another idea (what i try to do),
dim rst as dao.recordset
set rst = currentdb.openrecordset("sql here&quot

define the recordset first thing... the only problem with that is when i reuse a recordset variable in the same code...

just another idea...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[KevinMoore]

I've dealt with that exact same problem around transactions and error trapping and have ended up using Boolean flag variables (I know, a pain).
If db, qdf, rst, etc. are local variables (i.e. Dim'd within the procedure), they will automatically be closed and killed when you exit the procedure, with or without error -- so no need to explicitly close them.

Kevin

 

[hayo]

There are always a lot more choices than you think ....

You may use

if not (db is Nothing) then
Set db = Nothing
end if

Hope this helps !

Hayo

 

[Petemush]

Bingo!

Cheers Hayo, that was the syntax I was after, I was trying to use IsNothing with no space ala IsEmpty and IsMissing etc.

Cheers,

Pete