ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

 James's Database
~~James La Borde

Forms

• Auto-Form – The Easy Way Out

• Forms Wizard – Gaining a Little Control

• Creating a Custom Form – Total Control

• Form Properties – Strengthening Control

• Field Properties – Refining Control

Forms are the easiest way for the end user to enter data into your database.  There are three ways of creating a form.  Each will be discussed in some detail.  The three methods are Auto-Form, the Form Wizard and custom creation.  Following the discussion of creation methods several properties of the forms and their controls will be discussed. 

Auto-Form – The Easy Way Out 

Auto Form is the simplest option in creating a form.  It automatically creates a form using all fields in your table.  This can be very useful to someone who has little experience at creating forms.  There are advantages and disadvantages to this method.  The primary advantage is speed.  It only takes a few seconds for Access to create the form for you.  It is still modifiable so can be a useful step in creating more complex forms.  As far as disadvantages go, you are giving up control.  It automatically adds all fields rather you want them or not and does not allow for some more complex form features.  Auto-Form is a great tool to learn to build your forms as you can use it and then view the properties and all of the design behind it.    

<<<back to top

Form Wizard – Gaining a Little Control 

The Form Wizard is a wonderful, step-by-step guide through creating a form.  It is another great tool for learning to build your own custom forms.  The Form Wizard gives the designer a greater amount of control over how his/her form will be created.  To use the form wizard simply click the new button on the Forms tab.  Next select the table or query you choose to build a form on in the pull-down box at the bottom of the resulting screen, then click on Form Wizard.  The next paragraph or two will cover each step in the Wizard, if you are more advanced and choose to skip the remainder of this, skip ahead to Custom Creation – Total Control. 

The first window that pops up in the wizard is the selection of fields.  You can either select all or any number of the fields in the given table/query.  This allows you to leave out fields such as auto-number, which are useful to the designer but mean absolutely nothing to the end user.  After you have selected all of the fields you want, click next.  The next window to appear is the Layout Option.  This is nice because as you click on the different options a simple image of the layout will appear on the screen.  Try clicking through each of these to see what your form’s layout will look like.  When you have decided which one you like best, make sure it is selected and click ‘Next’.  Now the Style option appears.  There are several styles included with Access.  Click through each of them and as with the Layout option you will see a sample of what it will look like.  If you end up creating your own style after you start creating your own forms, those too will show up in this screen if you save them.  After you have made your selection, click ‘Next’.  We now arrive at our final page.  You get to select the title for your form (this will also be the name of it).  You also get to select how the form will first be displayed to you.  Do you wish to see what has been created?  Then opt for the Open the Form option.  If you would like to do some customization on the form first, then select the Modify the Form’s Design option.  Click Finish and you have a new form.  We will discuss some of the customization options in more detail a little later in this article. 

Now you have created your own form using the form wizard.  It gives the designer a great deal more control then the Auto-Form option and again is a great tool to learn what you can do to create your own form.  For the most part, the Form wizard is an expedient way to get the basics of your form set even if you choose to build your own custom form.   

<<<back to top

Custom Creation – Total Control  

Creating your own custom form is the best option to keep total control in the design of your form.  While it gives you total control; it is also the most time consuming option as well.  To take advantage of this option, click on the 'New' button and select your table/query and then click on Design View.  This will give you a blank form with a default size of 2” high by 5” wide.  This is of course easily customizable as you can change the dimension simply by grabbing an edge and dragging it.  By using this option, you open many new types of fields to your use in the form.  All of these new field types are available on a toolbar that is called the toolbox.  

The toolbox is a fantastic tool for placing almost any kind of field on your form.  Every item has a wizard available to walk you through the details involved with that field type available.  To utilize the wizard for a field type, simply make sure that the wand on the toolbar is depressed.  Now, let's move on to the field types. 

Label – A label is a simple filed type that allows the designer to label a field or to put a message that will not change across instances of the form.  The form’s user can not change this field. 

Text Box – This is a field where data from the table/query will be displayed and/or entered.  This is the most commonly used field in most forms. 

Option Group – This is a bound set of options that limit the user’s ability to enter data into the selected field.  This can be useful in a ‘Ship by’ field where you only want to give the end user a few options. 

Toggle Button – This can be linked to a Yes/No field.  It is depressed to identify yes.  If you are tracking your Beanie Baby collection, you can use this button to identify whether or not the particular beanie is retired. 

Option Button – This is a small circle that is either filled in, or empty.  It is the used to display which option is selected in an option group. 

Check Box – Similar to a Toggle Button, this field is used to identify a yes (checked) or a no (unchecked). 

Combo Box - This is a list of valid entries that can be entered in a field, the list can be viewed by clicking on the pull-down box in the field or the user can simply start typing and the option will appear once a unique character is entered.  This field is similar to the next type, the list box but takes up less room as the items are only displayed when the box is clicked.  This field type also allows the designer to decide whether the end user can add unlisted options or not. 

List Box – Similar to a combo box with a couple small differences.  The available entries are listed in a displayed box.  This box is there whether the user selects it or not.  The user is not allowed to enter unlisted items, however, the designer can allow for multiple selections. 

Image – This one is fairly obvious folks, you can insert an image into your form. 

Unbound Object Frame – This would be used if you wished to place an image on a form that you may wish to update frequently or link to a portion of an Excel spreadsheet. 

Bound Object Frame – This option is where you would put a static picture or image.  Microsoft uses the example of an employee picture or a linked resume in word that appears as an icon.  The main difference between unbound and bound is the frequency of change in the object. 

Page Break – Another obvious one. 

Tab Control – This allows you to set up tabs on your form, you may wish to have users enter data broken down into categories and each tab can be a different category. 

Sub-form/sub-report – This field type allows you to insert an entire other form or report as a part of the current form.  This will allow you to allow the user to update linked tables at the same time as the main table.   This option also works quite well when combined with the Tab Control option.  You can insert various linked tables on to each tab using a sub-form. 

Line – A simple image of a line. 

Rectangle – This is another basic drawing tool. 

Now that we have discussed the main types of fields available lets look at one final tool.  There is a Field Box icon located on the main toolbar that brings up a box listing every field in your table/query.  By dragging any of these to your form, you will end up with a basic label and text box that are linked together.  Move one and the other moves with it. 

Now that we have our tools, let’s build our own form.  Let us assume we are creating an employee form.

In our example we will have the following Employees table:

• FirstName – Text

• LastName – Text

• Department – Long Integer

• HireDate – Date

• Manager – Text

• Benefits – Yes/No

• Picture – OLE object

And a Departments table that includes:

• DeptNum – Long Integer or AutoNumber

• DepartmentName – Text

To build a form for the Employees table we will follow these steps:

We will drag the firstname and lastname fields onto our form from the Field List.  This will give us our labels and text boxes for them and we will place them appropriately.  

Next you will notice that we have a department field that has an integer data type.  That is going to allow us to use the Departments table as a source for our Combo Box.  

We will select a combo box for this field type.  After we have selected this option we will go into the properties tab for the box.  Click on the All tab and find the row labeled Row Source Type, this will be table query.  

Next is the Row Source – simply click the down arrow and select the Departments table.  Enter 2 for Column count, and enter 0,2 for the Column Widths Option.  

Finally enter a 1 in the bound columns field.  What this just accomplished is that the user will now see the department name from the Departments table, however, the deptnum is the field that our table will store.  This saves space in the database as each employee gets a simple number in the table but the appropriate data is displayed.  

Next we will use another label and text box combination for the HireDate field.  For Manager we can either use the label and text box combination or we can use a list box, just to see an example.  You would simply create an additional table similar to Departments for Managers and set the fields appropriately.  This will show all the managers in a box that stays open.  For Benefits lets use the Check Box, this is the simplest way to view a yes/no field.  

Last but not least is our picture and we will opt for a Bound Image Frame.

If you followed along you should now be able to save your form and view it.  You should have something that looks somewhat like this: 

Now that we have seen what we can do with our own custom form, let’s move on to what we can do to make that form even more powerful.    

<<<back to top

Form Properties – Strengthening Control 

The Form Properties give the designer the ability to add a greater degree of control to the form itself.  This section will be a basic overview of what the various form properties are and how they can be used.  If you would like to follow along, open your form in design view and open the property box, then click on the All tab.  This will not be an all-inclusive list but will cover the key properties: 

Record Source – This is the most important part of any form.  This is where we tell the form what table we are pulling the data from and storing the data in.  This field can be left blank! 

Filter – This can allow only certain records to appear in your form. 

Order By – This allows you to set in what order the records will be seen. 

Caption – This is the title that displays at the top of your form. 

Allow Edits – This is one of the most powerful properties in the form Properties.  By setting this appropriately you can allow a user to only enter new data and not edit old data. 

Allow Deletions – Another powerful tool.  You can prevent the user from deleting records. 

Allow Additions – The third of the Power trio, this tool allows you to prevent users from adding records. 

Data Entry – Similar to the Power Trio.  This option allows you to force the users into entering only new data and not only prevents them from editing or deleting old data but from even viewing it. 

Close Button – This allows you to remove the close button at the top corner of your form.  This forces the user to use your designed way out if you so choose. 

There are obviously many other properties for each form, but these are the basic ones that allow you the greatest control over the form itself.   

<<<back to top

Field Properties – Refining Your Control 

As you have now found, the designer has a great deal of control in what he/she allows their user to do.  By using the Field Properties in conjunction with the form properties you can refine that control even further.   We are now going to take a look at some of the refined control that we can gain by using the field properties.  We will concentrate on two tabs on the field properties.  

Data Tab:

Input Mask – An input mask restrict the data that can be entered and sets how the entered data will be displayed. 

Validation Rule – This allows you to set parameters for what data can be entered.  For example, you can set a validation rule that would prevent the user from entering an expired credit card. 

Validation Text – This allows you to set the text of the message that displays when the user enters a value outside of your specifications. 

Enabled – This tells Access whether the field can have the focus set on it. 

Locked – This is the partner of Enabled.  It determines whether not data in this particular field can be edited. 

Other Tab:

Tab Stop - This tells the form whether or not you want a tab to enter through this field.

Tab Index – This allows the designer to specify what order the fields are tabbed through.  This can be very useful if you want them entered in a non-linear fashion.  One important tip: The tab index starts with 0. 

By utilizing these properties you can refine the control you have over all aspects of a form’s usage.  The features on the Data tab are among some of the most powerful safeguards that Access gives the developer in protecting the integrity of the data once it is turned over to the end user.  Still further control can be gained by using the Event tab but that is far beyond the basics we are covering here.   

<<<back to top

Switchboards – Another Use for Forms

If you noticed that I mentioned that the Data Source could be left blank for a form and wondered in what event that could ever take place, here is your answer – Switchboards!  A switchboard is a navigational tool.  This is how the developer can get the user from one part of their database to another.  They have no data source, as they are merely a collection of buttons with either code or macros behind them to move the user as seamlessly as possible from one for to another.  I have include an example below of the Main Switchboard in Northwinds. 

Final Word on Forms 

As we have now learned Forms are an integral part of our database.  Without them the end user is free to wander aimlessly through our database and to pretty much do whatever they wish with it.  Using forms we can not only control what they view and/or enter but also how they get there.  Yes, creating a good form with all of the properties set to levels appropriate to the user’s needs may be tedious.  However, it is vital if you are to protect the data once it is out of your control.  As an experienced Access user, I can assure you that the form wizards are great tools to get you started.  They will teach you the basics and even after you have the experience, they will get the basic stuff into your form much more quickly than you can.  

Thank you for your time and patience,
James

James La Borde works in the computer department at a Credit Union, where he uses Access, SQL Server, VBA, and ODBC daily.  He also teaches online Access classes at Eclectic Academy.