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?
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.
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:
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.
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:
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.
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!