Advanced Forms and Switchboards - Exercises



UCL

Education & information support division

information systems

Access 2003

Advanced Access

Forms and Switchboards

Exercises

Contents

Exercise 1: Create an Employees Form 1

Exercise 2: Forms based on Parameter Queries 1

Exercise 3: Forms based on multi-Table Queries 1

Exercise 4: Adding an unbound Combo Box 2

Exercise 5: Adding a bound Combo Box 2

Exercise 6: Changing Control Types 3

Exercise 7: Adding User Instructions 3

Exercise 8: Creating Calculated Fields 4

Exercise 9: Creating a Departments Form 4

Exercise 10: Adding a Command Button using the Wizard 4

Exercise 11: Creating a Switchboard 5

Exercise 12: Creating additional Switchboards 5

Flowchart for Exercises 11 and 12 8

Exercise 13: Startup Options 6

Exercise 14: Creating a simple Macro 6

Exercise 15: Creating a Macro with several Actions 7

Exercise 16: Editing a Macro 7

1. Unless stated otherwise, the following exercises should be done using the Access database exercises.mdb.

Exercise 1 - Create an Employees Form

1. Create a Form based on tbl_Employees using the Form Wizard:

• Add all the Fields from tbl_Employees

• Choose a Columnar Layout

• Name the Form frm_Employees

Exercise 2 - Forms based on Parameter Queries

In this Exercise you will create a Form based on a Parameter Query which enables users to find employees by entering their surname or the first part of their surname.

1. Open the Query qry_Employees_surname in Design View.

Note the Fields included in the Query and the Criteria.

2. Run the Query, enter the text Win in the Parameter dialog box and view the results.

3. Close the Query.

4. Create a new Form using the Wizard:

• Base the Form on qry_Employees_surname

• Add all the Fields from the Query

• Name the Form frm_Employees_surname

In order to complete the following Exercises 3 – 8 you must have completed Exercise 1.

Exercise 3 - Forms based on multi-Table Queries

The existing frm_Employees Form is based on the Table tbl_Employees and displays all the Fields from that Table and all employee records, regardless of whether they are currently employed. It has been decided that frm_Employees will only show current Employees and will display some additional information. In this exercise you will create a new version of frm_Employees based on the existing one and link the Form to a Query which finds current employees.

1. View the Query qry_Employees_Current in Design View.

Which Tables is the Query based on? Which Fields have been included?

Note the Criteria in the Date_Left Field. Which employees will be included in the Query?

2. Make a copy of frm_Employeesand name it frm_Employees_current

3. Open the new frm_Employees_current in Design View.

4. In the Property Window, change the Record Source to qry_Employees_current.

5. Additional Tasks

6. The Fields from the Query which are not in tbl_Employees are not displayed on the Form.

Add these Fields from the Field List.

7. Switch to Form View to see the results of your actions.

Exercise 4 - Adding an unbound Combo Box

In this exercise you will create a Combo Box to find a specific employees on frm_Employees.

1. Open frm_Employees or complete Exercise 1 to create it.

2. Add a new Combo Box to the Form Header:

3. Ensure that the Control Wizard on the Toolbox is on

4. Add a Combo Box from the Toolbox

5. Use the Combo Box Wizard to create a Combo Box which will find Employees based on their Staff_ID and will display the Last_Name and First_Name in the drop-down list (in that order).

6. Give the Combo Box the Label ‘Find a staff member:’

7. View the results of your changes in Form View.

8. Experiment with finding different members of staff.

Additional Tasks

9. Edit the Properties of the Combo Box to display the Staff_ID:

10. View the Properties for the Combo Box in the Property Window

11. Change the Column Width for the first column from 0 to 2 cm

12. Increase the List Width by 2 cm (to approximately 7 cm)

13. Switch to Form View and view the results

14. Tip: To format the Staff_ID in the Combo Box so that it appears as five digits (as the Staff_ID Text Box does in the main part of the Form) type 00000 into the Format Property in the Property Window.

Exercise 5 - Adding a bound Combo Box

1. Notice that the Dept_ID Control is a Text Box. You will replace the Text Box with a Combo Box which displays the Dept_Name but will still store the Dept_ID in the Dept_ID Field.

2. Open frm_Employees or complete Exercise 1 to create it.

3. Delete the Dept_ID Text Box and its associated Label.

4. Add a new Combo Box to display Dept_ID:

5. Ensure that the Control Wizard on the Toolbox is on

6. Add a Combo Box from the Toolbox

7. Use the Combo Box Wizard to create a Combo Box which will display the Dept_Name Field but is linked to the Dept_ID Field

8. Ensure that data entered in the Combo Box will be stored in the Dept_ID Field

9. Give the Combo Box the Label ‘Department’

10. View the results of your changes in Form View.

11. Experiment with changing the data in the Combo Box and view the records in tbl_Employees.

Additonal Tasks for Exercise 5 overleaf.

Additional Tasks

12. Change the sort order of the Combo Box to sort alphabetically by Dept_Name (rather than Dept_ID):

13. View the Properties for the Combo Box in the Property Window

14. Click on the Build Button [pic] to the right of the Row Source Property

• Change the Sort option in the Department Field to ‘Ascending’

Exercise 6 - Changing Control Types

1. Open frm_Employees or complete Exercise 1 to create it.

2. Change the Ethnicity Combo Box to a List Box.

3. Move the Combo Box and/or resize the Form to accommodate the List Box.

4. View the results in Form View.

5. Additional Tasks

6. Change the Disability Check Box to an Option Button.

7. View the results in Form View.

Exercise 7 - Adding User Instructions

1. Open frm_Employees or complete Exercise 1 to create it.

2. Add Status Bar Text or Control Tips to the following Controls by entering the text provided in the Status Bar Property or the Control Tip Property:

|Control |Status Bar Text/ Control |Text to enter |

| |Tip Text | |

|DOB |Status Bar Text |Enter Date of Birth in dd/mm/yy format e.g. 24/12/76 |

|Staff_ID |Status Bar Text |A unique five-digit number |

|Dept_ID |Control Tip Text |This must be an existing Department! |

|Telephone |Control Tip Text |A mobile number is preferred |

3. View your changes in Form View

Additional Tasks

8. The following Fields are important ones you would like users to fill in wherever possible. Change the colour of their borders to Red and add an appropriate Label to the Form to advise users:

Staff_ID, First_Name, Last_Name, Dept_ID

9. Make the text in the Date_Left Field bold so that employees who have left stand out.

Test this by adding a date to the Date_Left Field for any employee.

Exercise 8 - Creating Calculated Fields

You must have completed Exercise 1 to do this exercise.

In this exercise you will create a Calculated Field to display a text message on the Form if an employee has left.

1. Open the Form frm_Employees in Design View.

2. Create a Text Box using the Tool Box.

3. Select and remove the Label from the Text Box.

4. Type the following expression into the Text Box Field:

=IIf([Date_Left] ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download