Practical Objectives

Forms, Input Masks Creating and modifying forms

Searching data through forms
and Validation Rules Input masks
Applying validation rules
Find sort and filter records
MS ACCESS 2016
LECTURE 4

Forms Form controls

Provide a better interface to capture, view, and Bound control ‐ A control whose source of data is a field in a
update data in tables. table or query. For example, a text box on a form that displays
an employee's last name might get this information from the
Manipulate one record at a time rather than all Last Name field in the Employees table.
data contained in table
Unbound control ‐ A control that doesn't have a source of data
Can provide more powerful data validations. (such as a field or expression). For example, a label that displays
the title of a form is an unbound control.
Controls can be bound, unbound or calculative.
Calculated control ‐ A control whose source of data is an
Formatting and input masks can be used for expression, rather than a field. An expression can be a
entering valid data in specified format. combination of operators (such as = and + ), control names, field
names, functions that return a single value, and constant values.

Creating a Form Form Design View

Forms can be
easily created by
selecting the
table, clicking
Create | Forms.

ISTN101 ‐ Databases ‐ Lecture 4 ‐ Forms, Input Masks and Validation Rules 1

 Adding a Calculation Field
Adding Fields to a Blank Form (Unbound Control)
 Click on Create ‐> Blank Form ‐> Add Existing Fields
 Drag desired fields on form area
Select new
 In Property Sheet box click‐> Control Source ‐> add source textbox ‐>
field from the list. Click Control
Source ‐>
enter formula
into
Expression
Builder as
shown below

Input Masks Input Masks (2)

Used to ensure user enters data in a valid format Add an Input Mask to a Table Field
only. 1. In the Navigation Pane, right‐click the table and click
Can be used for date, time, phone nos., currency Design View on the shortcut menu.
formats data entry. 2. Click the field where you want to add the input mask.
Used for data validation. 3. Under Field Properties, on the General tab, click the
Input Mask property box.
Prevents corrupt data entry.
4. Click the Build button Builder button to start the Input
Simplifies input Mask Wizard.
New input masks can be designed. 5. In the Input Mask list, select the type of mask that you
want to add.

Input Masks (3) Input Masks (4)

Note: If you use an input mask for a Date/Time field, the
You can use input Date Picker control becomes unavailable for that field.
masks with fields
that are set to the
Text, Number
(except
ReplicationID),
Currency, and
Date/Time data
types.

ISTN101 ‐ Databases ‐ Lecture 4 ‐ Forms, Input Masks and Validation Rules 2

 Input Masks (6)
Character Explanation

Input Masks (5) 0

9
User must enter a digit (0 to 9).

User can enter a digit (0 to 9).

Click Edit List ‐>Enter Description of mask, enter

# User can enter a digit, space, plus or minus sign. If skipped, Access enters a blank space.

L User must enter a letter.

input mask i.e.(000)000‐0000 for numerical digits ? User can enter a letter.
only to display the following format (031)260‐6801. A User must enter a letter or a digit.

For letters ‘L’ symbol specifies strictly a character a User can enter a letter or a digit.

e.g LLLLLLLL means 8 characters. & User must enter either a character or a space.

C User can enter characters or spaces.

Place holder will prompt for and replace entry .,:;‐/
Decimal and thousands placeholders, date and time separators. The character you select depends
on your Microsoft Windows regional settings.
> Coverts all characters that follow to uppercase.

< Converts all characters that follow to lowercase.

! Causes the input mask to fill from left to right instead of from right to left.

\ Characters immediately following will be displayed literally.

"" Characters enclosed in double quotation marks will be displayed literally.

Validation Rules Field Validation Rule (1)

There are three types of validation rules in Access: For example, a date field might have a validation
rule that disallows values in the past.
1. Field Validation Rule ‐ You can use a field
validation rule to specify a criterion that all valid Quick examples:
field values must meet. ◦ Disallow date values in the past: >=Date()
◦ Generally accepted email format: Is Null OR ((Like
2. Record Validation Rule ‐ You can use a record "*?@?*.?*") AND (Not Like "*[ ,;]*"))
validation rule to specify a condition that all valid ◦ Number less than or equal to five: <=5
records must satisfy. ◦ Currency field can't be negative: >=0
3. Validation on a form ◦ Restrict character length in string:
Len([StringFieldName])<100

Field Validation Rule (2) Field Validation Rule (3)

Create a field validation rule
1. Select the field that you want to validate.
2. On the Fields tab, in the Field Validation group,
click Validation, and then click Field Validation Rule.
3. Use the Expression Builder to create the rule. For
more information about using the Expression Builder.

ISTN101 ‐ Databases ‐ Lecture 4 ‐ Forms, Input Masks and Validation Rules 3

 Field Validation Rule (4) Record Validation Rule (1)
Create a message to display for field input that is not You can compare values across different fields using a
valid record validation rule. For example, a record with two
1. Select the field that needs a message for invalid date fields might require that values of one field always
input. The field should already have a validation rule. precede values of the other field (e.g., StartDate is
before EndDate).
2. On the Fields tab, in the Field Validation group,
click Validation, and then click Field Validation Quick examples:
Message. Ensure the end date doesn't come before the start
3. Enter an appropriate message. For example, if the date: [End Date]>=[Start Date]
validation rule is >10, the message might be “Enter a Enter a required date that occurs no more than 30 days
value that is less than 10.” after the order date: [RequiredDate]<=[OrderDate]+30

Record Validation Rule (2) Record Validation Rule (3)

Create a record validation rule
1. Open the table for which you want to validate
records.
2. On the Fields tab, in the Field Validation group,
click Validation, and then click Validation Rule.
3. Use the Expression Builder to create the rule.
For more information about using the
Expression Builder.

Record Validation Rule (4) Validation on a Form

Create a message to display for record input that is not You can use the Validation Rule property of a
valid control on a form to specify a criterion that all
1. Open the table that needs a message for input that is values input to that control must meet.
not valid. The table should already have a record The Validation Rule control property works like a
validation rule. field validation rule. Typically, you use a form
2. On the Fields tab, in the Field Validation group, validation rule instead of a field validation rule if
click Validation, and then click Record Validation the rule was specific only to that form and not to
Message. the table no matter where it was used.
3. Enter an appropriate message. For example, if the
validation rule is [StartDate]<[EndDate], themessage
might be “StartDate must precede EndDate.”

ISTN101 ‐ Databases ‐ Lecture 4 ‐ Forms, Input Masks and Validation Rules 4

 Add a Validation Rule to a Create a Validation Rule for a
Control on a Form Form Control
A control can have a different validation rule from the table field Right‐click the form that you want to change, and then
to which the control is bound. The form rule is applied, and then click Layout View.
the table rule is applied. If the rules are mutually exclusive, they Right‐click the control that you want to change, and then
prevent you from entering any data at all. click Property Sheet to open the property sheet for the control.
For example, suppose you apply the following rule to a date Click the All tab, and then enter your validation rule in
field in a table: <#01/01/2010# the Validation Rule property box.
But you then apply this rule to the form control that is bound to Tip: Click the Build button to start the Expression Builder.
the date field: >=#01/01/2010#
The date field now requires values earlier than the year 2010, Enter a message in the Validation Text property box.
but the form control requires dates have that year or later, thus
preventing you from entering any data at all.

Test Existing Data Against a

New Validation Rule Sorting and Filtering Form Data
If you add a validation rule to an existing table, you might want Data can be sorted ascending or descending
to test the rule to see whether any existing data is not valid.
1. Open the table that you want to test in Design View.
Data can be filtered based on any field.
2. On the Design tab, in the Tools group, click Test Validation Filtering is a way to view selected data based on a
Rules. specified criteria.
3. Click Yes to close the alert message and start the test. Click Advanced‐> Filter By Form option‐> Select
4. If prompted to save your table, Click Yes. the field value ‐> click Toggle Filter. The filtered
record will be visible.
5. You might see a variety of other alert messages as you
proceed. Read the instructions in each message, and then
click Yes or No, as appropriate, to complete or stop the
testing.

Filtering Data by Form Filtering Form Data…

1. Select Filter By Form
2. Select a CustomerNo
3. Click on Toggle Filter

ISTN101 ‐ Databases ‐ Lecture 4 ‐ Forms, Input Masks and Validation Rules 5

 YouTube Videos
Modifying Tables, Creating Queries, Forms & Reports
https://www.youtube.com/watch?v=1r47z7YRYwM
Input Masks
https://www.youtube.com/watch?v=kzTntQd5SAA
Applying validation rules
https://www.youtube.com/watch?v=08YQMEsK_xo
Finding and sorting data
https://www.youtube.com/watch?v=YK‐5DiFdXCM

ISTN101 ‐ Databases ‐ Lecture 4 ‐ Forms, Input Masks and Validation Rules 6