Why won't my 'Do Until Rst.EOF ' statement work?

[Ali Edwards]

Hi!

Can anyone tell me why I get the message "Cannot go to the next record. You may be at the end of the recordset"? I am trying to stop the procedure when it gets to the last record.....

Many thanks all..

Private Sub Cycle_Records_Click()

Dim stdocname As String
Dim Rst As Recordset
Set Rst = Me.RecordsetClone

Do Until Rst.EOF = True
DoCmd.GoToRecord , , acNext
Loop

End Sub

 

[John.Woody]

If you want to go straight to the las record try this:

Private Sub Cycle_Records_Click()

Dim stdocname As String
Dim Rst As Recordset
Set Rst = Me.RecordsetClone

rst.Movelast

End Sub

 

[Ali Edwards]

Thanks John. I tried that but it doesn't have the effect I need. I really need it to step through all the records but stop when it gets to the last one without giving me the error message.

 

[simongallop]

dim Rst as Recordset
Set Rst = Currentdb.OpenRecordset("NameofTable/Query")

Rst.Movefirst
Do
Rst.MoveNext
Loop Until Rst.EOF = True
Rst.Close
End Sub

 

[Ali Edwards]

Thanks Harry. It doesn't move the records at all, just stays on record 1. I'm using Access 97 - is that a problem?

Thanks again

 

[John.Woody]

What are you trying to do as you loop through the records?

 

[Ali Edwards]

Long story....Basically I have 200 labels and 200 images in a form. The images only become visible if there is a corresponding record in the underlying query. I'm using the following code to do this:

Private Sub Form_Current()

If Me("Spare Keys") = "001" Then
Image1.Visible = True
ElseIf Me("Spare Keys") = "002" Then
Image2.Visible = True
ElseIf Me("Spare Keys") = "003" Then
Image3.Visible = True
ElseIf ......blah blah


Trouble is the form shows only one record at a time so the images don't become visible for each record until you scroll through the records, for example clicking on the next record selector and holding the mouse down so all the records are scrolled through. Each time a record is passed the corresponding image appears (or not if there is no record in the query) and where there is a gap (no record) it means that position is available for use.
This all works very well (a bit clumsy to you gurus no doubt)but my code doesn't recognise the end of the recordset so gives me the "You're at the end of the recordset - Can't go to next record" message.

Though I'm sticking this code on a command button at the moment I intend to run it with the On Open of the form when I can get it to work. Are you asleep yet?

Thanks

 

[simongallop]

If you are trying to find the empty records, then why don't you run a query that only shows records where the image field IsNull?

 

[SteveA]

You should be able to do the following:

Private Sub Cycle_Records_Click()

Dim stdocname As String
Dim Rst As Recordset
Set Rst = Me.RecordsetClone

'If there are records to process, continue
If Rst.RecordCount>0 Then
'Move to the first record in the recordset
Rst.MoveFirst
'Process each record
Do Until Rst.EOF
'Place your logic here...
Rst.MoveNext
Loop
Rst.close
set Rst = nothing
End Sub

Unlike VB, Access does not automatically move to the first record in a recordset. Therefore you need to check that at least one record was returned and then move the Cursor to the first record. Because you are on the first record when the Do statement starts, you then place Rst.MoveNext at the end of the loop instead of the start.

HTH
SteveA

[This message has been edited by SteveA (edited 01-18-2002).]

 

[SpawnAccess]

You must use the Bookmark property.

Private Sub Cycle_Records_Click()

Dim stdocname As String
Dim Rst As Recordset
Set Rst = Me.RecordsetClone

Do Until Rst.EOF
Rst.MoveNext ' Next in Rst
If Not Rst.EOF then
Me.Bookmark = rst.Bookmark ' Next in Form
End If
Loop

End Sub

 

[Ali Edwards]

Thanks everyone - got there in the end with the following:


Private Sub Cycle_Records_Click()
Dim stdocname As String
Dim Rst As Recordset
Set Rst = Me.RecordsetClone

Rst.MoveFirst
Do Until Rst.EOF
Rst.MoveNext ' Next in Rst
If Not Rst.EOF Then
Me.Bookmark = Rst.Bookmark ' Next in Form
End If
Loop


End Sub


Many thanks for all the replies.