Access: Move to next record until EOF

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 reache开发者_开发问答s 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.

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

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)...

I have done this in the past, and have always used this:

  With Me.RecordsetClone
    .MoveFirst
    Do Until .EOF
      If Me.Dirty Then
         Me.Dirty = False
      End If
      .MoveNext
      Me.Bookmark = .Bookmark
    Loop
  End With

Some people would use the form's Recordset, which doesn't require setting the bookmark (i.e., navigating the form's Recordset navigates the form's edit buffer automatically, so the user sees the move immediately), but I prefer the indirection of the RecordsetClone.

Set rs = me.RecordsetClone
rs.Bookmark = me.Bookmark
Do
    rs.movenext
Loop until rs.eof

If you want cmd buttons that loop through the form's records, try adding this code to your cmdNext_Click and cmdPrevious_Click VBA. I have found it works well and copes with BOF / EOF issues:

On Error Resume Next

DoCmd.GoToRecord , , acNext

On Error Goto 0


On Error Resume Next

DoCmd.GoToRecord , , acPrevious

On Error Goto 0

Good luck! PT

Keeping the code simple is always my advice:

If IsNull(Me.Id) = True Then
  DoCmd.GoToRecord , , acNext
Else
  DoCmd.GoToRecord , , acLast
End If

Add This Code on Form Close Event whether you add new record or delete, it will recreate the Primary Keys from 1 to Last record.This code will not disturb other columns of table.

Sub updatePrimaryKeysOnFormClose()
    Dim i, rcount As Integer
    'Declare some object variables
    Dim dbLib As Database
    Dim rsTable1 As Recordset
    'Set dbLib to the current database (i.e. LIBRARY)
    Set dbLib = CurrentDb
    'Open a recordset object for the Table1 table
    Set rsTable1 = dbLib.OpenRecordset("Table1")
    rcount = rsTable1.RecordCount
    '== Add New Record ============================  
        For i = 1 To rcount
              With rsTable1
                      rsTable1.Edit  
                      rsTable1.Fields(0) = i  
                      rsTable1.Update  
                      '-- Go to Next Record ---  
                      rsTable1.MoveNext
             End With  
        Next  
        Set rsTable1 = rsTable1
End Sub