Next and Previous Buttons on a MS Access Form

Since I generally use unbound controls on my forms when a record is selected from a listbox the built-in navigation buttons do not work, the form only sees one record. In order to allow users to navigate back and forth through the list in the listbox I needed to create my own navigation buttons.

The key to creating Next Record and Previous Record buttons is to have a public variable to hold the list index value.

     Option Compare Database

     Public intIndex As Integer

This variable is initially populated in the form’s On Load event.

     intIndex = Forms!frmFolderItemList.lstItems.ListIndex

The On Click event for the Next Record button takes intIndex, adds 1 to it and moves the focus of the listbox to that List Index value. Then the On Load event is called.

     Private Sub cmdNext_Click()

         'Debug.Print "intIndex = " & intIndex

         intIndex = intIndex + 1

          'Debug.Print "intIndex_Next = " & intIndex

          Forms!frmFolderItemList.lstItems.ListIndex = intIndex

         Call Form_Load

     End Sub

The On Click event for the Previous Record button subtracts 1.

     Private Sub cmdPrevious_Click()

         'Debug.Print "intIndex = " & intIndex

         intIndex = intIndex - 1

         'Debug.Print "intIndex_Next = " & intIndex

          Forms!frmFolderItemList.lstItems.ListIndex = intIndex

          Call Form_Load

     End Sub

This works very well, except when the user tries to click the Next Record button when the last record is already being displayed, or the Previous Record button when the first record is being displayed. To fix this the Previous Record button needs to be hidden when the listbox focus is on the first record, and the Next Record button needs to be hidden when the focus is on the last record. You can determine where the focus is in the list by comparing the List Index to the List Row Count.

Dimension a variable for the List Row Count in the On Load event for the form and populate it.

     Dim intRowCount As Integer

     intRowCount = Forms!frmFolderItemList.lstItems.ListCount

The variable for the List Index, intIndex, is a public variable that was populated earlier in the On Load event in order to be available for the On Click events of the Next Record and Previous Record buttons. The variable is simply reused here.

     'You cannot hide a control that has focus, so make sure the focus goes to some

     'control other than the Next Record or Previous Record buttons.

     Me.txtDescription.SetFocus

     If intRowCount > 1 Then

     'For a list with at least two items the Next button will be used

     'as long as you are not already at the last item.

          If intIndex = intRowCount - 2 Then

          'The List Index starts at 0 and the Row Count starts at 1 and counts the column heads.

          'If you are using the column heads you need to subtract 2 from the Row Count in order

          'to identify the last item in the list and hide the Next button.

          'If you are not using the column heads you need to subtract only 1.

               cmdNext.Visible = False

          Else:

               cmdNext.Visible = True

          End If

     Else:

     'When there is only one item in the list the Next button is not used.

          cmdNext.Visible = False

     End If

     If intRowCount > 1 Then

     'For a list with at least two items the Previous button will be used

     'as long as you are not already at the first item.

          If intIndex = 0 Then

          'The first item in the list will always have a List Index value of 0,

          'so if that is true hide the Previous button.

               cmdPrevious.Visible = False

          Else:

               cmdPrevious.Visible = True

          End If

     Else:

     'When there is only one item in the list the Previous button is not used.

          cmdPrevious.Visible = False

     End If