Access: Move to next record until EOF

Question

I need to loop through a form by moving to the next record in the recordset.

I am using the Form_Current event to loop thru. I have used a couple of statements and have different outcomes.

This one sometimes crashes and gives the error message: "You can't go to the specified record."

DoCmd.GoToRecord , , acNext

This one only goes upto 72 records and stops.

DoCmd.RunCommand acCmdRecordsGoToNext

This one only goes upto 129 records and stops.

Me.Recordset.MoveNext

Trying to find an instruction that will go to the next record untill it reaches the End of File. I am using Access 2010 (Access 2002 -2003 file format mdb) as the front end. The recordsource is a SQL Server 2008 linked View.

Answer 1

To loop from current record to the end:

While Me.CurrentRecord < Me.Recordset.RecordCount
    ' ... do something to current record
    ' ...

    DoCmd.GoToRecord Record:=acNext
Wend

To check if it is possible to go to next record:

If Me.CurrentRecord < Me.Recordset.RecordCount Then
    ' ...
End If

Answer 2

If (Not IsNull(Me.id.Value)) Then
DoCmd.GoToRecord , , acNext
End If

Hi, you need to put this in form activate, and have an id field named id...

this way it passes until it reaches the one without id (AKA new one)...