MSAccess 2000 -- Error 91 problem

  • Thread starter Thread starter Cabby
  • Start date Start date
C

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
 
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
Click to expand...
Use the explicit namespace declaration here:
Dim rs As DAO.Recordset
Dim bmkReturnHere As Variant
Set rs = Me.Recordset
Click to expand...
Here you should work on the clone, then you need the bookmarks no longer.
Set rs = Me.RecordsetClone
rs.MoveFirst
Click to expand...
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
Click to expand...


mfG
--> stefan <--
 
Stefan Hoffmann said:
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 <--
Click to expand...

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
 
Cabby said:
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
Click to expand...


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.
 
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
 
Cabby said:
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
Click to expand...


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.
 
Marshall Barton said:
You should not use Close in either case.
Click to expand...
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.
Click to expand...
This explains a problem I had with earlier projects.
Thank-you
Cabby
 
Cabby said:
"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?
Click to expand...

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.
Click to expand...

Glad to be able to help.
 
Back
Top