The Records of a Database: Record Navigation

Introduction

There are two main types of views a user displays when using a database. One view is referred to as data sheet because it displays a series of columns and rows. This is the regular view of a table. Here is an example:

This type of view displays as many records as it can afford, based on its dimensions. To navigate among records, the user can click the Navigation Buttons in the bottom section of the window. This moves the caret from one record to the next or from a record to the previous one. When the view displays the last record, it would not move further. To move a particular record based on its number, the user can type an index, such as 12, in the text box located between the navigation buttons and press Enter.

Besides the data sheet, another view consists of displaying one record at a time. This is the regular view used with forms:

Once again, to move from one record to another, the user can click the buttons in the bottom section of the form.

Object	Role
	First Record: allows moving to the first record
	Previous Record: allows moving one record back (if there is one) from the current record
	Record Indicator: Displays the number representing the current record
	Next Record: allows moving one record ahead
	Last Record: Allows moving to the last record
	New Record: Used to enter a new record for a form Since you cannot create a new record on a report, this button is not available on it

Most users know how to use navigation buttons to move among records. Some others don't. This means that, sometimes, you will need a way to make it easy for the user to perform this navigation. Fortunately, Microsoft Access provides everything you need to do this. To perform navigation programmatically, the DoCmd object is equipped with the GoToRecord method that we reviewed earlier. After specifying the first argument as acDataForm and the second argument with the name of the form, you use the third argument to specify what record to jump to. The values of this third argument can be:

Value	Description	Same as Clicking
acFirst	Navigates to the first record
acPrevious	Navigates to the previous record
acNext	Navigates to the Next record
acLast	Navigates to the last record
acGoTo	Navigates to a record. In this case, pass a number as the fourth argument
acNewRec	Opens a new empty record

To assist you with creating your own navigation buttons, Microsoft Access provides the Button Wizard. To use it, with the Control Wizards button down, after adding a Command Button to the form, when the Command Button Wizard, in the first page, select Record Navigation (it should be selected by default):

In the Actions list, you can select the type of navigation you want the new button to perform. In the second page, you have the option of using a suggested bitmap for the button or you can enter a caption in the top text box and click Next. In the third page, you can enter a name for the button and click Finish. At the end, the wizard will have code for you that essentially calls the DoCmd.GoToRecord method. Here is an example:

Private Sub cmdNextStudent_Click()
On Error GoTo Err_cmdNextStudent_Click

    DoCmd.GoToRecord , , acNext

Exit_cmdNextStudent_Click:
    Exit Sub

Err_cmdNextStudent_Click:
    MsgBox Err.Description
    Resume Exit_cmdNextStudent_Click
    
End Sub

This code is used to move to the next record.