Drewslair.com - Microsoft Access for Beginners: Advanced Form Design

Microsoft Access for Beginners - Additional Information

Advanced Form Design

Custom Menus and Tools

When using the sample database included with this series, you might notice that the tool and menu bars at the top of the screen look different than normal. This is because in addition to designing forms for the database, I also designed a custom menu and toolbar as part of the interface. If you right-click anywhere on these bars, the popup menu should contain a Customize command that will take you to the menu screens where you can pick and choose which buttons and menu options you want to show to users. This can be very useful both for hiding potentially dangerous options from inexperienced users and providing custom functions that will automate various tasks in your application.

Office 2007 introduces the Ribbon which mostly replaces the menu and toolbars in Access. Access still has a Quick Access toolbar, however, which can be customized to include the controls you need. When using the demonstration database, you'll find the custom menu and toolbar shown under the Add-Ins menu.

Access contains a number of ready-made toolbars and you can also create your own.

(Click on the image to see the equivalent screen from Access 2007.)

Under the Toolbars tab in the Customize window, you'll see a long list of specialized toolbars that can be turned on or off using the checkboxes next to them. At the bottom of the list are the custom bars that I designed.

Custom utility bars are part of the application and are not dependent on the forms. You can specify different menu and tool bars for different forms and reports. In this way, you could design separate custom bars based on the needs and functions of specific sections of your program. If you import a form from one database file to another, you will see an option to import whatever custom utility bars are referenced by it. If you don't want to specify a menu bar on every form you create, you can also name one menu bar as the default in the application startup options and it will appear throughout the database, even when viewing the database window.

A third type of custom menu is a popup menu. This is the menu that appears when you right-click on a form or another object. Again, this menu can be customized to meet the specific needs of the application.

My personal use of custom utility bars is mostly limited to removing unneeded or confusing options but as you work with Access, you may find the ability to customize these menus to be a powerful part of application design that you wouldn't want to be without. It is one more tool that you can use to make the application your own.

Form Events

In addition to properties that affect appearance and other aspects, forms and other objects also have events that are triggered by user actions or other events. These include events such as On Current (the event that runs every time the user moves to a new record) and On Open (run when the form is first opened). The events on the form's property list can hold VBA code or macro references that can be used to change other form properties or perform various actions when a specific event occurs. A number of events can and often do occur in rapid succession in what would appear to the user to be only one event. For example, when you click on the button on the Welcome screen that opens the Employee form, it's Click event is triggered. This event contains code that opens the Employee form. When the form is opened, the following events occur between the time the command is issued to open the form and the time you see the form on the screen:

Open →→ Load →→ Resize →→ Activate →→ Current

You can see all of these events listed on the form's property sheet where you can get more information by clicking on any of them and pressing F1. For right now, just understand that these events are always going on in the background and Access makes them available to give you precise control over how your application operates. For this reason, Access programming is referred to as event-driven.

Tab Order

The tab order screen showing the controls on the main Employee form.

One of the properties of your form that determines it's behavior is the Tab Order. This one can be seen in design view by clicking on the View menu and then Tab Order. This shows the order in which focus will move between the controls when you press the Tab key. The property sheet for each control contains the Tab Index and Tab Stop properties which determine if and when a control will receive the focus.

Another short example of how I use form events to solve a problem is found on the tab control in the center of the Employee Data form. Each page of a tab control has its own independent Tab Order and the tab control itself has a place in the form's Tab Order. This means that at some point, the Tab key will take you to the first available page on the tab control and then the Tab Order for that page will go into effect. The only problem is that the page's Tab Order will not normally take you from one page to another. Once you reach the last control on the tabbed page, pressing the Tab key will move you away from the tab control and back to the next field on the main form. Pressing CTRL-TAB will move you between the pages of the tab control but it would be nice if there was a smooth flow between the pages.

In design mode, you'll notice a small blank rectangle on each of the tab pages except for the last one. These are actually small command buttons on which the Visible property is set to False so that the user doesn't see them and can't click on them. They are the last item in the Tab Order of their respective pages. If you look at the events for these buttons, you'll see that the Got Focus event for each has a single line of code that moves the focus to the first control on the next page.

Private Sub cmdTab1_GotFocus()
'Move to next page of tab control when button receives the focus
Me.Contact.SetFocus
End Sub

In the code above, the GotFocus event of one of these buttons is fired when the user presses the Tab key and the button receives the focus. The code passes the focus to the first control on the next page and the tab control appears to switch smoothly between pages.

Other examples of event-driven features:

On the Additional page of the tab control, the bottom half of the page contains a subform for information on past employees. This form is only visible if the Current Employee field on the first page is clear. When the user updates this checkbox or moves to a record for a previous employee, the form becomes visible and focus automatically changes to the subform. Look at the code under the Current Employee field's After Update event and the form's On Current event to see how this is done.
The program manages and displays the appropriate forms by using the Close and Exit commands. The Welcome form also uses the Tag property to identify it as a background form to be displayed if no other work forms are open.

Advanced Features

As I've indicated, Access offers a lot of flexibility in its design features so that you can build a program that will meet your exact needs. The features that I've described here only scratch the surface of what can be done with forms and other objects.

The final item I want to mention is the employee photo field on the Employee Data form. Storing photos or other images in Access is a challenge because of the way the program stores the image information. Access has an OLE Object field type that will store any kind of file, including other Office documents, Acrobat files and images. For binary files like Word and Acrobat, this works great. For image files, however, Access stores the image in an uncompressed format. In other words, an image that takes up less than 50 KB in compressed JPEG format on disk takes up well over a megabyte when inserted into the Access table. That's over a megabyte for every picture stored. The database file would get huge.

Because of the amount of space that images take up when stored in an Access

A common solution that I use here is to store images on disk instead and store the path information of the image file in the database. The photo field that you see on the form is actually an unbound object field and the photo location field above it is bound to the text field in tblPersonnel that stores the path information. The Show Employee Pictures button under the object field uses VBA code to switch between showing and hiding the photos. The field displays the image without taking up room in the database.

The ability of Access to link to information as well as store it is an important feature of the program that's not limited to object fields. An Access application can link to tables in another Access database or even on a server database such as Microsoft SQL Server to provide data for editing and analysis. Most of the applications that I've designed have been split into a front-end and back-end where the back-end contains all of the tables and the front-end contains everything else along with links to the tables in the back-end. Among other things, this separates the data from the programming and enables easier updating of the program. An Access application can also act as a front-end for network databases like SQL Server. Conversely, other programming languages such as Visual Basic can be used to build a front-end for a set of Access tables.

Working Out the Bugs

It's a rare occasion when anything I'm designing works exactly right the first time. At the very least, some tweaks are needed here and there to get the right result. Sometimes the process can be much more involved and can take hours before a form or function works the way I need it to. During that time, I'll be making changes here and there, consulting technical forums and help files and almost always learning something I didn't know before. Another rarity is a project where I don't learn at least two or three new and fun things I can do with Access. Discovery is half the fun, after all.

The more time you spend testing and debugging a program, the better. Every hour that you spend testing means at least one less potentially embarrassing problem for the user to discover. Entering pages of test data and coming up with test scenarios can be tedious and sometimes Access can seem to have its own obstinate personality when that one function you know should work doesn't. Debugging is essential, however, if the interfaces you design are going to meet the user's needs.

Of course, beyond the process of debugging, there's the time that you spend looking for ways in which to improve the application. One saying that's stuck with me over the years is "That which is 'good enough' seldom is." Your willingness to look for improvements in your programs for the benefit of the users, even when you don't believe they're strictly necessary, makes the difference between designing a barely adequate program that eventually has to be replaced at great expense by a professional programmer and providing a great solution that is praised by the people who see and use it.

Return to Series Index