MSAccess 2000 -- Error 91 problem

[Cabby]

The following code gives me Error 91 after I click button a second time.
Can someone tell me how to fix it.

Private Sub cmdTest_Click()
Dim rs As Recordset
Dim bmkReturnHere As Variant
Set rs = Me.Recordset
rs.MoveFirst
Do Until rs.EOF
bmkReturnHere = rs.Bookmark
If rs!OKd = False And rs!RunsOffOf <> 0 Then
rs.FindFirst "ID = " & rs!RunsOffOf
Do While Not rs.NoMatch
rs.Edit
rs!OKd = False
rs.Update
rs.FindNext "ID = " & rs!RunsOffOf
Loop
End If
rs.Bookmark = bmkReturnHere
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

tia,
Cabby

 

[Stefan Hoffmann]

hi Cabby,

The following code gives me Error 91 after I click button a second time.
Can someone tell me how to fix it.

Private Sub cmdTest_Click()
Dim rs As Recordset

Use the explicit namespace declaration here:
Dim rs As DAO.Recordset

Dim bmkReturnHere As Variant
Set rs = Me.Recordset

Here you should work on the clone, then you need the bookmarks no longer.
Set rs = Me.RecordsetClone

rs.MoveFirst

Not really necessary.

Do Until rs.EOF
If rs!OKd = False And rs!RunsOffOf <> 0 Then
rs.FindFirst "ID = " & rs!RunsOffOf
Do While Not rs.NoMatch
rs.Edit
rs!OKd = False
rs.Update
rs.FindNext "ID = " & rs!RunsOffOf
Loop
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

mfG
--> stefan <--

 

[Cabby]

hi Cabby,

Use the explicit namespace declaration here:
Dim rs As DAO.Recordset

Here you should work on the clone, then you need the bookmarks no longer.
Set rs = Me.RecordsetClone

Not really necessary.



mfG
--> stefan <--

The following table gives you some idea of my data. My outer loop
must cycle thru each record but my inner loop will jump all over
the table. Therefore I think I need bookmarks.
Marking a Street as True should mean that all subStreets are also
True. If this is not true, then mark Street as False.
I tried declaring Dim rs as DAO.Recordset but no change. With
further research, problem went away when I removed 'rs.Close'.
Please note that the first time I click button, it always works.
I do not understand. Can you explain or refer me to an explanation.


ID Street RunsOffOf Okd
1 SS KK True
2 KK AA False
3 PP TT False
4 RR PP True
5 AA 0 False
6 CC KK False
7 EE PP True
8 TT AA False
9 XX KK True
10 DD TT False

 

[Marshall Barton]

The following code gives me Error 91 after I click button a second time.
Can someone tell me how to fix it.

Private Sub cmdTest_Click()
Dim rs As Recordset
Dim bmkReturnHere As Variant
Set rs = Me.Recordset
rs.MoveFirst
Do Until rs.EOF
bmkReturnHere = rs.Bookmark
If rs!OKd = False And rs!RunsOffOf <> 0 Then
rs.FindFirst "ID = " & rs!RunsOffOf
Do While Not rs.NoMatch
rs.Edit
rs!OKd = False
rs.Update
rs.FindNext "ID = " & rs!RunsOffOf
Loop
End If
rs.Bookmark = bmkReturnHere
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

In addition to all that Stefan, pointed out, I rhink the
error is becaused you are closing the form's recordset.
It's good to set the recordset *variable* to Nothing when
you are done with it, but if you did not open an object, do
not close the object.

OTOH, I think the bookmarks are necessary even when you use
(recommended) RecordsetClone. Without the bookmarks, your
inner loop would interfer with the outer loop.

 

[Cabby]

I think I have solved the problem. After reading an explanation about
rs.close vs Set rs = nothing by David W. Fenton
http://www.pcreview.co.uk/forums/thread-3661095.php

I need to use:
Dim rs as DAO.Recordset
Set rs = Me.Recordset
Set rs = Nothing
**OR**
Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.Close
Set rs = Nothing

It seems to work! Thanks for your reply.

Cabby

 

[Marshall Barton]

I think I have solved the problem. After reading an explanation about
rs.close vs Set rs = nothing by David W. Fenton

I need to use:
Dim rs as DAO.Recordset
Set rs = Me.Recordset
Set rs = Nothing
**OR**
Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.Close
Set rs = Nothing

You should not use Close in either case.

If you use Recordset, Access will try to update the screen
to reflect your code's changing the current record all over
the place. Use Recordset clone to avoid messing with the
state of the form.

 

[Cabby]

You should not use Close in either case.

I thought that using RecordsetClone would create a second
record set in memory and using Close would release that
memory. Is this not right?

If you use Recordset, Access will try to update the screen
to reflect your code's changing the current record all over
the place. Use Recordset clone to avoid messing with the
state of the form.

This explains a problem I had with earlier projects.
Thank-you
Cabby

 

[Marshall Barton]

"Marshall Barton" wrote
I thought that using RecordsetClone would create a second
record set in memory and using Close would release that
memory. Is this not right?

It might release it, but since Access guarantees the
availability of RecordsetClone, it will just have to
recreate it. So, you aew not really saving any memory and
you are making Access do more work.

Again I say, if you did not open something, then you should
not try to close it. There are situation where that can
cause more serious problems then just causing redundant
work.

This explains a problem I had with earlier projects.

Glad to be able to help.