Error-trapping question

[Pete]

I'm trying to improve my code so that when I open a recordset object,
I can absolutely guarantee it is closed and is set = Nothing. I have
read some old threads and they all say to use the functional
equivalent of the following code:

Public Sub CloseRecordset()
On Error GoTo Sub_Error
Dim rs As Recordset

rs.FindFirst "This Will Error"

Sub_Exit:
On Error Resume Next
rs.Close '<----"Object variable or with block not set"
Set rs = Nothing

Sub_Error:
GoTo Sub_Exit
End Sub
However, *THIS CODE DOES NOT WORK*. It should attempt to "rs.Close"
and fail, and move to the next record as dictated by the "On Error
Resume Next" line. But it doesn't; it pops up the Debug/End/Help box
here (the default error handling).

I'm using Access 97, and yes, this is cut/pasted directly out of my
test function. I understand that originally, I have it go to
Sub_Error whenever an error occurs. When I get to the exit point (the
Sub_Exit label), it should switch to the "resume next" behavior, but
instead apparently resets the error trapping to default. What am I
doing wrong?
Pete

[Allen Browne]

Pete, you need to add the line:
Exit Sub
immediately after:
Set rs = Nothing.

If you do not exit sub at this point, the code falls into the error trapping
routine, and is sent back to the Sub_Exit label.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Pete" <ps********@zombieworld.com> wrote in message
news:98**************************@posting.google.c om...

I'm trying to improve my code so that when I open a recordset object,
I can absolutely guarantee it is closed and is set = Nothing. I have
read some old threads and they all say to use the functional
equivalent of the following code:

Public Sub CloseRecordset()
On Error GoTo Sub_Error
Dim rs As Recordset

rs.FindFirst "This Will Error"

Sub_Exit:
On Error Resume Next
rs.Close '<----"Object variable or with block not set"
Set rs = Nothing

Sub_Error:
GoTo Sub_Exit
End Sub
However, *THIS CODE DOES NOT WORK*. It should attempt to "rs.Close"
and fail, and move to the next record as dictated by the "On Error
Resume Next" line. But it doesn't; it pops up the Debug/End/Help box
here (the default error handling).

I'm using Access 97, and yes, this is cut/pasted directly out of my
test function. I understand that originally, I have it go to
Sub_Error whenever an error occurs. When I get to the exit point (the
Sub_Exit label), it should switch to the "resume next" behavior, but
instead apparently resets the error trapping to default. What am I
doing wrong?
Pete

[hal boyles]

step thru and tell us which line is crashing

[Allen Browne]

Try:
Dim rs As DAO.Recordset

If that line generates an error, it is a references issue. Details:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"phappyman" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

Thanks, but that isn't the problem. I built the test sub just to make
absolutely sure that what I was writing happened. The reason why I
forgot the "Exit Sub" line is because my subroutine *never got that
far*. Here is the (updated) subroutine:

Public Sub CloseRecordset()

On Error GoTo Sub_Error

Dim rs As Recordset

rs.FindFirst "This Will Error"

Sub_Exit:

On Error Resume Next

rs.Close

Set rs = Nothing

Exit Sub

Sub_Error:

GoTo Sub_Exit

End Sub



The problem is the same as before, but the obvious (unrelated) error has
been fixed.

Pete

Original question:

> I'm trying to improve my code so that when I open a recordset object,

> I can absolutely guarantee it is closed and is set = Nothing. I have

> read some old threads and they all say to use the functional

> equivalent of the following code:

> Public Sub CloseRecordset()

> On Error GoTo Sub_Error

> Dim rs As Recordset

> rs.FindFirst "This Will Error"

> Sub_Exit:

> On Error Resume Next

> rs.Close '<----"Object variable or with block not set"

> Set rs = Nothing

> Sub_Error:

> GoTo Sub_Exit

> End Sub

> However, *THIS CODE DOES NOT WORK*. It should attempt to "rs.Close"

> and fail, and move to the next record as dictated by the "On Error

> Resume Next" line. But it doesn't; it pops up the Debug/End/Help box

> here (the default error handling).

> I'm using Access 97, and yes, this is cut/pasted directly out of my

> test function. I understand that originally, I have it go to

> Sub_Error whenever an error occurs. When I get to the exit
> point (the

> Sub_Exit label), it should switch to the "resume next" behavior, but

> instead apparently resets the error trapping to default. What am I

> doing wrong?

> Pete

--
Posted via http://dbforums.com

[Pete]

Let me start over. This is not a references issue-the code runs. I
am using Access 97 and there isn't an ADO reference set, so the
ADODB/DAO precedence is not an issue.
I'm trying to improve my code so that when I open a recordset object,
I can absolutely guarantee it is closed and is set = Nothing. I have
read some old threads and they all say to use the functional
equivalent of the following code, which uses the "On Error Resume
Next" at the function's exit point:

'The numbers "01"-"12" at the beginning of the lines
'were added for readability
'Please note that my function does not have those
'numbers in the code, so no kneejerk replies please

01 Public Sub CloseRecordset()
02 On Error GoTo Sub_Error
03 Dim rs As Recordset

04 rs.FindFirst "This Will Error"

05 Sub_Exit:
06 On Error Resume Next
07 rs.Close '<----"Object variable or with block not set"
'Above error message appears on previous
'line; it shouldn't

08 Set rs = Nothing

09 Exit Sub

10 Sub_Error:
11 GoTo Sub_Exit
12 End Sub
This is the question: why does the On Error Resume Next not work? I
have stepped through the code, and it goes 01-02-03-04-10-11-05-06-07
and then POPS UP the default Access error handling on line 07.
Remember that line 06 has just instructed the environment to "Resume
Next" on an error. WHY DOES "On Error Resume Next" FAIL TO WORK AS
ADVERTISED?

That is my question.
Pete

[hal boyles]

Between steps 03 and 04 you willneed to OPEN the recordset. You can't find
a record until the set is open

[Pete]

"hal boyles" <ha********@timeinc.com> wrote in message news:<bk**********@inntp-m1.news.aol.com>...

Between steps 03 and 04 you willneed to OPEN the recordset. You can't find
a record until the set is open

Thanks, but no thanks. The point is not that the function WORKS.
Because the function doesn't DO ANYTHING ANYWAY. So stop critiquing
the FAKE FUNCTION I WROTE TO PROVE A POINT.

I'm tired of people not reading my post. If I'm being unclear, sorry.
My problem was that the line "On Error Resume Next" does not work as
advertised. Read my other most recent post again.

Yes, I'm probably being too rude, but so far I'm 0 for 4 on replies.

[Rick Brandt]

"Pete" <ps********@zombieworld.com> wrote in message
news:98*************************@posting.google.co m...

"hal boyles" <ha********@timeinc.com> wrote in message

news:<bk**********@inntp-m1.news.aol.com>...

Between steps 03 and 04 you willneed to OPEN the recordset. You can't find
a record until the set is open

Thanks, but no thanks. The point is not that the function WORKS.
Because the function doesn't DO ANYTHING ANYWAY. So stop critiquing
the FAKE FUNCTION I WROTE TO PROVE A POINT.

I'm tired of people not reading my post. If I'm being unclear, sorry.
My problem was that the line "On Error Resume Next" does not work as
advertised. Read my other most recent post again.

Yes, I'm probably being too rude, but so far I'm 0 for 4 on replies.

The only thing I notice that is different from how I usually do things is that I
would use...

10 Sub_Error:
11 Resume Sub_Exit
12 End Sub

....instead of GoTo Sub_Exit. I do exactly what your code example is attempting and I
do not get an error on the rs.Close line.

[Pete]

> The only thing I notice that is different from how I usually do things is that I

would use...

10 Sub_Error:
11 Resume Sub_Exit
12 End Sub

...instead of GoTo Sub_Exit. I do exactly what your code example is attempting and I
do not get an error on the rs.Close line.

Thanks! This was exactly my problem. I changed the "GoTo" to Resume
and it worked flawlessly.
Pete