Next and Previous Buttons for MS Access Bound Forms

Normally I prefer to use unbound controls on my forms since I feel it gives me more flexibility and control, however on a recent project I needed to use bound controls. This form has 13 subforms in a tab control for 13 different audio-visual media formats. I couldn’t allow the users to create new records using the navigation buttons on the subforms since that would create records in the table with no way of linking them back to the proper subform. I, instead, added a “New Item” button to each subform, which creates the new record and inserts the media format into it, and deleted the navigation buttons. This meant I needed Next and Previous buttons on the subforms.

Back on 07Ju11 I explained how to make Next and Previous buttons on an unbound form. It’s a little different on a bound form. I did, however, want some of the same behavior. I don’t like the Previous button to show if you are on the first record or the Next button to show if you are on the last record. This prevents the user from click a button when there is nowhere to go. The code below is how I control the display of the buttons. I ended up including Previous, Next, and Last buttons, a First button could also be included if you like. I also included a text box to let the user know that they are on “record 1 of 7”, or wherever they are.

For the sake of simplicity I used the exact same code for all three buttons:

     Private Sub cmdPrevious _Click()

     'Private Sub cmdNext _Click()

     'Private Sub cmdLast _Click()

          Dim intIndex As Integer

          Dim intCount As Integer

          'For the Previous button use:

          DoCmd.GoToRecord , , acPrevious

          'For the Next button use:

          'DoCmd.GoToRecord , , acNext

          'For the Last button use:

          'DoCmd.GoToRecord , , acLast

          'Get the count of records in the form's record set.

          intCount = Me.Recordset.RecordCount

          'Debug.Print " Count = " & intCount

          'Get the cursor position in the recordset.

          'Since the cursor position for the first record is zero

          'add 1 to the cursor position so that the last one will equal the record count.

          intIndex = Me.Recordset.AbsolutePosition + 1

          'Debug.Print "Postion = " & intIndex

          If intCount = intIndex Then

          'The last record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Next and Last buttons, one which has the focus,

          'make sure the Previous button is visible and move the focus to it.

               Me.cmdPrevious.Visible = True

               Me.cmdPrevious.SetFocus

               Me.cmdNext.Visible = False

               Me.cmdLast.Visible = False

          ElseIf intIndex = 1 Then

          'The first record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Previous button, which has the focus,

          'make sure the Next button is visible and move the focus to it.

               Me.cmdNext.Visible = True

               Me.cmdNext.SetFocus

               Me.cmdPrevious.Visible = False

               Me.cmdLast.Visible = True

          ElseIf intIndex > 1 And intIndex < intCount Then

          'The displayed record is not the first or last record, all buttons should show.

          'Since you are not changing the visible status of the button used 

          'the focus does not need to be reset.

               Me.cmdPrevious.Visible = True

               Me.cmdNext.Visible = True

               Me.cmdLast.Visible = True

          End If

         'Give the user an indication of where they are in the record set.

          Me.txtItemCount = "Item " & intIndex & " of " & intCount

     End Sub

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