databasedev.co.uk - database solutions and downloads for microsoft access

A Standard Set of Form Navigation Buttons In Microsoft Access 2010

What’s this Article About?

You can end up reinventing a lot of wheels in Access if you’re not careful. This article shows you how to reach Access form Nirvana: a situation where you have a set of navigation buttons at the bottom of each form, which you can freely copy and paste without worrying about whether the code behind them will work.

The buttons at the bottom look pretty – but will they still work if you copy and paste them to another form?

Creating the Original Buttons

I’m going to assume, gentle reader, that you know a reasonable amount about creating Access forms. You should be able to knock up the form shown above without too much problem. I’ve changed quite a few form properties to make it look neater:

Here I’ve taken off a lot of the clutter – so no scroll bars, dividing lines or navigation buttons.

For the buttons themselves, you can set pictures easily enough:

Make sure you give your buttons good names (this one is called cmdFirst), and if required click on the Picture property to assign a picture to each button. The picture for this one is the one called GotoFirst in the list which appear.

Now that you’ve got a reasonable-looking form, it’s time to make it work! I’m using VBA rather than Access macros for this because it’s more powerful, more flexible and supported by other Microsoft software applications.

Coding the Buttons

For each button you can attach code to it in the button’s event properties:

You can click on the build button shown to attach code to the button’s click event (so that when your users click on the button, something will happen)

For the buttons to go to the first and last records (and the one to create a new record) the code is fairly straightforward:

Private Sub cmdClose_Click()

    'button to close down the current form
    '(prompting to save changes)
    DoCmd.Close acForm, Me.Name, acSavePrompt
    
End Sub
Private Sub cmdFirst_Click()

    'button to go to the first record
    Me.AllowAdditions = False
    DoCmd.RunCommand acCmdRecordsGoToFirst
    
End Sub
Private Sub cmdLast_Click()

    'button to go to the last record
    Me.AllowAdditions = False
    DoCmd.RunCommand acCmdRecordsGoToLast
    
End Sub
Private Sub cmdNew_Click()

    'create new record
    Me.AllowAdditions = True
    DoCmd.RunCommand acCmdRecordsGoToNew
    
End Sub

(I’ll take more about the AllowAdditions property of the form in a moment).

For the buttons to go to the previous and last record we need to build in error-trapping, since if you’re on the first record and try to go to the previous one Access generates an error. Here’s what the slightly more complicated routines could look like:

Private Sub cmdPrevious_Click()

    'if can't go to previous record, assume on first
    On Error GoTo OnFirstRecord
    
    'button to go to the previous record
    Me.AllowAdditions = False
    DoCmd.RunCommand acCmdRecordsGoToPrevious
    
    'if get here, everything was OK - can exit
    GoTo wayout
    
OnFirstRecord:

    MsgBox "You're already on the first record!"
    
wayout:

    'reset error trapping to default
    On Error GoTo 0
    
End Sub
Private Sub cmdNext_Click()

    'if can't go to next record, assume on last
    On Error GoTo OnNextRecord
    
    'button to go to the previous record
    Me.AllowAdditions = False
    DoCmd.RunCommand acCmdRecordsGoToNext
    
    'if get here, everything was OK - can exit
    GoTo wayout
    
OnNextRecord:

    MsgBox "You're already on the last record!"
    
wayout:

    'reset error trapping to default
    On Error GoTo 0
    
End Sub

What each routine essentially does is this:

• If you’re already on the first record and try to go to the previous one (or on the last record and try to go to the next one), the code will jump to the OnNextRecord label. Access will then display a message, and then reset the error-handling code to the default setting
• Otherwise, the code will move you to a different record and then jump to the wayout label, where the subroutine will reset the error-handling to the default setting and exit.

The reason I keep resetting the AllowAdditions property of the form is this. When a user is on the last record of a form in Access and tries to go to the next one, what will normally happen is that Access will create a new record and display that. To avoid this behaviour, I allow additions only when a user has specifically clicked on the button to add a new record.

All of the above is all well and good, but it doesn’t give us buttons that we can copy between forms, since the code is embedded in the form. To achieve this, it’s time to create a module.

Creating a Separate Module to Hold our Navigation Routines

You should now create a separate module (you can do this within Access or within the VBA code window) and perhaps rename it:

Here I’ve changed the module name from Module1 to NavigationCode, using the Properties window

You can now use more or less exactly the same code, subject to three changes:

1. You’ll need to remove any references to Me. Me refers to the current object (in this case, the form). If you’re writing code in a separate module, there is no current object, and so Me won’t work
2. Crucially, you’ll need to make all of your Sub procedures into Function ones. The reason for this isn’t that you’re writing functions; just that you won’t be able to assign the code to your buttons otherwise
3. You’ll also need to change all of your procedures from Private (only the form can use them) to Public (any VBA in the current database can call them)

Here’s what one of your routines will now look like (the others will be similarly modified). I’ve renamed the procedures to make them easier to understand. Before it looked like this:

Private Sub cmdFirst_Click()

    'button to go to the first record
    Me.AllowAdditions = False
    DoCmd.RunCommand acCmdRecordsGoToFirst
    
End Sub

The new version will now look like this:

Public Function GotoFirst()

    'remove ability to add records
    Screen.ActiveForm.AllowAdditions = False
    
    'go to the first record!
    DoCmd.RunCommand acCmdRecordsGoToFirst
    
End Function

Notice that I’m using Screen.ActiveForm to pick up on the form you’re using.

Assigning the Functions to your Buttons

The final part of the jigsaw is to make sure that the buttons run the code you’ve painstakingly created! To to this, just start typing the function name into the event-handler for each button:

You can start typing the name of a function after the = sign, and Access will help you type it in

The final event-handler should read like this:

Clicking on this button will run the GotoFirst function that you’ve created in a separate module

The beauty of all of this is that you can now copy and paste your buttons, and they will work in any form without further modification!