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

Input Box Error Capture in MS Access

I find input boxes quite useful for getting data from users, especially if it is just a single number, but input boxes can present a problem if the user cancels or exits the input box, or clicks “Okay” without entering anything in the input box. In all three of these cases the input box returns a zero-length string. You not only need to capture this error, but you need to discover the intent of the user in order to redirect the code in the right way. If the user meant to exit or cancel the input box you’ll want to exit the sub. If the user simply clicked “Okay” before entering the data you’ll want to offer them the chance to enter the data.

The initial error will be a “type mismatch” if the variable for the input data is dimensioned as an integer. Instead, use a string variable, then latter pass the value to an integer variable if needed.

Here is the error capture code for an input box where the user tells how many folders an overfilled folder should be divided into.

strSplit = InputBox("Split Folder " & intFolder & " into how many folders?")

'If the user fails to specify how many folders the folder is to be split into display an error

'message and re-present the input box.

If strSplit = "" Then

     'Since VBA input boxes do not differentiate between "Cancel", "Exit", and "Okay (with no data entered)"

     'the error capture needs a mechanism to allow the user to exit or cancel if they wish or enter data

     'if they had forgotten to enter it.

     If MsgBox("You have not specified the number of folders or have clicked 'Cancel'" & vbNewLine & "Do           you wish to Cancel?", vbYesNo) = vbYes Then

          'The user meant to exit or cancel, so exit the sub

          Exit Sub

     Else:

          'The user did not mean to exit or cancel, so re-present the input box.

          Call cmdSave_Click

     End If

Else:

     intSplit = strSplit

     [rest of the code to split the folder]

End If

Capturing a list box error in MS Access.

There are several places in KIDB where the user can select from a list box and then click a button to perform an action on the selection. A good example of this is you can select a box from the list box on the Box Location form and click a button to move it, that is to assign it a new address in the stacks. A potential problem with this is if someone clicks the button without first selecting a row in the list box, which chokes the code. Here is how I capture that error in the On Click event of the Move Box button:

Private Sub cmdMoveBox_Click()

On Error GoTo Err_cmdMoveBox_Click

'Sub allows user to move a box from one address to another.

'Limits ability to move boxes to users with a permission level of 3 or higher.

     Dim strDocName As String

     Dim strLinkCriteria As String

     Dim lst As Access.ListBox

     Set lst = Me.BoxList

     Dim intUser As Integer

     strDocName = "frmBoxMove"

     'Check for user authorization to delete boxes.

     intUser = Nz(DLookup("[Level]", "tluUsers", "[UserID]= '" & Environ("username") & "'"))

     If intUser > 2 Then

          'Check to see if any boxes have been selected.

          If lst.ItemsSelected.Count = 0 Then

               'Let the user know what is wrong.

               MsgBox "No box has been selected to move."

               'Exit the sub.

               Exit Sub

          'Check that no more than 1 box has been selected.

          ElseIf lst.ItemsSelected.Count > 1 Then

               'Let the user know what is wrong.

               MsgBox "Please select only one box to move."

               'Clear the listbox selections.

               lst.SetFocus

               lst.ListIndex = -1

               'Exit the sub.

               Exit Sub

          'If only one box is selected open the move box form.

          Else:

               strLinkCriteria = "[BoxID]=" & Me!BoxList.Column(11)

               DoCmd.OpenForm strDocName, , , strLinkCriteria

          End If

     Else:

          MsgBox "Sorry, you are not authorized to move boxes."

     End If

Exit_cmdMoveBox_Click:

Exit Sub

Err_cmdMoveBox_Click:

MsgBox Err.Description

Resume Exit_cmdMoveBox_Click

End Sub