ACC1x: Checking for Last or First Record in a Macro

• Microsoft Access versions 1.0, 1.1

SUMMARY

In a macro, you cannot check the result of an action just performed. As a result, you can run into a problem when using a macro to move to the next record or the previous record. There is no record previous to the first record and no next record following the last record. If you attempt to go to the previous or last record when there is none, Microsoft Access displays error messages that may confuse users of a custom application.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual.

MORE INFORMATION

You can work around this on a form by using Access Basic functions to move from record to record. For example, use the following custom MoveToNext() function to move to the next record or previous record on a form:

   Option Explicit
   Function MoveToNext (DisplayMsg$, MoveForward%)
      Dim MyDyna As Dynaset
      Set MyDyna = Screen.ActiveForm.Dynaset

      MyDyna.Bookmark = Screen.ActiveForm.Bookmark
      If MoveForward% Then
         MyDyna.MoveNext
      Else
         MyDyna.MovePrevious
      End If
      If MyDyna.EOF Or MyDyna.BOF Then
         MsgBox DisplayMsg$
      Else
         Screen.ActiveForm.Bookmark = MyDyna.Bookmark
      End If
   End Function

• The name of the function.
• The message you want displayed if the current record is at the end or the beginning of the record set.
• "True" if you want to move to the next record. "False" if you want to move to the previous record.