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.

Flooded

I am using an ActiveX control on the Form and need to process each record individually. – Flooded

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

Tacye

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

Seaborg

Answer 3

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.

Moneymaking

Answer 4

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

Impacted

@David: I set the bookmark there for the loop to start at the current record. He initially said he needed to loop through the records, which did not imply to move the form itself. – Impacted

Hmm. Good point about the difference between the RecordsetClone and the form's edit/display buffer -- that is, the recordsetclone bookmark navigates the RecordsetClone independently of the form's record pointer. And you might want to do the edits without changing the display buffer. So, I deleted my comment. However, if the ActiveX control is needed to edit the record, then that surely has to be done in the form's edit/display buffer, not just in the RecordsetClone, as the ActiveX control will only be able to operate on the form's edit buffer (i.e., what's displayed). – Moneymaking

Answer 5

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

Fisch

Answer 6

Keeping the code simple is always my advice:

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

Comedienne

I like that. Nice and simple. Thanks – Flooded

Answer 7

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

Hazel