Access Forms Masterclass #1: Custom Navigation Buttons||||||||||| Access Forms Masterclasses Access Forms Masterclass #1 Custom Navigation Buttons Published: 1 August 2013 Author: Martin Green Screenshots: Access 2010, Windows 7 For Access Versions: 2007, 2010, 2013 Why Add Custom Navigation Buttons? Unless you specify otherwise, each Access form comes with its own set of built-in navigation buttons, located in the lower left corner of the form ( Fig. 1) so why bother creating your own custom navigation buttons? Access Forms Masterclass #1. Author: Martin Green Screenshots: Access 2010, Windows 7. Much of the code associated with Access forms takes the form of Event. Deactivate and Activate Events In addition to moving between records, a user can switch between forms; when you click off of a form, the Deactivate event fires; when you click a form, the Activate event fires. These events are not commonly programmed, and you will find that if you try to manage window focus in these events. Apr 23, 2007. Experts Exchange > Questions > Access doesn't fire the Form_Activate() event correctly!!! Now when you execute the Form the Text1 box now shows 7, which means that the Form_Activate() event never fired???!!! There is nothing I've found that indicates that a Form marked as 'PopUp. 1 A form's built-in Navigation Buttons. When building applications for other people to use you soon learn that you should take nothing for granted and, most importantly, you should never assume any particular level of knowledge or expertise on the part of the user. Of course, you and I know what those little arrows at the bottom of a form are for and how to use them but what about the people who are going to use the database? Adding some clearly marked buttons might help them navigate through their records. I have found that even knowledgeable users find custom buttons useful because on modern high resolution computer screens the built-in navigation buttons appear very small and can be difficult to see and operate. I usually add my own custom navigation buttons to a form ( Fig. 2) simply because it makes things easier and more convenient for the user. Whether or not you choose to keep the built-in navigation buttons is entirely up to you. 2 Custom Navigation Buttons In this Masterclass you will learn how to add a set of custom navigation buttons to a form and write the VBA code to activate them. You can optionally remove the built-in navigation buttons and add refinements such as Tool Tips and code to disable the buttons when they are not needed. You can download a copy of the database containing the completed exercise used in this Masterclass as well as a printable PDF of this Masterclass in the section at the bottom of this page. View the video version of this Masterclass in my section or on my YouTube channel. Step 1: Add a Footer to the Form First, you need to decide where to put your navigation buttons. I like to put them in the form's Footer section. The footer is always visible in the form's window so, if the form is too big for the window, the user won't have to scroll to find the navigation buttons. When you create an Access form it doesn't have a footer by default so you have to add one. In the form's Design View right-click in the background of the form (the Detail area) and choose Form Header/Footer from the context menu ( Fig. 3 Add a Footer to the form. A Form Footer section appears at the bottom of the form ( Fig. Notice that a Form Header section is also created at the top of the form. 4 A Footer section is added to the form. If you don't need a header you can remove it by reducing its height to zero. To do this point at the upper edge of the bar labelled Detail so that the mouse pointer changes to a cross with a double headed vertical arrow ( Fig. 5) then drag the bar up as far as it will go. This hides the Header section from view ( Fig. 5 Adjust the height of the Header Section Fig. 6 Hiding the Header by reducing its height to zero. If at any time you want to restore the Header just reverse the process. An alternative way to do this is to specify the Height property of the Header in the Property Sheet. Click on the bar labelled Form Header then go to the Property Sheet (click the Property Sheet button on the Design tab of the Ribbon or press [Alt]+[Enter] to open the Property Sheet if it is not already visible). Enter the required size in the Height property on the Format tab of the Property Sheet. Enter a zero to hide the Header or a number to specify the required size. Step 2: Draw the Command Buttons Having decided where to place them, the next step is to draw the required number of buttons on the form. Click the Button tool on the Design tab of the Ribbon ( Fig. 7) then click on the form Footer approximately where you want the button to appear. I normally work with the Control Wizards tool switched off because I prefer to write my own code rather than let Access do it. If you have the Control Wizards tool switched on the Command Button Wizard will appear when you click on the footer. You could use the wizard to achieve the task in hand but since the point of this exercise is to teach you how to do these things yourself you can dismiss the wizard by clicking its Cancel button. NOTE: If you want to turn off Control Wizards permanently (you can turn them on again at any time) expand the Controls group on the Design tab of the Ribbon and de-select the Use Control Wizards option. 7 Use the Button tool to add a Command Button. When you clicked on the footer Access created a Command Button and gave it a default name and caption. In this example the button was given the name and caption Command16 ( Fig. Whenever a control (an object on a form is called a Control) is added to a form Access automatically names it and, if appropriate, captions or labels it with a name and sequential number. You will give the button a sensible name and a meaningful caption later. First, you need to resize the button and position it accurately. Fig.8 The Button tool creates a new Command Button. See that the when the Command Button control is selected it has a highlighted border with dots at the corners and mid-way along its sides. These are handles for resizing or moving the control using the mouse. Drag the small dots to change the size of the control. Drag the large dot at the upper left corner of the control to move it. I prefer to use either the keyboard or the Property Sheet to accurately size and position controls. To use the keyboard to move a control, first click on the control to select it then press the [Left Arrow], [Right Arrow], [Up Arrow] or [Down Arrow] keys. Each button press moves the control one grid unit. For finer movements hold down the [Control] key whilst pressing the arrow key. This moves the control one quarter of a grid unit for each key press and is useful for accurate placement of items on the form. To use the keyboard to change the size of a control, hold down the [Shift] key whilst pressing the arrow key. [Shift]+[Left Arrow] reduces the width of the control whilst [Shift]+[Right Arrow] increases the width. Similarly, [Shift]+[Up Arrow] reduces the height of a control whilst [Shift]+[Down Arrow] increases the height. Hold down the [Control] key as well to achieve fine changes. Alternatively you can do it all using the Property Sheet. That's my preferred method. Select the Command Button control and go to the Format tab of the Property Sheet. Because I'm English I'm going to use centimetres but if you use inches just enter the nearest equivalent measurement that suits you. With the command button selected, go to the Property Sheet and enter the following property values ( Table 1): Table 1: Command Button 'Back' Properties Property Tab Value Width: Format 3 cm Height: Format 1 cm Top: Format 0.5 cm Left: Format 1 cm There is no need to add 'cm' or 'in' as Access will do this automatically. You might also notice that, particularly if using metric measurements, Access changes some of the dimensions. For example 0.5 gets changes to 0.501. Don't worry about this, it's just Access converting measurements behind the scenes. Give the command button a sensible caption by entering Top: Format 0.5 cm Left: Format 4 cm Select the second of the new buttons and go to the Property Sheet and enter the following property values ( Table 3): Table 3: Command Button 'New' Properties Property Tab Value Name: Other cmdNew Caption: Format New Top: Format 0.5 cm Left: Format 7 cm Depending on which method you used to create the new buttons, you might find that the Footer section increased in height to accommodate them. If necessary adjust the height of the Footer section by pointing at its bottom edge so that the mouse pointer changes to a cross with a double headed vertical arrow ( Fig. 10) then drag the border upwards to change the Footer to the desired size. If you prefer, you can select the Footer by clicking the bar labelled Form Footer before entering the desired value in the Height property on the Property Sheet. 10 Resizing the Footer section. Before proceeding, switch the form into Form View and take a look at your new buttons ( Fig. If you want to change their size or position you can return to Design View to make any necessary adjustments. Fig 11 The new Navigation Buttons in Form View. Step 3: Additional Design Refinements Add Tool Tips Users might find it helpful to have a little extra information about what the buttons do. You can easily provide an additional hint by creating a Control Tip. This is a Tool Tip that appears when the user points their mouse at a control ( Fig. Enter some text in the ControlTip Text property (located on the Other tab of the Property Sheet) of each button. For example you might enter Go To Previous Record for the cmdBack button, Go To Next Record for the cmdNext button and Create New Record for the cmdNew button. 12 A tool Tip helps users understand a control's function. Check the Tab Order A form's Tab Order is often overlooked by form designers. Many users navigate around a form by using their [Tab] key. This should take the user from control to control in a logical sequence. A control's position in the Tab Order is defined by the order in which that particular control was created on the form so, unless you have created each control in the precise order in which the user would be expected to visit them, the Tab Order will be incorrect. This can be very frustrating for someone who is used to using the [Tab] key to navigate around a form. It is very simple to check the Tab Order and, if necessary, correct it. Each part of the form has its own Tab Order. To check the Tab Order of your new buttons, right-click on the background of the Footer section and choose Tab Order from the context menu ( Fig. Alternatively, select the Footer by clicking on the bar marked Form Footer and click the Tab Order button on the Design tab of the Ribbon. 13 Open the Tab Order dialog. The Tab Order dialog ( Fig. 14) shows the current order of controls in the selected Tab Order. To change the order, select a list item by clicking the grey button next to its name. Release the mouse then drag the selected item up or down the list to create the desired order. You can select multiple items and move several at once if required. 14 The Tab Order dialog. You can use the Auto Order command to have Access automatically create a tab order. If there is more than one column of controls on a form the automatic tab order will visit items left-to-right before moving down. You should always check the Tab Order after making changes to a form. A simple change such as converting a Text Box control to a Combo Box will change its position within the Tab Order, moving it to the end of the order even though its position on the form has not changed. Add First and Last Record Buttons I haven't included First and Last record buttons because I seldom use them myself, but it is a simple matter to add them if you wish ( Fig. 15 First and Last buttons can be added. Proceed exactly as for the other buttons, supplying appropriate names, captions and, if you are using them, tool tips for the command buttons. Remember to include them in the correct Tab Order. Remove the Built-In Navigation Buttons Now that you have your own custom navigation buttons you might like to remove the built-in ones. It's a simple process. In Form Design view, open the Property Sheet and select Form from the drop-down list at the top. Then, on the Format tab change the Navigation Buttons property to No. This causes the navigation bar to be hidden on that particular form ( Fig. 16 Hiding the form's built-in Navigation Buttons. Bear in mind that removing the built-in navigation buttons also removes the Record Count and Search boxes, so you might want to keep them in addition to your own. There are alternative methods for searching and in the next Masterclass I show you how to display your own custom Record Counter (). Step 4: Coding the Navigation Buttons The buttons don't work yet. To activate them you need to write some VBA code. The code for each button will take the form of an Event Procedure, that is a procedure (commonly called a Sub or VBA Macro) that will run by itself when a particular event happens. In the case of our buttons that will be the Click event, which fires when the user clicks a button or presses their [Enter] key when a button is selected. Coding the Back Button In Form Design view select the cmdBack button and go to the Event tab of the Property Sheet. Click in the text box next to On Click then click the Build button ( []) to open the Choose Builder dialog then select Code Builder ( Fig. 17) and click OK. 17 The Choose Builder dialog. This takes you into the Visual Basic Editor where Access has created an empty event procedure for the cmdBack_Click event, ready for you to add your code. When writing VBA code you should always consider what might go wrong and cause an error when the code runs. If, for example, the user clicks the Back button when they are already on the first record this will cause the code to 'crash'. So, the first thing you need to do is add a simple error handler telling Access to ignore an error if one should occur when the user clicks the button. (NOTE: Error handlers usually need to be more sophisticated than this but in this case it will be safe to simply ignore the error.) Enter the code as follows: • Place the cursor between the lines beginning Private Sub and End Sub and press [Tab] to indent your typing by one tab-space. Type On Error Resume Next then press [Enter] to create a new line. • Type DoCmd followed by a dot (.). When you type the dot Access shows you a list of all the available relevant commands. • Scroll down the list and choose GoToRecord ( HINT: Start typing the text you need and the list will automatically scroll to it.). Either double-click the list item or select it and press [Tab] to add it to your code. • Type a [Space]. Access shows a list of possible objects, but since we are referring to the current form we don't need to specify this so skip this piece of information by typing a comma (,). • Access now wants to know the name of the object but, again, since we are referring to the current form we don't need to specify this, so skip this piece of information by typing another comma (,). • Finally we get a list allowing us to specify which record we want to go to. Double-click acPrevious to choose it. Your finished code should look like this ( Listing 1). Listing 1: Private Sub cmdBack_Click() On Error Resume Next DoCmd.GoToRecord,, acPrevious End Sub Before testing your code, first check your typing then open the Visual Basic Editor's Debug menu and choose Compile (the name of your database is shown). Compiling the code checks for any errors you might have missed. If everything is OK (the Visual Basic Editor will tell you if it finds a problem) then click the Save button. This is an important step because, if you test code without saving your database first and an error causes Access to crash, you might lose some of your work. Return to your database, put the form into Form View and test the Back button. Clicking it should move you to the previous record unless you are on the first record, in which case nothing should happen. Coding the Next Button Proceed exactly as described for the Back button. Create a Click event procedure for the cmdNext button and enter the code, this time specifying acNext. Your code should look like this ( Listing 2): Listing 2: Private Sub cmdNext_Click() On Error Resume Next DoCmd.GoToRecord,, acNext End Sub Compile, save and test the code as before. Coding the New Button Proceed exactly as described for the Back button. Create a Click event procedure for the cmdNew button and enter the code, this time specifying acNewRec. Your code should look like this ( Listing 3): Listing 3: Private Sub cmdNew_Click() On Error Resume Next DoCmd.GoToRecord,, acNewRec End Sub Compile, save and test the code as before. Step 5: Additional Code Refinements I suggested that you added a simple error handler in case the user pressed a button and gave Access a command that it could not complete. The error handler dealt with any possible error by ignoring it. But it is always good practice in programming to try to prevent errors happening in the first place. Often errors can't be anticipated so error handlers are always a good idea, but in this case we can prevent errors from happening by disabling those buttons whose function is not relevant at the time. We can use the form's CurrentRecord property, which returns the index number of the record within the current recordset, to know if we are on the first record (the CurrentRecord property will have a value of 1). The RecordCount property of the form's recordset can be used to find out how many records there are and can be compared with the CurrentRecord to determine if we are on the last record or a new record (if the value of the CurrentRecord property is equal to the value of the RecordCount property then we are on the last record). The form's NewRecord property can be used to determine if we are on a new record. It returns True for a new record and False for an existing record. With this information we can change the Enabled property of the buttons to True or False to enable or disable the appropriate buttons so there is no chance of an error occurring. To achieve this we make use of the form's Current event. This event fires whenever a new record is displayed. This happens when the form opens and when the user moves from record to record, or asks for a new record. To create the event procedure select the form itself by choosing Form from the drop-down list at the top of the Property Sheet.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2018
Categories |