Access Record Navigation using GoToRecord

A nice feature in MS Access is the record navigator pane at the bottom of a form or datasheet.

Sometimes you need more control which you can achieve by making custom buttons with DoCmd.GoToRecord.

You can use the GoToRecord method to make the specified record the current record in an open table, form, or query result set datasheet.

DoCmd.GoToRecord has 6 possible values for the Record argument. The meaning of the first four, acFirst, acNext, acPrevious and acLast is self-evident. acGoTo jumps to the Nth record. Finally acNewRec inserts a new (unsaved) record and makes that the current record.

If you don't specify the object, the command works on the active object, the Access object that currently has focus:

DoCmd.GoToRecord, Record:=acNext, Offset:=1

Alternatively, if you do specify the ObjectType and ObjectName arguments, the movement will be applied on that:

DoCmd.GoToRecord ObjectType:=acDataForm, ObjectName:=, Record:=acNext, Offset:=1

Offset represents the number of records to move forward or backward in case of acNext or acPrevious, or the record to move to if you specify acGoTo for the Record argument.

The image above shows the Code VBA (download) builder that explains what the different options mean and allows you to select required values. The image below shows the menu navigation to easily find such common procedures in Access.

Using DoCmd.GoToRecord to navigate a subform

To navigate a subform, you first have to give it focus:

Me![strSubformControlName].SetFocus
DoCmd.GoToRecord Record:=acNext, Offset:=1

Disable navigation buttons if at the first or last record in a form

Access disables the Previous and Next buttons on the record navigator by default if you are at the beginning or end of a recordset. If you use custom navigation buttons you can add the following code to the Current event of your form:

Private Sub Form_Current()
cmdPrevious.Enabled = Not (CurrentRecord = 1 Or NewRecord)
cmdNext.Enabled = Not (CurrentRecord = DCount("*", RecordSource) Or NewRecord)
End Sub