So far you have accessed the data in a table directly through a data sheet or indirectly using a query. In this tutorial you will be introduced to the use of forms (or screens). These exercises will show you to build customised data entry or enquiry screens, enable you to enter data into more than one table through a single screen and show you how to build simple menu screens to implement an interface design.

You will use a powerful feature of MS-ACCESS, the form wizard, to help you construct forms.

A form to allow the entry of students into the Student table.

From the Database window, select the Forms tab, then New → Form Wizard and choose the Student table.

The form wizard will now begin to ask you questions about the form you wish to design

• Select all fields from the Student table then press the Next button
• We want a Columnar layout (you can try the others later if you wish)
• Choose a style you like then press the Next button
• Give your form the name Student Details then press the Finish button

When finished you should have a form that allows you to enter and edit student data.

By clicking on the Design View and Form View icons in the toolbar you can swap between design and data entry views.

Select Form View and using the Record Navigation controls at the bottom of the form move backward and forward through the records of your table.

Select Design View and make the labels more appropriate.

Save the form as ... StudentFRM

Now use the form to add the following two new students. Use the Record Navigation controls to go to the end of the table until a blank form form appears and then enter the following data.

951000  FRED MARTIN in tutorial group 3
951001  AMY HARDING in tutorial group 2

You are now going to enhance the design of StudentFRM.

To modify a form, highlight the form name in the database window and select Design.

The form design window should now open and the form design toolbox should be displayed at the bottom of the screen. Check that the form control wizard is on (it should be highlighted). If you run the mouse slowly across the tool box buttons, each function should be shown.

A general comment

Each component (or object) in the screen design (including the background and the form itself) can be selected and by clicking with the right mouse button, a properties list can be accessed. These properties cover everything from display characteristics to form use and can be set accordingly. You are encouraged to use the on-line help facility to investigate their use.

Really making the form more user-friendly:

Re-size the design area and from the View menu, turn off the form and page headers/footers. Give yourself plenty of work space. We will add a number of different controls to our basic form:

1. First we will replace the text box for the entry of gender with an option group by:
   • select and delete the text box (and label) for gender
   • select option group from the tool bar and drag to the relevant vacant position in the form.
   • the wizard will now ask you some questions about the option group.
   • the label names will be male and female
   • the default will be male (change this if you want
   • you will wish to store the selection in gender
   • choose a suitable control ie. style and button types (experiment with these)
   • enter ’Gender’ for the caption
   note: if your going to use option groups then the data type of the field in the table must be integer.
   
   
2. If you need to change the labels, simply click on the label to highlight it, click on it again to enable editing of the text. The text can also be edited in the Caption item in the label’s Properties Window. Font sizes and font styles can be set in the Formatting Toolbar or in the Properties Window.
   
   
3. to display the current date on the form, use the Text Box tool to add new text box to the form. Access the properties list and for the Control Source property, enter:
   
   =Format$(Now, "dd-mmm-yy")
   Make the label of this entry ’Date’ as can be seen on the following page.
   
   
4. to add a button to close a form, use the Command Button tool. In this case you wish to carry out the form operation, close form. You can specify either a graphic or text on the button.
   
   
5. to add a pull down list for tutorial selection, use the Combo Box tool. The combo box will look up values in the Tutorial table. The fields to be displayed from the Tutorial table will be tute_no, tute_time and tute_room. The selected field,tute_no from the Tutorial table will be stored in tute_no in the Student table.
   Choose the view mode to view the form.

The new Student Form is navigable via sequential access to find the appropriate student. Try adding a comboBox to "find a record based on a selected value" to enable direct access via student name.

Exercise:

Design suitable forms that will similarly allow access to the Assessment and the Tutor tables. Name them AssessmentFRM and TutorFRM respectively.

A form that accesses more than one table

In this exercise you will use the wizard to create a form which shows student data from the Student table and the marks for each piece of assessment (from the Result table). It is a Main/Subform form.

From the database window select the Forms tab then New→Form Wizard and choose the Student table.
You need to read the wizard instructions very carefully...

• Select all fields from the Student table then all fields from the Results table except for the stud_id field.
• Press the Next button
• In the second page of the Form Wizard we want to:
  • View the data by student, and
  • Select the Form with subForms layout.
• A datasheet layout usually works best in subforms
• Select a Style
• Name the form StudentResultFRM and the subform ResultSUBFRM

Exercises

Use the form, StudentResultFRM to attempt to enter the following. Give reasons for those where you were unsuccessful.

1. Student, 898265, Angus Fraser received 8 for assessment number 3.
2. Student 898265 received 15 for assessment number 4.
3. Student 904372, Simon Owen received 20 for assessment number 2 (try to add this as an extra assessment).
4. Student 904372 received 15 for assessment number 5.

Extension: Attempt to make the following enhancements to StudentResultFRM

• place the current data on the form (make sure some data is visible in the form display)
• place a button on the form that allows the user to close the form.
• use more meaningful and descriptive text on the form.
• Rather than enter an assessment number, attempt to modify the subform so that the user can access a pull down list of the assessment descriptions.

A menu form

Our application is getting rather large now with many different forms to use.A menu to select the appropriate form from is a good step to providing a user-friendly application. In the database window ...

A blank form design window will then open. Ensure the toolbar wizard is on.

Use the command button tool to place a command button on the form. This button will open the form StudentResultForm. Use the command button tool to place further buttons on the form that will open the other forms you have created. Ensure you also have a button that closes the menu form.

Add a label for the heading "Student Record System Main Menu". Group your buttons under two more headings: "Forms" and "Reports".

You may even wish to further extend the menu application by preparing a menu structure which has a Forms Menu that uses the StudentFRM, AssessmentFRM, TutorFRM and StudentResultFRM. (you will need to add in another form called Forms Menu or something similar such as ’Data Maintenance’)

Enhance the design of the menu form (eg colours, positioning of buttons, heading, use of icons and text, display of current data etc). Save this form as Menu1FRM.