Tutorial:
Microsoft Access 2007 Screen Form
Creation
Dr.
Thomas E. Hicks
Computer Science
Department
Trinity University
Place the
NorthCentral.accdb database in the Temp folder of hard drive C; your directory
C:\Temp\ should look like the following:
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.
Database Table: <==> 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.
About Field Names: <==> 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.
About Field Data Types: <==> 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.
About 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.
About 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.
About OLE Object Data Type/BLOB: <==> 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 contains 4 tables at this time. (See Below!) We are going to create a Database Form for the Employee database 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 |
1] Hold down the drop-down combo boxed in red below. (See Below!)
2] Select Forms. (See Below!)
3] The Northwest database currently has no forms. (See Below!)
1] There are a number of different ways to create custom forms. One of the easiest ways to create a starting form is to use the Form Wizard to create an AutoForm is the easiest way to create a basic form that relates to a single table. Using the mouse, select the Create tab in the Form view. (See Below!) Using the mouse, hold down More Forms and select the Form Wizard. (See Below!)
2] Be sure to select the Employees table in the dropdown
combo box at the top. You can select the fields one at a time if you like. We
are going to push 
to select all of the
fields. (See Left Below!) Using the mouse, select/push the Next button.
(See Right Below!)
3] You can select a number of different starting layouts. Try several of them. Then, to continue this tutorial, select Columar. (See Left Below!) Using the mouse, select/push the Next button. (See Right Below!)
4] You can select a number of different starting styles. Try several of them. Then, to continue this tutorial, select Access 2007. (See Left Below!) Using the mouse, select/push the Next button. (See Right Below!)
5] Employees is a good name for the form; we could rename it if we liked. Using the mouse, select/push the Next button. (See Right Below!)
6] Employees is a good name for the form; we could rename it if we liked. Using the mouse, select/push the Next button. (See Right Below!)
1] Note that Employees is now in the Forms list. Using the mouse, double-click on the Employees Form (left) to launch the form. (See Below!)
1] The form Control is located at the bottom of the auto form. (See Below!)
2] The table below has 9 records; the record being shown in the form is Record #1. (See Below!)
3] The table below has 2,737,504 records; if I move the mouse in the control, we can scroll over and see the full number. The record being shown in the form is Record #2,737,496. (See Below!)
4] 
5] 
6] 
7] 
8] 
9] Even less obvious to the most casual user is the need to right-mouse click on the left control bar and select Cut to delete the current record. (See Below!)
10] You do at least get a delete confirmation control when you cut a record. (See Below!)
1] Using the mouse, right-mouse click on the Employees Form and select Design View. (See Below!)
2] Note that the form has a Header at the top and a Footer at the bottom. Neither are required. (See Below!)
1] Just for fun, change the font color
and text; change the background
; change the font face
.
(See Below!)
2] Note the changes below. Neither the header, nor the footer, change as you navigate through the form records.
3] Neither the Header, nor the Footer, are required. (See Below!)
4] The form now has neither a header, nor a footer. (See Below!)
1] While in the Design View, you can take you mouse and grab the right edge of the page (Red) and shift it larger or smaller by dragging left and right. (See Below!) While in the Design View, you can take you mouse and grab the bottom edge of the page (Purple) and shift it larger or smaller by dragging up and down. (See Below!)
1] Using the mouse, box in all of the fields on the form and drag them downward (so that there is some room at the top). (See Below!)
2] The column of fields at the left (ID, First, Last, Title, ....) are locked in a layout. Let us remove this layout so that we can move the field. Using the mouse, right-mouse click on the group of fields and select Layout and select Remove. (See Below!)
3] The layouts have been removed. The items can now be manipulated one at a time. (See Below!)
4] The most important information should be at the top of the form. Suppose I would like to move the First Name up; select it with the mouse and drag it. (See Below!)
5] Note that the prompt and the input text box are locked together and both move together. (See Below!)
6] Some times you might choose to have the prompt above the data entry field. Using the mouse, grab the brown box at the top right corner of the data entry field and drag it where you would like to position the data entry field. (See Below!)
7] As you can see below, this works, but I am going to undo mine (Ctrl-Z).
8] I have decided that I want neither the photo nor the extension on our new form. I have deleted the prompt from the TitleOfCourtesy and the Notes. I have roughly laid out a design for the new form. I have attempted to use a couple of major diagonals (as was recommended on the good form design tutorial).
9] As I look at the new form, I decide that someone using it 8 hours a day might prefer a prettier layout.
1] Using the mouse, I am going to select all of the text. As you can see below, I am changing the font face to Ariel. (See Below!)
2] Using the mouse, I am going to make the Ariel font face bold. (See Below!)
3] Maybe better, but not much. (See Below!)
1] Using the mouse, I am going to touch some place on the background that has neither a prompt nor a text box. (See Below!)
2] Using the mouse, select the color button
. (See Below!) Select a color you like.
I am going to select a dark blue. You may select More Colors. (See Below!)
3] You can see right away that I am going to have to do something about the color of the prompts.(See Below!)
4] This simply isn't going to work. (See Below!)
1] You can change the prompts, one at a time if you like; I am a bit lazy. Using the mouse, encircle a bunch of the prompts. (See Below!)
2] If you hold down the shift key, you can encircle the rest of the prompts. (See Below!)
3] I am going to hold down the font color button
and select white. (See Below!)
4] Likewise, I am
going to select all of the data input fields; I am going to hold down the font
color button and select a dark red. (See
Below!)
5] Our form is starting to get a little bit better. (See Below!)
1] It is best to save your work regularly.
1] Select the Arrange tab. Using the mouse, select all of
the prompts and data input fields within a single row; push the horizontal align
at the top. 
2] Do likewise for each of the other rows. Make the distance between the rows about the same. (See Below!)
1] Select the Arrange tab. Using the mouse, select all of the
data entry fields in the first major column (one at a time); data entry fields
should be aligned left. Push the left align at the top.
2] Select the
Arrange tab. Using the mouse, select all of the data entry fields in the second
major column (one at a time); data entry fields should be aligned left. Push the
left align at the top.
3] You can move the prompt away from the data entry field by dragging from the top right corner. Move all of the prompts about the same distance away from their respective data entry fields. Reset the horizontal alignment if necessary. (See Below!)
4] Select the
Arrange tab. Using the mouse, select all of the prompts; they should be aligned
right. Push the left align at the top. 
4] Select the Home
tab. Using the mouse, select all of the prompts in the first major column (one
at a time); data entry fields should be aligned right. Push the left align at
the top.
5] Things are starting to look a bit more professional.
1] We would like to have a better button to move to the next
logical record. The buttons on the control are woeful. Select the Design tab.
Using the mouse, select/push the Button button.
(See Below!)
2] After you push the
button, touch your mouse to your form. (See
Below!)
3] Select Record Navigation. Select Go To Next Record. Using the mouse, push/select the Next button. (See Below!)
4] Select the symbol that you wish to use on the button; you may draw your own. Using the mouse, push/select the Next button. (See Below!)
5] Name the button appropriately. Using the mouse, push/select the Finish button. (See Below!)
6] You now have a fully functional Next button; you can drag it to an appropriate size. (See Below!)
1] You can make your own custom buttons. I used a paint program to create the set of bitmap images below; they might be used to represent previous, next, last, first, delete and new respectively.
 |
 |
 |
 |
 |
 |
2] We would like to have a better button to move to the previous logical record. The buttons on the control are woeful. Select the Design tab.
Using the mouse, select/push the Button button.
(See Below!)
3] After you push the
button, touch your mouse to your form. (See
Below!)
4
] Select Record Navigation. Select Go To Previous Record. Using the mouse, push/select the Next button. (See Below!)
5] I have the images above on my desktop. Using the mouse, push/select the Browse button.
6] I have selected the Previous Image. Using the mouse, push/select the Open button. (See Below!)
7] Using the mouse, push/select the Next button. (See Below!)
8] Name the button appropriately. Using the mouse, push/select the Finish button. (See Below!)
9] You now have a fully functional Previous button; you can drag it to an appropriate size. (See Below!)
1] We would like to have a better button to move to the first logical record. The buttons on the control are woeful. We would like to repeat the process above to create a First button.
2] As you create this new button, select Record Navigation. Select Go To First Record. Using the mouse, push/select the Next button. (See Below!)
3] I Select our image for First and name the button appropriately. Using the mouse, push/select the Next button. (See Below!)
4] You now have a fully functional First button; you can drag it to an appropriate size. (See Below!)
1] We would like to have a better button to move to the last logical record. The buttons on the control are woeful. We would like to repeat the process above to create a Last button.
2] As you create this new button, select Record Navigation. Select Go To Last Record. Using the mouse, push/select the Next button. (See Below!)
3] I Select our image for Last and name the button appropriately. Using the mouse, push/select the Next button. (See Below!)
4] You now have a fully functional First button; you can drag it to an appropriate size. (See Below!)
1] We would like to have a better button to move to delete the current record. Selecting the option to cut the record is woeful. We would like to repeat the process above to create a Delete button.
2] As you create this new button, select Record Operators. Select Delete Record. Using the mouse, push/select the Next button. (See Below!)
3] I Select our image for Delete and name the button appropriately. Using the mouse, push/select the Next button. (See Below!)
4] You now have a fully functional Delete button; you can drag it to an appropriate size. (See Below!)
5] When you push the delete button, a confirmation dialog box automatically pops up. (See Below!)
1] We would like to have a better button to add a new record. The buttons on the control are woeful. We would like to repeat the process above to create a Add button.
2] As you create this new button, select Record Operators. Select Add New Record. Using the mouse, push/select the Next button. (See Below!)
3] I Select our image for New and name the button appropriately. Using the mouse, push/select the Next button. (See Below!)
4] You now have a fully functional New button; you can drag it to an appropriate size. (See Below!)
1] We would like to have a better button to add a new record. The buttons on the control are woeful. We would like to repeat the process above to create a Add button.
2] As you create this new button, select Record Operators. Select Add New Record. Using the mouse, push/select the Next button. (See Below!)
3] I Select our image for New and name the button appropriately. Using the mouse, push/select the Next button. (See Below!)
1] For the more adventurous among you, you might want to customize the buttons a bit more. Using the mouse, double-click on the button. (See Below!)
2] This brings up a properties dialog box. I encourage you to play around with the configuration. At worst, you can delete the button and try again. I am adding text and color to my Exit button. (See Below!)
3] If you expand the Picture
options in the Property Sheet
you will discover that Microsoft has more than 120 pictures available without drawing your own. Many people also import images from the web.
4] Just a quick look at the changes made to the Exit Button in the properties window above.
1] As you scan through the data, you will notice that many of the auto-generated field sizes are too large. There is also a large void on the left side of the form that needs some assistance. The form below is better yet!
1] Run your form. Move to the First Name Field. (See Below!)
2] Then hit the tab key. The results will vary depending upon how you created your form. You can see below that my form went to the Title next; I really would have preferred that it move next door to the Last Name. (See Below!)
3] The next tab moves to the Title Of Courtesy and the following to the Birth Date. (See Below!)
4] I would like to set the tab order to move start at the top left -> move across the row until reaching the far right --> drop a row --> and repeat. Let us begin by Starting the Tab Order control. (See Below!)
5] The tab order button enables you to see the order, but not really customize it much. You can see that tabs proceed from the First to the Title Of Courtesy to the BirthDate. (See Below!)
6] Right-Mouse click on Title Of
Courtesy ==> Select Properties. Set the Tab Index to 0; it will now be first on
this form. (See Below!)
7] Right-Mouse click on First Name==> Select Properties. Set the Tab Index to 1; it will now be second on this form. (See Below!)
8] Right-Mouse click on Title Of
Courtesy ==> Select Properties. Set the Tab Index to 0; it will now be first on
this form. (See Below!)
9] When you go back to Tab Order
button, you will see that things have changed. If you select the AutoOrder on
this form, it will make it proceed from left to right as you move top to bottom.
Try it! (See Below!)
1] There will be times that the form is not fully visible.
2] There will be times that the form is more than fully visible. (See Below!)
3] To get to the form properties, right-mouse click off the edge of the form (to the right) and select properties. (See Below!)
4] We would like to get rid of the scroll bars, and all of the quick and dirty access controls. Configure your application as illustrated below. (See Below!)
1] While you still have the form properties open, change the caption. (See Below!)
2] Each form should have a good caption. (See Below!)
Download and decompress the data file for this example to see the form created above.