Recordset.EOF not working

[Adam Thwaites]

I have the following code on a form to make sure the first record I open has
not been assigned to anyone. I'm not sure how to reference the recordset that
is already open of the form, and Me.RecordsetClone.EOF is never returning
True. I just get error 2105, 'You can't go to the specified record' when the
code reaches the last record and tries to do another goto acNext.
The recordsource of the form is tbl_Main.

Do Until chkAssigned = False
If Me.RecordsetClone.EOF Then
DoCmd.GoToRecord , , acFirst
Else
DoCmd.GoToRecord , , acNext
End If
Loop
chkAssigned = True

 

[Allen Browne]

If EOF is True, it might be because there are no records, in which case the
attempt to go to the first record will fail. You could test this be seeing
if both BOF and EOF are true at the same time, or (assuming Access tables so
this is a DAO recordset) by testing the RecordCount.

This kind of thing:

With Me.RecordsetClone
If .RecordCount > 0 Then
If .BOF Then
RunCommand acCmdRecordsGoToLast
ElseIf .EOF Then
RunCommand acCmdRecordsGoToFirst
Else
RunCommand acCmdRecordsGoToNext
End If
End With

 

[Terry Kreft]

Should be something like

Do Until chkAssigned = False
If Me.RecordsetClone.EOF Then
Me.RecordsetClone.MoveFirst
Else
Me.RecordsetClone.MoveNext
End If
me.BookMark = Me.RecordsetClone.BookMark
Loop

Having said that if chkAssigned is bound to a field in the forms recordset
(which I woul guiess it is) there is a more elegant way to do this.

with me.recordsetclone
' Assuming chkAsigned is bound to the field Assigned change as appropriate
.FindFirst "Assigned = False"
if not .Eof then
Me.BookMark = .BookMark
end if
end with

 

[Adam Thwaites]

I'v used the first solution as I need to retain the position the user enters
into the recordset and the second one loses that,

Do Until chkAssigned = True
Me.Bookmark = Me.RecordsetClone.Bookmark
If Me.RecordsetClone.EOF Then
Me.RecordsetClone.MoveFirst
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.RecordsetClone.MoveNext
End If
Loop

I had to move the bookmark code to this position as anywhere else it brings
up 'No current record' when EOF = true

 

[Adam Thwaites]

Do Until chkAssigned = True
If Me.RecordsetClone.EOF Then
Me.RecordsetClone.MoveFirst
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.RecordsetClone.MoveNext
End If
Loop

More like that as the first bookmark was bringing up the error