.jpg)
Microsoft Access for Beginners - Part IV
Designing the Forms
Contents
Introduction
A Word about VBA
Form Design Toolbar
The Welcome Screen
Form Design Basics
Managing your Data
Additional Controls
Conclusion
Introduction
In Parts I and I!, I showed you how to build the foundation of your database application with properly designed and related tables. Then I showed you how queries can read and manipulate the data you've stored in your tables. Now you need a way for the average user to quickly access and edit the data when needed. As wonderful as a properly designed set of tables and queries is, the database window does not provide a great interface for someone who knows little about the structure of your database or even for you on a regular basis.
The word 'interface' is important because that is what you are designing when you create a data entry form. A well-designed application interface does the following:
- Provides easy access to the tools and functions featured by the program
- Simplifies data entry and retrieval with fields that anticipate, where possible, the values to be entered
- Helps to ensure correct data entry and protects existing data by providing templates, masks and validation rules that require items such as dates to be entered correctly
- Anticipates user error where possible and prevents or corrects for it
- Offers as much compatibility as possible with accepted design standards and user expectations so as to minimize the learning curve required to use the program
- Conforms to the user's natural workflow so that data can easily be entered and updated in the relevant order
This represents the ideal that you can work toward as you gain experience with Access and other programming tools. When you design an interface with these goals in mind, your programs are more likely to be readily accepted by the users and you will have an easier time training people on how to use them.
I design my applications so that the average user will never have to directly open a table or even view the database window. All work should be completed through the forms and menus. This reduces errors and the chance of a necessary form or query being accidentally deleted or records being improperly deleted from a table. It's not necessary to completely prevent access to the database window except in the most secure applications as some users may be qualified to add their own queries and tools. You should, however, do everything you can to minimize the confusion for those who aren't.
A Word about VBA
For me, any discussion of forms and includes references to Visual Basic for Applications (VBA), the programming language that Access uses to provide extra functionality behind forms and reports. In this article and the supplemental article on advanced form design, I make a number of references to the VBA code that I've attached to the form objects in the sample database. You don't need any actual VBA knowledge to benefit from this chapter beyond the understanding that code can be attached to objects to carry out tasks when a button is pressed or other events are triggered.
Sample Files
If you have not already done so, please download the demonstration files:
Demo.zip - Access demonstration database and original Excel spreadsheet (Requires Access and Excel 2000 or above)
Form Design Basics
Data Sources
Creating forms from scratch is about two things: learning how to use the different controls available and how to link the data to them. Every form has a Record Source property which determines where the data comes from. This can be a table, a query or a SQL statement. The figure below shows part of the properties sheet for the main employee form from the demo database with the Record Source holding a SQL statement.
The record source of a form specifies where the data for the form comes from. It can be a SQL statement or a separate query or table.
Clicking on the Build button (
) on the right of the field will bring up the query window
to show you the query behind the form. The text for this query is stored
completely within the form rather than as a separate query in the database.
The Record Source can also hold the name of a table or a separate query that will function as the data source. If it does, then all of the records returned from that source will be available to the form. Using a SQL statement as shown above or a separate query can be one way to limit the number of records shown. The form works with the record source to determine which record is currently displayed and to manage the transfer of data between that record and the controls on the form.
Another word for the collection of records made available to the form is a recordset. This recordset is sorted according to the specifications of the query or the order in which the records are held in the table.
The controls you place on the form such as text boxes, combo boxes, radio buttons, etc. access individual fields from the recordset. Each control has a Control Source property which determines which field it will work with. In the figure below, the properties sheet for the LastName text box is shown with the Control Source set to the LastName field provided by the record source shown above.
Each time the form displays a new record, this control is updated to display the content of the LastName field from that record.
Once you understand these two properties, the rest is about placement of controls and fine-tuning of their properties.
Creating the Form
Starting out with a new, blank form is as simple as selecting Insert / Form from the main menu or selecting New in the Forms section of the database window.
All of the options in the New Form dialog except for the first one are wizards that will create the new form and populate it with controls or guide you through the steps for creating the requested form type. The selection box at the bottom presents a list of all of the tables and queries saved to the database. I've selected the main employee table here. Clicking the OK button with these options results in a screen that looks something like this:
Notice the secondary window in the above screenshot that shows the list of fields available in the data source. You can click and drag these fields onto the form to automatically add controls to the form. In the screenshot below, I've added five of the main fields to the form and arranged them. Notice that the form created a combo box for the Department field which uses a lookup.
When I switch from Design to Form View, the form automatically loads the first record from the table it's using as a data source and displays the requested fields.
At the bottom of the form is the record selection toolbar which enables you to move through the available records. As with any list, the recordset has a beginning and an end. On this form, moving beyond the last record will create a new blank record and the function of the controls will change. Instead of reading and editing the data, anything typed into the controls will be written to the new record. Depending on the needs of your application, the form can be set to simply display read-only data and to disallow the creation of new records.
Experimenting with the other form properties available in Design View will also help you become familiar with the many way in which you can get the most out of your forms.
Working with Properties
As you've seen by now, every object in Access has a list of properties that you can set to customize your application. The form designer is especially flexible in the ways you can adjust the look and feel of your program. If the properties screen is not already visible, pressing F4 or right-clicking anywhere on the form and selecting Properties from the menu will bring up the list.
One of the things to keep in mind about form properties is that a form has a number of sub-sections including the main detail area where I placed the controls shown above as well as a header and footer section. This is in addition to all the controls you place and a main list of properties that apply to the entire form as a whole.
In the above screenshot, notice the combo box at the top of the properties sheet. This control contains a list of all of the form elements including the controls that you create. Here, it's set to the properties for the form itself. Each element has a different list of property settings available to it. As you select different controls or sections with this box, you will notice that they are selected within the form design window as well. This can be an easy way to find a specific control on an intricate form.
There are far too many properties available to detail here but you can get more information on any of them by placing the cursor in one of the properties fields and pressing F1 for help. The more time you spend getting familiar with them, the more you will be able to access the power behind Access form design.
Form Design Toolbar
One of the standard toolbars that you'll use quite a bit when working with Access is the Form Design Toolbar, sometimes called the Toolbox. If this bar doesn't appear automatically when you enter form design mode, you can view it by selecting Toolbars on the View menu. This toolbar includes icons for all of the form controls that I mention in this article plus a few more. It also has a Control Wizards option.
For the beginner, command buttons and other form controls are one of the few exceptions that I make to what I said in the last chapter about not using the wizards. If you have the Control Wizards activated, a dialog box will appear as soon as you create a form control and guide you through setting up the control to perform any one of a limited number of actions. The wizard will write all the code necessary to carry out the action needed. For small controls such as buttons, it's fine to use these when first designing forms until you become comfortable designing macros and writing code. It is still important, however, to learn how to design without them for finer control.
The Welcome Screen
Forms can serve other purposes besides data entry and when you first open the database, you'll see one example of this. The Personnel Database main menu is a form that serves as a welcome screen for the application. Right now, it only contains a few command buttons for navigation but it could also feature a company logo and additional information such as copyright notices and instructions. In the application startup options (accessed by selecting Startup from the Tools menu), you can specify a form to load as soon as the application starts. This enables you to define a welcome screen like this one or run code that is attached to a form to carry out almost any task at startup.
The figure above shows the startup settings screen for the sample application. This dialog holds settings unique to the application you're designing. I've set an application title that will display at the very top of the screen while the application is loaded. I've also set the Welcome form as the form to be displayed at startup. The option directly below this, Display Database Window, specifies if the window will be shown or hidden. I've set it to be hidden but you can see it whenever you need to by pressing F11.
Sometimes you might want to override these settings if you're making changes to the database. If you hold down the SHIFT key as you're opening the database, this will bypass all of these properties and give you immediate access to the database window for the application. There is also a way to disable the SHIFT-key bypass for especially secure applications but this is a subject for another chapter and something that should obviously be used with care.
Managing your Data
If you click on the Manage Personnel Data option on the Welcome screen, the program brings up the Employee Data screen where you can view the sample data included in the database. As you look through this form, you'll see fields from many of the tables that were mentioned in the previous chapters. In addition to the main Personnel table, there's contact, department and and skills information for each employee. This is accomplished in two different ways.
First, instead of being based on a single table, the form is based on a query. If you open the form in design view, view the properties of the form and then view the Record Source property, you'll see it contains a SELECT statement. This is a query on six different tables with tblPersonnel being the table that links them all together. In Part I, I mentioned the importance of limiting the fields in each table to those directly relevant to the table's subject. Here, you can see how the form can be used to bring all of that data back together in a coherent way.
Another thing you may notice about all the tables in the record source is that they are all based on one-to-one relationships with tblPersonnel. None of these tables contain more than one record for each employee in the system which is why they can all be included in this form which shows one 'record' for each employee. There are other tables, however, that contain multiple records for each employee such as tblSkills and tblNotes. To include these, we need another method of sharing the form.
A subform is a form that can reside as part of another form. These parent and child forms use the same relationship as the tables they are based on. In this case, the Skills and Notes subforms are related to the main form by a one-to-many relationship between tblPersonnel and their respective tables. You define this relationship when you add the subform to the main form. Every time you move to a different employee record, the subforms are updated to show the relevant records for that employee.
Additional Controls
The Employee Data form has a good sampling of some of the controls you can use to enter and manage data in Access. Using these very flexible controls, you can build an interface that provides the features and protections outlined in the introduction. You'll probably want to have the form open in design view as I go over some of the controls that this form uses. To view the properties on any control, right-click on it and select Properties from the menu.
Tab Controls
This form contains a lot of fields, far too many to be placed on one flat form without causing it to appear cluttered and disorganized. For this reason, I decided to use a tab control to group many of the fields by subject. This control is available on the Toolbox bar. Each one of these tabbed pages is an object with its own set of properties and you can add and remove pages as needed from the control. In the figure below, you can see how one of the tabs contains the Skills subform mentioned earlier.
You can add controls to each page of the tab control in the same way that you would add them to the form itself. Controls can also be dragged from the form to a tab control. The tab control can hold as many tabs as you need and as you can see in the example here, they're used to group data by subject. They are very useful in cases like this one and there are a couple of extra features that I'll talk about later on.
Combo Boxes
A number of fields on the Employee form are represented by combo boxes, which are the fields with the little arrow on the right side which enable you to choose from a list of values. When typing values in these boxes, you'll notice that they also attempt to supply possible values based on what you're typing. This is called the Auto Expand feature. In addition to speeding up data entry, combo boxes can also help to ensure accuracy by supplying a set number of choices for the user to enter into the field.
To show the flexibility available through this control, look at three specific fields under the Work Information tab; Department, Job Title and Work Status. The names are self-explanatory but each of these controls supplies possible values in different ways.
If you look at the Row Source property for the Department field, you'll notice that it's actually a query. The control uses a query to pull up a list of the available departments in the facility so that when the user enters the department for the employee, he or she only has to start typing the department number or press F4 for the pull-down list. The query uses a field titled "Label" which combines the department number and name fields into one so that both can be shown. Also, you'll notice that the Limit to List property for this control is set to Yes. This prevents the user from entering any values that don't match what is in the department table. All of the properties in this section affect the way this control accesses and displays information. By clicking once on a property and pressing F1, you can find additional information on its function and uses.
The Job Title field is also based on a query but finds its values in a different way. This control gets the list of titles from the Job Title field in the table and the user is not limited to the current list. Since the field supplies the table and reads from it, every time the user enters a new title, it is added to the list of values the control will present the next time it's refreshed. The drawback to this approach is that it allows the user to enter variations on the same value or misspellings. It can be used temporarily, however, to compile a list of necessary values. Those values can then be placed into a lookup table and the control can be changed to only allow values from that table. Whatever approach is used depends on the needs of the application.
The Work Status field indicates if an employee is currently reporting for work or on a leave or suspension of one kind or another. It can also be used to mark employees who have been laid-off and are subject to callback. In this case, there is a very limited number of values that a user should enter but unlike the Department field, these values are not found in a lookup table. Because there are so few of them, the can be specified in another way. Instead of using a query to supply the values, the control uses a value list and the options are entered in the Row Source property as string values (non-numeric values enclosed in quotes) separated by semi-colons. The pull-down list on the control shows these values just like the other controls did and the user is limited to using one of the values supplied.
This type of control can be created manually when designing the form and you can adjust the settings as needed. In the case of the Department and Work Status fields, however, the settings were made automatically as soon as the fields were placed on the form. This is because the lookup functions described above were actually defined when creating the tables. If you open tblPersonnel in design view, you'll see that the Department and Work Status fields are both designed to get their information in the same way these controls do. When I placed the controls on the form, they inherited the properties of the table fields that they are bound to. This inheritance only happens when the control is created. If you change the table field properties, the control does not change automatically.
Checkboxes
A few of the fields in the database are true / false fields or boolean fields. When you place a boolean field on a form, a checkbox is automatically created. When the box is checked, it equals a True value which is sometimes represented by -1. When unchecked, it equals a false or 0 value. You can also use the Triple State property to let the control deliver a third value to the field if necessary which could be shown as 1.
There is another feature that's related to the checkbox and, although I don't use it in this application, it's something you should check out. The Option Group is a control that groups together a number of checkboxes or option buttons to deliver one value to a table field. You would use this when you have a small number of optional values for a field and you want to display a list of the values for the user to choose from. One example would be if you were storing credit card information and the user needed to select from three or four different card types.
Command Buttons
Command buttons are one of your most useful and versatile tools for designing forms. The buttons on the Welcome screen were used solely for navigation while the five buttons that you see on the Employee form also set various conditions on the form and move it from one state to another.
A command button provides a familiar device that the user will recognize as
a way to start a task. When the user clicks on the button, it appears to
act just like a real button, moving in and out. What the user is actually
doing is activating the macro or VBA code that is attached to one of the button's
event properties, usually the On Click or On Dbl Click event. To see what
I'm referring to, choose one of the buttons and find the On Click event in the
buttons properties. The words '[Event Procedure]' indicate that VBA code
has been written for that event. Then click on the Build button () that appears when you click on the property. This
will bring up the VBA design environment in a separate window and you can view
some of the code that I've written for these buttons.
Private Sub cmdAddNew_Click()
'Set the form to add a new employee or to read-only
If Me.CurrentMode <> eAdd Then
Me.CurrentMode = eAdd
Else
Me.CurrentMode = eRead
End If
End Sub
The code above is an example from the button that adds a new employee. The first and last lines indicate the beginning and end as well as the name of the sub and its attachment to the command button's click event. The second line is a comment that is ignored by Access and serves to document the code. The rest is a simple decision process which looks at a custom property I created for the form and then sets the form to either add a new employee or to read-only mode.
Conclusion
My first work with Access was meant solely to automate some of the work that I was doing with documentation and auditing. I had worked with other databases and the BASIC language before so I had some knowledge of programming concepts and good practices. I still didn't know anywhere near as much as I know now or as I thought I did then but I managed to turn out a couple of programs that worked well and were eventually noticed by supervisors, co-workers and even people outside the company. By the time I left that company, I had designed several databases for various departments and had earned a reputation beyond the limits of my own position. By learning as much as you can about tools like Access, you can do the same.
When I was in college studying BASIC programming, I remember one of my teachers commenting on how BASIC allows for flexibility and individual style. (The flip side of the point was that it also allows for sloppy programming but that's another discussion.) The logic and methods that someone uses to design an interface in Access or another environment can be as unique as the way they use their spoken language or do anything else. The methods that I used above to keep the form in the right mode and accomplish other tasks are my own. Another programmer might do it differently and still arrive at the same result. If you work with Access over the years, you will develop your own style based on the way you approach different problems. What's most important in the end is that the program works well over the long-term and meets the user's needs.
The introduction of forms and the event-driven interface in this chapter shows a different side of Access than you saw when setting up tables and queries. Where tables need to be setup according to the rules of data normalization for the application to work efficiently, forms and reports allow for much more flexibility and creativity on your part. Your next step should be to learn as much about form design and event handling as possible so you will have the foundation to build any application you need to. This knowledge will help you not only with Access but with other database systems and programming environments.