EOF won't end loop...need some help with code

[gusbrunston]

Hi.

I've written the following private sub that successfully does what I need to do. However, once all the records have been accessed and the calculations performed, I get an error and the sub stops. Can you help me get out of this loop in a proper manner?

Thanks a bunch!

Private Sub Form_Open(Cancel As Integer)

Dim rs As Object
Set rs = Me.Recordset.Clone

    DoCmd.GoToRecord , , acFirst
        'Do calculation for first record
    
    Do Until rs.EOF
        DoCmd.GoToRecord , , acNext
        'Do calculation for each record
    Loop
    
    DoCmd.Close
    
End Sub

Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[gusbrunston]

I forgot to give the error code:

It's Error code 2105, "You can't go to the specified record."

Thanks, Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[AccessMoron]

You are doing the calculation and move on the original
recordset, but looking for the .eof of the clone, which will never be reached.

 

[jfischer]

Rewrite your procedure as follows:

Private Sub Form_Open(Cancel As Integer)

Dim rs As Object
Set rs = Me.Recordset.Clone
If Not rs.EOF Then
  'Do calculation for first record

  rs.MoveNext    
  Do While Not rs.EOF
    'Do calculation for each record

    rs.MoveNext
  Loop

End If  
rs.Close
    
End Sub

 

[mac318]

Gus, another option is use error trapping; i.e.,
On Error Goto ErrHandler

SubRoutine Code

ExitHere:
Exit Sub

ErrHandler:
If Err.Number = 2105 then
'End of File
Resume ExitHere
Endif
'Something else happened
Msgbox Err.Number & vbCrLf & Err.Description
Resume ExitHere

Also, after any recordset movement command, you should syncronize with the underlying table:

rs.MoveNext
Me.Bookmark = rs.BookMark

This is my rationale for using error trapping. If an attempt is made to set the bookmarks after the first or last record, you will get an error (which will be trapped harmlessly by the error code.

mac

 

[gusbrunston]

Hi.

Thanks to all of you. I've studied each suggestion, and they are all very worth while, and have pointed me in the direction where I know I need to learn a lot more.

The calculation is in the "On Current" event, and performs properly when I move from record to record with "PageDown" or with the forward navigation button. But this is time-consuming and not in good taste.

What I've accomplished is a stopgap until I learn more: I've trapped the specific error, and exit the sub.

rs.MoveNext doesn't work in my sub for whatever reason.
If I don't dim the recordset I get an error.
Putting the calculation in the sub rather than in the On Current event doesn't work (I dunno' why)

So it's working, but I know there's a reason to fix it. When I get it worked out, I'll post my result here, admit my mistakes (but I won't cut my throat), and thank you again.

Cheers. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[mac318]

Gus, not only must you always dim(inision) the recordset, it must be dim'd where it will have scope within the subroutine in which you access it. A safe way to make sure that the recordset is available to each subroutine is to dim it under the Option Explicit. If you dim it in a Private subroutine, it is only available in that sub. I used rs.movenext; you must use the letters with which you dim'd your recordset. For example, I often use rst. If you use rst to dim your recordset, when you enter rst. (see the dot) you should get a drop down list of available method and properties. If you don't get this list, you probably don't have your recordset in scope. Don't be afraid to use error trapping as the ultimate solution. Sometimes is the best solution.

Hope this help you.

mac

 

[gusbrunston]

Dear Mac:

Yes, it helps. I'm learning, but the learning curve in Access seems pretty steep. I spent some years in DOS, Dartmouth Basic, Pick, and Quick Basic, but VB has it's own "lingo" and that's slow in coming and the manuals use examples that are often far removed from what I have to accomplish. Suggestions like yours are just what I need to keep me on track. Thanks, very much! Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com