Tutorial: Microsoft Access Screen Form Creation

Dr. Thomas E. Hicks
Computer Science Department
Trinity University


Other Tutorials

Tutorial: Microsoft Access
Data Types, Table Processing & Design Layouts


Database Form Guidelines

 

Sample Database

Download and decompress the data file for this example and design your own Access  application as you walk through the tutorial.

 NorthCentral.zip     

If you are not sure how to unzip files, check out the WinZip Tutorials

Installing WinZip Tutorial     Using WinZip Tutorial

Place the NorthCentral.mdb database in the Temp folder of hard drive C; your directory  C:\Temp\ should look like the following:


 


Database Terminology

Database Management System [DBMS]: <==> a collection of interrelated data and the programs to access that data.

Database: <==>A database is comprised of those (1) tables, (2) programs, (3) forms, (4) labels, and (5) queries related to a specific application.

DatabaseTable: <==> a collection of information pertaining to some type of entity. A database table might represent an entity such as clients (contact manager) , an entity such as parts (inventory management system), an entity such as homes (real-estate management system), an entity such as students (grade book program), etc.

Record:  <==> the information related to one entity (one part, one client, or one student). In a client database table, each record would contain the information related to one client. In a student database table, each record would contain the information related to one student.

Field: <==> the individual sub-components of one record. Each database table is partitioned into one or more fields. The fields within a client database table might be Name, Address, City, State, Zip, Phone, Male, Notes, etc. The fields within a Homes database table might be Owner, Address, City, State, Zip, Phone, NoBedrooms, NoBath, Pool, Info, etc.

Field Name: <==> Associated with each field must be a Field Name. The Field Name is a program variable associated with this field. The field name should be meaningful. Although the Access Database allows the field name to be more than one token, for purpose of SQL, web site design, and more sophisticated database interfaces, it is best if the field name and a single token (no blanks!). It is best to use upper & lower case letters to make this field more readable (if the database permits); FirstName is preferred to any of the following: firstname, first_name, firstName, or  FirstName. When possible, I recommend that the first character of each important word in the field name should be capitalized; there should be no blanks in the field name.

Field Data Type: <==> Associated with each field must be a Data Type, also spelled Datatype. The Data Type  is absolutely essential. It is the data type that helps to prepare the right type of container in which the user can include the desired information. Without going into detail, it is sufficient to say that computers store character data differently than whole numbers differently than fractional numbers etc. Our primary data types shall include text/char, logical/yes-no, date, numeric, and memo.

Numeric Data Type: <==> the Numeric Data Type  prepares a container for numeric information. Numeric data is often stored in the computer's native binary format. The computer stores integer { 0, +/- 1, +/- 2, +/- 3, +/- 4, ...} differently than it stores rational {a/b | a is an integer, b is an integer, and b <> 0} numbers. We sometimes call the rational numbers fractions. Larger numbers and precision often require more bytes for storage. It is the database designer's responsibility to select a container that is sufficiently large enough to hold all of the values that might be stored in the field without making the field too large; this size can be changed later.

Numeric [Byte] Data Type:  <==> can be used to store numbers 0 <==> 255. [1 byte]

Numeric [Integer] Data Type: <==> can be used to store numbers -32,768 <==> 32,767. [2 bytes]

Numeric [Long Integer] Data Type: <==> can be used to store numbers -2,147,483,648<==> 2,147,483,647. [4 bytes]

Numeric [Single] Data Type: <==> real/fractions represented with scientific notation with at least 7 digits of accuracy. [4 bytes]

Numeric [Double] Data Type: <==> real/fractions represented with scientific notation with at least 15 digits of accuracy. [8 bytes]

AutoNumber Type: <==> computer generated integer values generally in the sequence 1, 2, 3, ...; since the same number is never generated twice, these are unique.

Memo Data Type: <==> the memo field is an unbound, unlimited container for text data. All of the Access database components are stored in a single .mdb file.  In other databases, it is generally stored in a file separate from the associated database table.

OLE Object Data Type: <==> the OLE object data type is used to store pictures and other objects.

Database Form: <==> a screen window into some portion of the database information. A form can contain information from database fields as well as  buttons, tabs, & controls which assist in user processing.

Database Report: <==> a print formatted copy  of some portion of the database information.

Database Label: <==> a print copy  of some portion of the database information that is designed especially for output on business type labels.

Database Query: <==> a specialized request for some portion of the database information.

Primary Goal of a DBMS: Provide a convenient and efficient environment in which store and retrieve information. 


The North Central Company Database

The North Central Company database contains 4 tables at this time. (See Below!) We are going to create a database form for the Employee database table.


 


Database Structure/Layout  For The Employee Table

Field Name Data Type Caption Format Specification
EmployeeID AutoNumber Employee ID  
LastName Text Last Name 20 Characters
FirstName Text First Name 10 Characters
Title Text Title 30 Characters
TitleOfCourtesy Text Title Of Courtesy 25 Characters
BirthDate Date/Time Birthday Medium Date
HireDate Date/Time HireDate Medium Date
Address Text Address 60 Characters
City Text City 15 Characters
Region Text Region 15 Characters
PostalCode Text PostalCode 10 Characters
Country Text Country 14 Characters
HomePhone Text HomePhone 24 Characters
Extension Text Extension 4 Characters
Photo OLE Object Photo  
Notes Memo Reports To  
ReportsTo Number Reports To Long Integer


Creating An AutoForm

1] The AutoForm is the easiest way to create a basic form that relates to a single table. Using the mouse, select the Table Object from the Objects menu on the left. (See Below!)

2] The AutoForm is the easiest way to create a basic form that relates to a single table. Using the mouse, hold down the Insert Menu on the top menu bar and select AutoForm (See Below!)

3] Access will generate it's best shot at a form; it is shabby and mediocre at best.  (See Below!) It is somewhat functional if you know what to do. The AutoForm opens in the Form View Mode.

Even this form view of the data is more pleasant than the spreadsheet table view of the data. Users can now use this form to add new records, alter existing records, delete old records, etc.


The AutoForm Controls In The Form View Mode

 The screen is poorly laid out. The controls are woeful and unclear to the novice. See Database Form Guidelines

  moves the database record pointer to the first logical record in the database.

  moves the database record pointer to the last logical record in the database.

moves the database record pointer to the previous logical record in the database.

moves the database record pointer to the next logical record in the database.

adds a new record.

 Record deletion is the most obscure. Using the right mouse button on the triangle in the bar on the left side of the form reveals a menu bar; select cut to delete! Yuk! (See Below!)


Using The View Control To Enter The Design Mode From The Form View

Fortunately, we can go into the design view and modify the AutoForm. Using the mouse, hold down the T-Square View control [generally top left corner of menu bar] and select Design View. (See Below!)

Once we get the form into the design view we can do great things with it. Note that the design view has more of a graph paper look to it. We shall use this view to alter the form later in this tutorial. (See Below!)


Using The View Control To Enter The Design Mode From The Form View

We shall shortly discuss some of the many options available in the design view. As we make improvements, it will be essential that we know how to move from the Design View back to the Form View in order to examine the results of our labors.  Using the mouse, hold down the T-Square View control [generally top left corner of menu bar] and select Form View. (See Below!)

The Form View can now be seen below.


Save Your Work Every Few Minutes

Your time is valuable. Save your work every few minutes.  Using the mouse, hold down the File Menu and select Save.  (See Below!)

 Select good titles for your forms. Since this form relates to the Employees Table, I shall call it Employees. If I were going to do several forms related to the Employees table, I would select a more descriptive title for the form; I am not. (See Below). This is the default provided for you. Using the mouse, push the OK button.


Determining What Forms Exist In The Database

Using the mouse, select the Forms Object from the Objects menu on the left. (See Below!)

You can see that my database currently has three forms.  (See Below). You might guess from the titles that the Customers form relates to the Customers table, that the Products form relates to the Products table, etc. Select good titles for your forms.


Starting The Database Employees Form

In order to start your database form, the user will simply (1) double-click on the database icon to start Access, (2) select the Forms Object, double click on the Employees form.


Data Objects In The Design View

The prompt and the data entry field  related to that prompt are components of a single object. In the image below, I have used the mouse to select/touch the prompt (on the left). If I were to change the font size, the font style, or the font color, it would be the prompt that would change. (See Below!)

 In the image below, I have used the mouse to select/touch the data entry field (on the left). If I were to change the font size, the font style, or the font color, it would be the prompt that would change. (See Below!)


Changing The Prompt Text

Suppose my data entry folk keep asking what Employee ID means; after I tell one or two of them that I want them to type in the Employee ID number, I realize that I have not made the prompt as clear as I had intended. I want to change the text to read Employee Id #.

Using the mouse, select the prompt. Note that the cursor turns into the text I-beam often used with word processing. I have made the I-beam cursor red for emphasis; yours will be black.

You can move it to the end and add the # to the prompt. The text has now been added to the prompt, but the # is now physically behind data entry field and the visibility is impaired and the prompt and data entry field must be separated.

    


Altering The Prompt & Data Entry Field Relationship

Using the mouse, select the box at the top left corner of the prompt; I have colored the box red in the diagram below; yours will be black. Note that the cursor changed into a hand; I colored the cursor blue in the diagram below; yours will be black. Hold down the left mouse button and drag the prompt to the desirable location.

On most forms, the prompts are located equal distance on the left of the data entry fields such as

On some forms, the prompts are located equal distance above the data entry fields such as

There are generally two/three ways to do everything in form design. I will often only show you one. You may also select the box at the top left corner of the data entry field and move the field instead of the prompt.

Whether you decide to make them equal distance on the left or equal distance at the top, be consistent; it is generally considered bad practice to mix the two formats.


Moving Objects In The Design View

You may use your mouse to highlight/box one or more prompt/field objects and drag them to the desired location. In the illustration below, I have boxed in the EmployeeID prompt and data entry field object.

In the illustration below, I am dragging my boxed in collection to the right; have not let go of the mouse yet. You can sort of see where I am dragging it by the box that goes along with me in the background.

When I let go of the object, it physically moves.


Changing Font Size In The Design View

Changing the font face or the font size alter the size associated with an object. Making the font bold, italic, and/or underscore also alter the size associated with an object.

Suppose I wish to change the font size of the prompt to 14 points to make it easier for the user to read the form. Using the mouse, select the prompt. Using the mouse, hold down the font size control and select 14. (See Below!)

The prompt is now size 14, but very unreadable.  We shall use Size To Fit to fix this shortly.  (See Below!)

I wish to change the font size of the data entry field  to 14 points to make it easier for the user to input data into the form. The data entry field should be at least as big as the prompt. Using the mouse, select the data entry field. Using the mouse, hold down the font size control and select 14. (See Below!)

The data entry field is now size 14, but it too shall be unreadable.  (See Below!)

The data entry field is now size 14, but it too shall be unreadable.  (See Below!)

The efficient computer scientist is always looking for short cuts. Instead of resizing the prompt and the data entry fields separately, we could have used the mouse and selected both fields and changed them simultaneously! In fact, we can select multiple objects and make one change.  The selection below includes the prompts and fields for 7 of the form objects. Let us make all of them 14 point font.

 Using the mouse, select the prompt. Using the mouse, hold down the font size control and select 14. (See Below!)

The objects below are also unreadable.  See Size To Fit!


Resizing Objects In The Design View

Using the mouse, select all of the objects to resize. In the illustration below, I am only selecting the object associated with the Employee Number.

Using the mouse, select all of the objects to resize. In the illustration below, I am only selecting the object associated with the Employee Number.

Visual FoxPro Tutorial D:Introduction To FoxPro Screen Forms

Resizing Objects In The Design View

Using the mouse, select all of the objects to resize. In the illustration below, I am only selecting the object associated with the Employee Number.

Using the mouse, select all of the objects to resize. In the illustration below, I am only selecting the object associated with the Employee Number.

Once the prompt and the data entry field have been properly separated, you will see that the data entry portion of our form is much easier to read.

Size to Fit  does an excellent job resizing the prompts. It does not do a good job resizing the data entry fields. Whereas Foxpro, and some other databases will resize a text field of 20 characters large enough to hold any 20 characters of your chosen font, Access does not. It simply make a guess that is often too small. Check out your form. You will often have to drag the data entry fields larger or shorter in Access.


Changing The Form Background In The Design View

Make sure that no object on the form is selected. You can do this by touching a portion of the form that has no objects. Using the mouse, hold down the Fill Background button's drop down. (See Below!)

Select a background color & font color combination that is easy to visualize and use for 8 hours a day. The font color below should be changed!


Changing the Font Color In The Design View

Using the mouse, select those prompts and/or fields for which you wish to change the color. Using the mouse, hold down the Font Color button and select the desired color. (See Below!)

Select a background color & font color combination that is easy to visualize and use for 8 hours a day. Limit the number of colors on your form.


Making the Font Bold/Non-Bold In The Design  View

Using the mouse, select those prompts and/or fields for which you wish to change the font boldness. Using the mouse, push down the Bold toggle button. I press it once to make my selection bold. If I don't like the results, I press it a second time to remove the boldness from my selection. (See Below!)

The text and fields need to be easy to read if doing data entry 8 hours a day.

You should always resize the selected fields and prompts after altering the boldness. This is easy to read!


Making the Font Italic/Non-Italic In The Design  View

Using the mouse, select those prompts and/or fields for which you wish to change the font italicness. Using the mouse, push down the Italic toggle button. I press it once to make my selection italicized. If I don't like the results, I press it a second time to remove the italics my selection. (See Below!)

The text and fields need to be easy to read if doing data entry 8 hours a day.

You should always resize the selected fields and prompts after altering the italicness.


Making the Font Underline/Non-Underline In The Design View

Using the mouse, select those prompts and/or fields for which you wish to change the font underline status. Using the mouse, push down the Underline toggle button. I press it once to make my selection underlined. If I don't like the results, I press it a second time to remove the line from my selection. (See Below!)

The text and fields need to be easy to read if doing data entry 8 hours a day. Underline often makes it very difficult to read. I would use this very sparingly! Since it is also possible to put hyperlinks on your forms, it is can also send mixed signals to the user.


Changing the Font Face/Type In The Design  View

Using the mouse, select those prompts and/or fields for which you wish to change the font face/type. Using the mouse, push down the Font Face drop down menu and select the font you like. I shall change my font to the Comic San MS face.  (See Below!)

The text and fields need to be easy to read if doing data entry 8 hours a day. With this font face, I am going to non-bold my selection..

Use a small number of font size, font color, and font size changes on any given page.  This is easy to read!


Review The Guidelines For Good Forms!

Database Form Guidelines

The most important information should be placed at or near the top of the form; Name, Employee ID, Title, and Phone are important; I would put them near the top.

Information should be logically organized on the form; City, Region, and Postal Code should be in the same general vicinity.

If you are not partitioning the form with boxes and lines, it is often best to try to keep one or two major columns running through your form.

Consider your audience when making your forms. I recommend writing them for idiots and you will seldom be disappointed.


Horizontal Alignment In The Design  View

Although some of the important information is at the top, the form below is really bad!

The prompts and data entry fields should be horizontally aligned within each row. (See Below!) Use the mouse to select the prompt and data entry fields that are associated with the first and last name. Using the mouse, hold down the Format Menu,  select Align, and select Bottom. This will horizontally align that entire row.

The same was done for the other two row. See Below!

Save The Form Frequently!


Spacing In The Design  View

The form above needs help with spacing. When possible, the form should have about the same amount of space at the left, right, top, and bottom; frame it like a photo as much as possible. Use The Mouse To drag!

When possible, the form should have about the same amount that same amount of space between rows. Use The Mouse To drag!

This is better!


Vertical Alignment In The Design  View

The form above needs help with vertical alignment. As is possible, the data entry fields should be left aligned within the major columns. Using the mouse, select only the data entry fields in the left column. Using the mouse, hold down the Format Menu,  select Align, and select Left. This will horizontally left-align that entire column. Do likewise for the other major column.

The prompts should be right justified equal distance from their respective fields. I shall move the Last Name prompt a desirable distance from its data entry field. Since I am about to do a right alignment, I don't want any of the other prompts in this column to be closer than that to their respective fields.

Select all of the prompts in this column. Using the mouse, hold down the Format Menu,  select Align, and select Right. This will horizontally right-align that entire column. Do likewise for the other major column.

Things are looking more presentable/professional.


Adding a Next Button The Design  View

We would like to add custom buttons to the form above, as opposed to using the AutoForm controls. Check to see if there is a command button somewhere on your menu bar or screen. If not, use the mouse to hold down the View Menu and select ToolBox.

Using the mouse, touch/select/push the Command Button. (See Below!)

Using the mouse, touch on your form the general location in which you wish to create this button; it can be easily moved when finished.. (See Below!)

Using the mouse, select the Record Navigation Category and the Go To Next Record Action.  Using the mouse, push the Next Button. (See Below!)

Using the keyboard, enter Next in the text box at the top.  Using the mouse, push the Next Button. (See Below!) {You may use a graphical button if you like. You may draw your own and save it in .bmp format and use that as well.}

If you ever choose to program some of the Access events, it is best to have well named objects. Using the keyboard, enter btnNext in the text box at the top.  Using the mouse, push the Finish Button. (See Below!)

You now have a button that can be resized.. (See Below!) See The Forms Guidelines for suggestions pertaining to buttons.

Go to the View mode and try out the Next button.


Adding an Previous Button The Design  View

Adding a Previous Button to the form above is going to be much like adding the Next Button. 

You begin by adding a Command Button to general location on the form.

Using the mouse, select the Record Navigation Category and the Go To Previous Record Action.  Using the mouse, push the Next Button. (See Below!)

Using the keyboard, enter Previous in the text box at the top.  Using the mouse, push the Next Button. (See Below!) {You may use a graphical button if you like. You may draw your own and save it in .bmp format and use that as well.}

If you ever choose to program some of the Access events, it is best to have well named objects. Using the keyboard, enter btnPrevious in the text box at the top.  Using the mouse, push the Finish Button. (See Below!)

You now have a button that can be resized.. (See Below!) See The Forms Guidelines for suggestions pertaining to buttons. Go to the View mode and try out the Previous button.


Adding An Add New Record Button The Design  View

Adding an Add New Record Button to the form above is going to be much like adding the Next Button. 

You begin by adding a Command Button to general location on the form.

Using the mouse, select the Record Operations Category and the Add New Record Action.  Using the mouse, push the Next Button. (See Below!)

Using the keyboard, enter Add A New Record in the text box at the top.  Using the mouse, push the Next Button. (See Below!) {You may use a graphical button if you like. You may draw your own and save it in .bmp format and use that as well.}

If you ever choose to program some of the Access events, it is best to have well named objects. Using the keyboard, enter btnAdd in the text box at the top.  Using the mouse, push the Finish Button. (See Below!)

You now have a button that can be resized.. (See Below!) See The Forms Guidelines for suggestions pertaining to buttons. Go to the View mode and try out the Add A New Record button.


Adding A Delete This Record Button The Design  View

Adding a Delete  Button to the form above is going to be much like adding the Next Button. 

You begin by adding a Command Button to general location on the form.

Using the mouse, select the Record Operations Category and the Delete Record Action.  Using the mouse, push the Next Button. (See Below!)

Using the keyboard, enter Delete This Record in the text box at the top.  Using the mouse, push the Next Button. (See Below!) {You may use a graphical button if you like. You may draw your own and save it in .bmp format and use that as well.}

If you ever choose to program some of the Access events, it is best to have well named objects. Using the keyboard, enter btnDelete in the text box at the top.  Using the mouse, push the Finish Button. (See Below!)

You now have a button that can be resized.. (See Below!) See The Forms Guidelines for suggestions pertaining to buttons. Go to the View mode and try out the Delete This Record button.

The default button code associated with this object actually requires user confirmation. (See Below!)

Save The Form Frequently!


Putting It All Together

I have not been showing the entire form because of space and printing concerns. Out form has come a long way! (See Below!)

Those controls that came with the AutoForm are no longer needed. Time to remove them.


Removing The AutoForm Controls In The Design View

This part is a little more tricky! We need to get to the form object properties dialog box. Using the mouse, right click in the red regions that is inside your form design window, but off the form itself and select Properties.  I have colored mine red, but yours is probably gray. (See Below!) You may have to make your form design window wider to get to such an area.

Using the mouse, change the Record Selectors option to No.

Using the mouse, change the Dividing Lines option to No.

Using the mouse, change the Navigation Buttons option to No.

Save The Form Frequently!


Adding A Form Title In The Design View

Go to the form object properties dialog box just as we did in the "Removing The AutoForm Controls In The Design View" section above. Note that the captions was Employees.

Using the keyboard, I am changing the caption to Employee Database Application Written By Dr.. Thomas E. Hicks; it scrolls off the screen, but just keep typing. Every form should have a Good Title.

Save The Form Frequently!


Final Product In The Design View


May be accessed through URL: http://www.cs.trinity.edu/~thicks
May also be accessed through URL: http://carme.cs.trinity.edu
This Document May Not Be Printed or Reproduced Without Written Permission.
 2003 Copyright : Dr. Thomas E. Hicks
Permission granted : Professional Educators & College Students may print one copy of this page!

Dr. Thomas E. Hicks

Computer Science Department    
Trinity University

"Dr. Web"