CSE1IS Information Systems

Tutorial – Week 3

STRS – Adding Input Masks and Validation Rules

In the previous tutorial you created the database for the Student Tutorial Record System (STRS). The database contained a number of related tables. In this tutorial we begin the process of entering data into the tables. We MUST ensure that the data in the tables is accurate and valid. To do this we will apply an Input Mask and Validation Rules to the fields in the tables in our database.

Run Microsoft Access and open your STRS database.

Open the Student table in Design View. Below the list of fields making up the Student table you will see a list of Field Properties for the currently selected field. We are interested in the Input Mask, Validation Rule and Validation Text Field Properties.

Input Masks

In this Field Property we can enter a pattern which all data entered into the field must match. The pattern is also displayed to the user when entering data to assist with the entry of appropriate data. For example an Input Mask of LLLL requires 4 characters between A and Z inclusive to be entered. An Input Mask of >LLLL causes all 4 characters to be converted to uppercase.

For a list of Input Mask characters place the cursor inside the Input Mask Field Property and press F1 for Help. Select TextBox.InputMask Property from the suggested Help topics. Scroll down the Help window to find a list of Input Mask characters and examples of their use.

Enter the Input Mask for the fields in the Student and Tutorial tables as indicated in the tables below.

Student Table

Field	Acceptable Data	Input Mask
stud_id	Six compulsory digits. Entry required.	000000
stud_surname	Capitals. Two or more characters. Maximum of 15 characters. Could include a space. Must be entered.	>LLCCCCCCCCCCCCC
stud_givenname	Capitals. Maximum of 15 characters. Could include a space.	>CCCCCCCCCCCCCCC
gender	1 – male. 2 – female. Must be entered.	0
tute_no	Single digit. Must be entered.	0

Tutorial Table

Field	Acceptable Data	Input Mask
tute_no	Single digit Entry required.	0
tute_time	Eg. Wed 02.30pm. Three character to begin, first to be a capital. Then a space. Two digits, a decimal place, then two more digits. Two characters to end.	>?<??99\.99<??
tute_room	Capitals. Maximum of 5 characters. Could include a space.	\B9\.99
tutor_no	Single digit. Must be entered.	0

Switch to the Datasheet view. Highlight the new record (the one with the *) and add some data to test your Input Masks. When you have finished testing delete the records you have added.

Validation Rules

In this Field Property we can enter a rule for some fields that the data must satisfy. For example, in the gender table we can set a rule so that an entry into a field is either 1 or 2. To view some examples of Validation Rules place the cursor inside the Validation Rule Field Property for a field and press F1. Choose Textbox.ValidationRule property from the Help topics suggested. Scroll down the Help window a bit to view some examples of Validation Rules.

Enter the Validation Rules for the fields in the Student and Tutorial tables as indicated in the tables below. Also add the Validation Text which will appear if the user attempts to add data which does not pass the Validation Rule.

Student Table

Field	Validation Rule	Validation Text
stud_id	>= 150001 And <=159999. (The unique value requirement will be taken care of with the (No Duplicates option in the Indexed Field property)	Must be an ID Number between 150001 and 159999.
stud_surname
stud_givenname
gender	Must be a 1 or 2.	Must be 1 for male or 2 for female.
tute_no	No rule needed here. The relationship between the Student and Tutorial tables in the database ensure that a tute_no cannot be entered in the Student table that doesn’t match a tute_no in theTutorial table.	0

Tutorial table

Field	Validation Rule	Validation Text
tute_no	Between 1 And 9.	Must be a number between 1 and 9 inclusive.
tute_time
tute_room
tutor_no	The relationship in the database between the Tutorial and Tutor table ensures that a tutor_no in the Tutorial table must match a tutor_no in the Tutor table.

Switch to the Datasheet view. Highlight the new record (the one with the *) and add some data to test your Validation Rules. When you have finished testing delete the records you have added.

Other Tables

Below are tables describing acceptable data for the fields in the other tables in your database. Use these tables to enter Input Masks and Validation Rules and Text for the Tutor, Assessment and Results tables.

Tutor Table

Field	Acceptable Data
tutor_no	Between 1 And 9 inclusive.
t_name	Capitals. Less than 16 characters.
t_room	Of the form B1.18 for example.
t_phone	4 digits beginning with 7

Assessment Table

Field Name	Acceptable Data
assess_no	Between 1 and 4 inclusive
a_desc	Any
max_mark	Any number.
weight	Any number less than 100

Results Table

Field Name	Acceptable Data
stud_id	Must match a value in the stud_id field in the Student Table.
assess_no	Must match a value in the assess_no field in the Assessment table.
mark	Numeric. Must be less than or equal to the maximum mark for the corresponding assessment in the Assessment table.

If you have created a Course table then you need to add Input Mask and Validation Rule Field properties.