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

User · Accepted Answer

To loop from current record to the end   While Me CurrentRecord  lt  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  lt  Me Recordset RecordCount Then           End If

User · Answer

Keeping the code simple is always my advice   If IsNull Me Id    True Then   DoCmd GoToRecord     acNext Else   DoCmd GoToRecord     acLast End If

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[ms-access] Access: Move to next record until EOF

Examples related to ms-access

Examples related to vba