Loop does not End (Acess 2003)

Topic

New Reply

The following code is intended to step through a recordset until one text box matches the other at which point the loop should end. The loop is in the oncurrent function of the Form.
When the code runs and the values match the loop ends and the code advances to end sub but it does not end at this point as expected rather it jumps back to the previous record in the recordset and restarts the loop which then causes an infinite loop. How can I End this Sub Procedure

Any assistance is appreciated.

Sub Form_Current()

If FindIssueFlag = True Then

Dim rsFind As Recordset
Set rsFind = Me.Recordset

Do Until Me.txtTemp.Value = Me.txtIssueID.Value
rsFind.MoveNext
Loop
End If

FindIssueFlag = False

End Sub

Regards,

Tom Duthie

Reply | Quote

Replies

Since Me.Recordset is the record source of the form, rsFind.MoveNext causes the On Current event to fire again. You shouldn’t be calling this code from the On Current event of the form. Perhaps you could call it from the On Load event?

Reply | Quote

When you have a bound form, any “looping” through the recordset should be done against the RecordSetClone, not the recordset itself. Change start of your code to this:

Set rsFind = Me.RecordSetClone
rsFind.bookmark = me.bookmark

At the end, use this code to set your form to the found record, if one is found:

if Me.EOF = False Then
Me.bookmark = rsFind.Bookmark
End if

Reply | Quote

Edited by HansV to remove large number of superfluous empty lines.

Dear Mark and Hans:

With you suggestions and our determination the following code works in all situations. It may not be the best code but it works

Sub Form_Current()
On Error Resume Next
Dim rst As Recordset
Dim lngCount As Long
Dim Count As Integer
Dim rsFind As Recordset

‘This code displays the “Record # of #” label on my form
Set rst = Me.RecordsetClone
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Me.txtRecordCounter = “Record ” & Me.CurrentRecord & ” of ” & lngCount

‘The rest of this code loops until the values in txtTemp and txtIssueID match in a given recordset (essentially a find function). Every

Reply | Quote