Access Form Design

Technical Support Services

Course Description
This is the fifth in a series of six workshops about Microsoft Access. It deals specifically with form design. The purpose of this installment is to expose you to the possibilities involved in designing your database forms. Form design is time consuming and requires some patience. Learning the form design process is a considerable portion of the database development process. Our goal is to assist you to learn the software, understand some basic concepts and show you some tips and techniques so you can develop your database management/programming skills over time. The six workshops are: Introduction to Access Access Queries Access Reports Access Database Design Access Form Design Access Macros

Form Basics
Uses of Forms Forms created in Microsoft Access are very much like paper forms except they have a wider variety of functions and uses. They can be used to: Enter data into the database Using a form for data entry not only saves time, it is a way to prevent errors. Values can be selected from pull down lists thereby reducing the typing time and insuring that data entry rules are consistent. View or browse data in the database A form can be used to view or browse through the data from a single record in form view or multiple records in tabular or datasheet view.

 Replicate source documents An Access form can be created that looks exactly like the paper source document or data collection form. During data entry, the user doesn't have to be concerned with finding the exact spot in the table to insert the data; the form and its table links will position it in the correct table field. Provide an attractive presentation of data The form can be designed to use a variety of fonts, colors, and special effects such as shading to enhance its appearance. Calculate totals The form can extract raw numbers from a table record, calculate totals or evaluate mathematical expressions, and display the results as a part of the form. Hold a chart or pivot table A form can be used to display data in a graphical format (such as a bar chart or a pie chart) or in a Microsoft Excel pivot table format. These forms can only be used to display data; they cannot be used for data entry. Display data from more than one table The form can be based on a multiple table query instead of a single table. Using this feature you can access the data in several tables simultaneously.

Form Objects
All of the information on a form is contained in controls. Controls are objects on a form that are used to display data, perform actions, or decorate the form. Some controls are bound (linked) to fields in a table or a query. These controls can place data into the fields or display data from the fields. Different types of controls or objects are used to display different data types: a text box is used to display data and numbers, an object box is used to display pictures, and other controls display information that is part of the form design. Labels are controls that are used to display descriptive text, while lines and squares are controls that are used to organize or separate data. The image control is used to enhance the appearance of the form with a logo, clip art, or some other graphic file. If you want each database record to have a different image (e.g. employee photographs), then use a bound object control instead. Calculated controls act like bound controls but are used to display the results of a calculation. Frequently the parameters used in the underlying formula are other controls on the form.

Creation Methods
Access contains several ways to create a form. You can design the form yourself by placing controls on a blank grid or you can use one of the builtin Wizards. The most common approach is to use a wizard to develop the basic design of the form (a rough draft) and then switch to the design view for form customization. Build From Scratch You, as the user, can enter controls, labels, and links to the data in the underlying tables in the design view of a form. Autoform Wizards Each of the autoform wizards produces a simple preformatted form in either columnar, tabular, or datasheet format. Each wizard works automatically, without prompting from the user. Other Wizards Other form wizards are available that will produce columnar, tabular, datasheet, justified, chart, or pivot table forms. Each wizard prompts the user with a series of questions and bases the form on the answers.

Sources of Data
When a form is used to enter, update, or display data from a single table, then you can connect or link the form directly to the table. However, if the form is to use data from multiple tables, then the tables should be linked together in a query and the form based on the query.

Microsoft Access forms are generally classified into one of these types: Columnar In a columnar form, the values are all placed in one vertical column; one item to a line and one record per form. Access will place the data in newspaper style columns if the number of data items exceeds the form length. Tabular In a tabular form, the data is placed in rows with field labels at the top. Multiple records can be displayed on a single form. Tabular forms are best when you want to display multiple records with relatively few fields to prevent the need for left to right scrolling. Datasheet A datasheet form will display multiple records in a typical datasheet view or single records in a tabular view. This form is used mainly as a subform. Justified Available through the Form Wizard; lays out fields in a staggered compact manner.

Main/Subform

A main/subform form is used to show a one to many

relationship between the main form and the subform. The main (or one side) is displayed in single column format on the top of the form while the subform (or many side) is displayed in a datasheet format on the bottom of the form. The subform can be used to display one or more records that are related to the main form. There is a subform wizard button available in the Form Design Toolbox. Linked Forms
The main form in a linked form set contains a button that branches to or opens another form related to the first. The second form is displayed in its own window, generally on top of the first form. Closing the second form returns control to the main form.

Charts
The chart form displays data as a graph such as a bar chart, pie chart, or a column graph. The chart cannot be used for data entry.

Pivot Tables
A pivot table lets you summarize large amounts of data in spreadsheet form. The pivot table requires that Microsoft Excel be installed on the computer before the table can be created. Management and editing of the table is done with an OLE branch to Excel.

Creating Forms
The general procedure for creating a form is: 1. In the Database Window, click the Forms entry in the Objects list 2. Click on the New button in the toolbar to display the New Form dialog box. 3. Select the appropriate table or query in the Choose Table or Query field (click the pop-up to display the list of available tables and queries). 4. Select a Wizard or Design View from the list and click the OK button. If you choose a wizard, follow the instructions in the Wizard dialog boxes. Click the Next button after each step to continue through a Wizard.

Activity 1 - Use an Autoform Wizard

Create a form to add or update information in the class table using the Tabular AutoForm Wizard. 1. Open the sample database SCHOOL4.MDB if it is not already open. 2. In the Database window, click the Forms entry in the Objects list. 3. Click New button in the Database window toolbar. 4. Select the Class table. 5. Select the AutoForm: Tabular format. 6. Click the OK button. 7. The form is created and displayed on screen. 8. To save the form for future use, close it and respond Yes to the Save Changes dialog box. When the Save As dialog box appears, name it Class Entry.

Activity 2 - Use an Interactive Form Wizard

Create a columnar form to add or update information in the Teacher table using the Form Wizard. 1. Open the sample database SCHOOL4.MDB if it is not already open. 2. In the Database window, click the Forms entry in the Objects list. 3. Select the Teacher table. 4. Select the Form Wizard format and click the OK button. 5. When the wizard asks which fields to include, click the All button (marked with >>); click the Next button. 6. Select the Columnar layout and click the Next button. 7. Select a style for the form and click the Next button. 8. Give the form the title Teacher Records and click the Finish button. Note: this title will be used to name the form as it is being saved. 9. Close the form and save any changes if prompted to do so. Creating forms in the Design View is a little more complicated and requires a more indepth discussion of the design screens, options, and tools that are available within Access. These items will be covered in the next section.

Activity 3: Environment overview

1. Open the Teacher Records form in Design View 2. Make sure the ruler, grid, toolbox, and field list are visible. 3. Move the floating field list to the side if you need to. 4. Shape your floating toolbox to appear as the one on page 9. 5. Put your mouse over each tool to see the tool tips appear. 6. Change back and forth between Form view, Datasheet View, and Design View. 7. While in Form view, perform a Print Preview.

Designing a Form
Views in the Form Window The form window has four possible views Design view Design view is used to access the design screen to create a new form or to change the structure of an existing form. Commands: - Select form and click Design button in the database window toolbar. - Right mouse click on form and select Design view shortcut. - If form is open in Form view, right mouse click in title bar and select the Design View shortcut

Form view
Form view is used to enter, change, or view data using the form itself. In form view, you can usually see all of the fields for one record at once. Commands: - Select form and click Open button in the database window toolbar. - Double-click a form object. - Right mouse click on a form object and select Open shortcut. - If the form is open in Design view, right mouse click in title bar and select the Form View shortcut

Datasheet view

Datasheet view is used to view, enter, or change data using a datasheet. You can usually see many records at once, but it does not display pictures or other OLE objects. Commands: - In Form view, right mouse click in the title bar and select the Datasheet View shortcut. - In Design view, right mouse click in the title bar and select the Datasheet View shortcut.

Print preview
It is used to see how a form will look when printed. Commands: - Print preview is available from the File menu. - Click the Print Preview button on the toolbar

Inserting Controls into the Form

A control on a form is classified by the source of the information that it displays. Bound controls are connected or linked to a data field in a table or query. Unbound controls do not have a source of data. They are used to display labels or instructions. Calculated controls use an expression as the control source and display the results of the expression.

Control Toolbox
The Toolbox is a special type of toolbar that is used to create controls. The toolbox is automatically displayed when you enter the form design view. You can move the toolbox around the work area by dragging it or you can click on the control box to close it. When a tool is in use, it is outlined with a small square indicating it is active. Option Group: if you are going to use this, create it before you create the buttons to go inside it. Select the tool and draw the box on your form. Then create option buttons, check boxes, or toggle buttons. When a command button is clicked on a form, the button will run a program or macro or perform some operation such as go to first record or close form. Toggle Buttons, Option Buttons, and Check Boxes work best for yes/no, true/false type variables. When the item is selected, the value is True or 1. When it is left blank, the value is False or 0. These buttons can be used as part of an option group to create mutually exclusive choices. In a group, the first button created will have the value of 1, second button's value will be 2, etc. 9

Creating a bound text box control

There are two ways to create a bound control. Either of these methods result in a pair of controls: a bound control used to display data from the table and an unbound control used to label the bound control. The easiest method is to drag the field from the field list to the form, creating a text box control by default. The control will be assigned a label that is the same as the field name or caption. It also inherits most of the properties from the connected field. 1. Open the form in Design view. 2. Select the Field List command from the View menu. This displays the field list in a separate window. 3. From the field list, drag the field(s) to the desired position on the form.

Alternatively, you can create the control using the toolbox and then assign the field name in the controls property sheet. 1. Open the form in design view. For a new form, specify the table or query that is to be connected to the form. 2. Select the control from the toolbox and position it on the form. 3. Click on the control with the right mouse button and choose Properties, or double-click on the control and select Properties, or click on the control to select it and then click on the Properties button. 4. Select the Control Source property and select the field from the pull down menu.

Activity 4: Toolbox overview

1. Open the Teacher Records form in Design View if it is not still open 2. Drag a field from the field list onto the form. 3. Use the Text tool to add some text to the form. 4. Use the Text Box tool to add an unbound field to the form. 5. Practice using the utility functions listed on the next page: select, move, resize During the design process, controls may need some minor changes in their size or position. The process of selecting, moving, sizing, or copying is the same for all types of controls on both forms and reports.

Selecting You can select a control by clicking on it with the mouse button. To select several controls, hold down the Shift key while clicking on the objects. You also use a "marquee" (with the pointer tool selected, draw a box around the objects) to select multiple objects. The selected item(s) will have sizing handles around the edges. Moving a control Click on the control when the cursor looks like a flat hand and drag it to a new location. Moving a label or data box To move a label or data box separately, click on the larger square in the upper left corner of the object and drag & drop while the cursor looks like a pointing finger. Resizing a label or data box Position the cursor over one of the square boxes (sizing handles) and it will change into a double arrow. Drag and drop this arrow cursor to change the size of the control. Copying and deleting Select the control and choose the Duplicate command from the Edit menu to obtain a duplicate of the control directly below the original. You can also select the control and use the standard Cut, Copy, and Paste commands, or you can press the Delete key to remove the selected control(s).

Activity5 - Creating and Modifying a Form without a Wizard

1. In the Database window, click the Forms entry in the Objects list. 2. Click the New button in the toolbar. Select the Design View option. 3. Select the STUDENT table. Click the OK button. 4. Make sure the toolbox, ruler, field list, and grid are displayed. Use the View menu to turn these display options on / off. 5. Establish the size of your form. Make it 4 inches across and 2.5 inches deep. Drag & drop the pages edges to change its size.

6. Put 3 text box controls on your form. Open the Field list (View menu), then drag & drop S-FName, S-LName, and S-Score from the field list box to your form. 7. Position the fields on the form (drag while the cursor looks like a hand). 8. Make at least one of the data boxes wider (click on a white box to select it, drag & drop a black square resizing handle). 9. Move a label in relation to its data box (click on the big square in the upper left corner). 10. Save your form by clicking on the Save button. 11. Change the background color: Right mouse click on the background area and choose the Properties shortcut. Click the Format tab and then click in the Back Color field. Click the 3 dots (...) and select a color from the dialog box. Click the OK button and close the properties dialog box. 12. Change a label text color Right mouse click on a label and choose the Properties shortcut. Click the Format tab, scroll down click in the Fore Color field. Click the 3 dots (...) and select a color from the dialog box. Change other characteristics (font, style) of the text as desired. Close the properties dialog box. 13. Save your form again. Keep this form open, we will add other controls to it as we work through this section.

Form Sections
Microsoft Access forms can have up to five sections that can be entered in any combination. It is possible to have different sections for different views of the form Microsoft Access forms can have up to five sections that can be entered in any combination. It is possible to have different sections for different views of the form Form Header The form header displays a title for the form along with control buttons and/or instructions. In form view, the header appears at the top of the screen. When printed, the header appears at the top of the first page. The header is not used in datasheet view. Page Header The page header is only used on printed forms and appears at the top of each page. It is used to display titles, column headers, and other information. Detail Section The detail section is used to display records of data. It will display one or more records on a single screen or page. Page Footer The page footer is only used on printed forms and it appears at the bottom of each page. It is used to display data like the page number or date. Form Footer The form footer appears at the bottom of the screen in form view and at the end of the printout when the form is printed. It is not used in the datasheet view.

Notes on using the form sections

The size of a section can be changed by dragging the edges in design view. To add or remove sections to a form, choose Form Header/Footer or Page Header/Footer from the View menu in design view. Like other objects in the database, form sections have properties. In design view, right click on the section or select View / Properties from the menu to access the properties.

Activity 6 - Add a header to your form

1. Open your Student Form created in the previous activity. 2. Go to the View menu and select Form Header and Footer. 3. Use the Label tool to insert some text into the Form Header. 4. Modify the text's location and appearance if necessary. 5. Adjust the size of your header. 6. Save and view your form

Setting control properties

Throughout Access, properties are used to determine the look or characteristics of objects. Controls on forms also have properties that can be used to change the structure, appearance, and contents of the control. Control property sheet: To see the control property sheet, right click or double click on the control. You can also select the control then click on the properties button. Note that if the control has an attached label, it has a separate set of properties. Changing several controls You can change the properties of several controls at once by displaying the property sheet for one control then select additional controls of the same type (hold down the shift key and click on the other controls). Changing default properties You can change the default properties for a type of control by displaying the property sheet for one control and then click on the tool type in the toolbox. Access will display the default properties for that type of control.

Data display formats The way the data is displayed in the control can be changed by setting the Format property of the control. Instructions on the Status Bar You can display messages on the status bar by changing the StatusBarText property of the control. Validation of data To avoid mistakes when entering data into forms, you can alter the DefaultValue, ValidationRule, ValidationText, and InputMask properties. Multiple lines in text boxes Multiple lines of text can be placed in a text box by sizing the box. If desired, the ScrollBar property can be set to include scroll bars in the text box.

A bound control will inherit the lookup, format, input mask, caption, default value, validation rule, validation text, and decimal places properties from the underlying table fields. However, the validation rule, validation text, and default value settings will not appear in the property sheet for the form. Any entries made in the property sheet will override or append to these properties.

Customizing the Form

Access offers a wide variety of controls that can be used to make your form easier to use and more visually appealing. Some of the more common or useful controls are: Using a list box or combo box: A list box allows you to make choices from a simple list of values. A combo box is like a list box and a text box in one control; you can choose from the items in the list or type in your own item like a text box. In either case, the list can be field values from a table or query or it can be values entered into a value list. 1. From the View menu, turn off the Control Wizard if you plan to create a list box. 2. In the toolbox, select either the List Box or the Combo Box. 3. Click and drag (draw a box) on the form where you want to place the control. 4. Set the Control Source property of the box to bind it to the desired field. 5. Set the RowSourceType property to indicate what type of data should be displayed in the control. 6. Set the RowSource property to indicate the source of the data. When creating a list of values, place them in the RowSource property separated by semicolons.

Activity 7 - Add a Combo Box and a List Box to your Student Form
1. Make sure the Control Wizard button is pushed in (active). 2. Click on the Combo Box tool and draw a thin box on your form. 3. Answer the Wizard's questions: you will supply the values (for major); choose S-Major; etc. 4. Move the combo box label over top of the data portion. 5. Now turn off the Control Wizard. 6. Click on the List Box tool and draw a box on your form. 7. Click on the Properties button to display current properties for this control. 8. Click on the Data tab and set Control Source to the desired field: S-Hair. 9. Under the Data tab, set Row Source Type to be: Value List. 10. Next to Row Source, enter: red; blonde; brown; black; random; bald; purple. 11. Close the Properties box. Move or resize your list box control as needed. Save your form.

Check Boxes, Option, and Toggle Buttons:

Use the check box, option button, or toggle button controls as stand alone controls to set Yes/No values. When selected, these controls indicate a Yes or True condition. When cleared, they indicate a No or False value. A check box appears checked when selected and unchecked when cleared. An option button contains a dot when selected and is empty when cleared. A toggle button appears down when selected and up when cleared.

Note: These controls are most useful when bound to a Yes/No field. To speed data entry, use the control's DefaultValue property to make the most common choice the default.

Option Groups
You can use a group of option controls to present a limited set of alternatives. An option group consists of a group frame that contains a set of check boxes, option buttons, or toggle buttons. Only one option in a group can be selected at a time (mutually exclusive choices). One value in the group can be set as the default. Option controls used in a group have different properties from those used stand alone. In a group, each option has its Option Value property set to a number that indicates its value in the bound field. The group frame, which must be created first, is bound to the table field. Option Value properties, which can only be set to numbers, not text, are the values that are placed in the bound field when that option is selected.

Activity 8 Create a Check Box for Paid and an Option Group for Gender
1. Select the Check Box tool and click on your form. 2. Select the control and bring up its Properties. 3. Under the Data tab, select Paid as the Control Source field. 4. Close the Properties box. 5. Edit the label to say: Paid Fees. 6. Make sure the Control Wizard is active (button looks pushed in). 7. Click on the Option Group tool and draw a control box on your form. 8. The Wizard starts asking you questions: Label names: type Female in the first row, press Tab, and type Male in the second row [next]. You do not want a default choice - click the Next button. Use the default values of 1 and 2 - click the Next button. Store the value in S-Gender (click in the second choice and select S-Gender from the list) click the Next button. Choose Option buttons and pick a style of your choice for the group itself - click the Next button. Specify Gender as the caption and click the Finish button. 9. Move and/or resize your 2 new controls as needed. 10. Save your form.

Command Buttons
A command button is placed on a form to initiate an action. To make a command button do something, you need to write a macro or code module. The command button wizard will create some common buttons for you and will create the underlying Access Visual Basic event code. 1. From the toolbox, select Control Wizards. 2. In the toolbox, click on the command button. 3. Click on the form where you want the button placed. Access will display the wizard dialog box. 4. Follow the directions in the dialog box to create the event code.

Page Breaks
A page is defined as a screen in Form View or a physical page on a printed form or report. The Page Up and Page Down keys are used to move past page breaks. Page breaks are created by selecting the Page Break tool from the toolbox and clicking on the form or report where you wish the page break to appear.

Notes

A page break is active in Form View only when the DefaultView property is set to Single Form. To size a form's window so that it shows only one page, choose Size To Fit Form from the Window menu in Form View.

Special Design Effects

Access contains a number of design tools on the Format toolbar and in the Toolbox that can add color, shading, lines, or fonts to the form to enhance its appearance. Three dimensional appearances for controls can be created by creating the control, then selecting an option from the formatting tool bar. Fonts on a control can be changed by selecting the control and altering the font information on the formatting toolbar. You can also select the alignment buttons on the toolbar to position the text on the control or the attribute buttons to boldface, italicize, or underline text Colors (background, foreground, or border) and Borders can be set from the formatting toolbar. Form controls can also be sculptured from the rightmost button on the form design toolbar. The Rectangle and Line tools in the toolbox can be used to add emphasis to important information or to isolate sections of the form. Objects (photos, graphics, etc) can be placed in a form by creating a box with an Image tool or an Unbound Object Frame tool from the toolbox. If you want different objects for different records, then you must link the object to a field in the underlying table or query.

Calculated controls
A calculated control uses an expression as its control source. The expression can include values from data fields, other controls, or constants. Access does not store the result of the expression, it recalculates it every time the form is displayed. Precede the expression with an equal (=) sign when it is placed in the control source property. The expression can be typed directly into the property setting area or it can be created in the expression builder. The builder can be activated by clicking on the Build (...) Button. If you are entering the expression directly in the Control Source box, press Shift F2 (zoom) if you need more room to see what you're typing. Click on OK when you're done.

Activity 9 - Create a Calculated Control to compute student's age

1. Create an unbound text box control on your form. 2. Change the label to say: Age 3. Click on the data portion of the new unbound control and bring up its Properties. 4. Click on the Data tab. 5. Next to Control Source, press Shift F2 to zoom. 6. Type =Cint((Now()-[S-BDay])/365) and click the OK button. 7. Close the Properties box and switch to Form view to review your results. 8. Save your form.

Subforms
A subform is simply a form within a form. The Control wizard has the ability to walk you through the creation procedure: 1. In the Design view of the form, select the Control Wizard button from the toolbox. 2. Click on the Subform/Subreport toolbox button. 3. Click and drag a rectangle on the form where the subform is to appear. The wizard will activate when the rectangle is complete. 4. Respond to the questions posed by the wizard to complete the procedure

Activity 10: Create a Query and a Subform

1. Switch to the Queries tab and create a new query in design view. 2. Include these three tables: Class, Attend, Student 3. Include these fields: S-ID from the Student table, C-Name, Grade and Status 4. Save the query as StudentAttend 5. Return to your Student form in design mode. Drag the bottom of the form down to get some more room. 6. Make sure the Wizard button is selected. 7. Click on the Form/Subform button and drag a box on top of your form. 8. Wizard responses: Use existing query "StudentAttend". Select all fields. Show StudentAttend for each record in Student.

Data Management in Forms

While working in the form view, some of the common data management features, such as sorting and filtering, are available by rightclicking on the desired field and selecting from the shortcut menu that appears. An option also exists to remove the filter or sort and restore the data to its original order. You can also use the Find command (Edit>Find or Ctrl F).

Activity 11: Filter, Sort, Find

1. Apply a filter to display only Journalism majors. Browse through the filtered records. Remove the filter. 2. Sort the records by last name. 3. Sort the records by test score, with the highest score being displayed first. 4. Find a student whose last name begins with M. (hint: use an advanced Filter and a wildcard in the criteria). Remove the filter.

Create A Switchboard Form

When you use the Database Wizard to create a database, the wizard creates a switchboard that makes it easy to navigate between the forms and reports in your database. If you want to build the same type of switchboard for a database you created yourself, you can use the Switchboard Manager. 1. On the Tools menu, point to Database Utilities, and then click Switchboard Manager. 2. If Microsoft Access asks if you'd like to create a switchboard, click Yes. 3. In the Switchboard Manager dialog box, click the Edit button to edit the default Main switchboard. 4. In the Edit Switchboard Page dialog box, click on New to add a new item to the list of possible actions. 5. In the Edit Switchboard Item dialog box, type the text for the first switchboard button in the Text box, and then choose a command in the Command box. 6. Depending on which command you click, Microsoft Access displays another box below the Command box. Click an item in this box, if necessary. For example, if you chose Open Form In Edit Mode in the Command box in step 5, click the name of the form you want to open in the Form box, such as STUDENT Form, and then click the OK button.

7. Repeat steps 4 through 6 until you've added all the items to the switchboard. If you want to edit or delete an item, click the item in the Items On This Switchboard box, and then click Edit or Delete. If you want to rearrange items, click the item in the box, and then click Move Up or Move Down. 8. Click Close. 9. Look for your Switchboard under the Forms tab. 10. You can edit it in design view to change the text, colors, etc. Be careful: do not edit or delete the Switchboard table which controls your options on this form. 11. You can make the Switchboard open automatically when the database is opened can (and optionally make it the only way into the database objects) by going to the Tools menu and selecting Startup. 12. Change Display Form to Switchboard. 13. Uncheck Display Database Window if you don't want other users to see it.

This Slide was Prepared by the Aim-Aop II-A Group II Member List: Homer Cruz Maricar Sabado Donna Lyza Pring Katrin Gimpaya Aira Babatio Juvy Ann Malit Justin Garcia Roldan Remy Lopez Arjan Ebuenga Dale Myron De Leon Jan Kris Vergel De Dios Vanessa April Orain Paulo Serrano

Thank You!!! SY 2010-2011