LO2: Add record on table

This unit to provide you the necessary information regarding the following content coverage
and topics:
 Adding data in a table according to information requirements
 Modifying and deleting records as required
 Saving and Compiling database objects
This guide will also assist you to attain the learning outcomes stated in the cover page.
Specifically, upon completion of this learning guide, you will be able to:
 Add, Modify and Delete Records
 Identify Field Property Setting
 Save & Compile database objects
2.1 Add, Modify & Delete records
A. Ways to Add, Edit, and Delete records

There are several ways to update data in an Access database. You add a record to your database
when you have a new item to track, such as a new contact to the Contacts table. When you add a
new record, Access appends the record to the end of the table.
You also change fields to stay up-to-date, such as a new address or last name. To maintain data
integrity, the fields in an Access database are set to accept a specific type of data, such as text or
numbers.
If you don't enter the correct data type, Access displays an error message. Finally, you can delete
a record when it is no longer relevant and to save space.
You use a form to manually update data. Data entry forms can provide an easier, faster, and more
accurate way to enter data. Forms can contain any number of controls such as lists, text boxes,
and buttons. In turn, each of the controls on the form either reads data from or writes data to an
underlying table field.

Figure 2. 1 Data entry form

Datasheets are grids of data that look like Excel worksheets. You can change data by working
directly in Datasheet view. If you are familiar with Excel, datasheets should be relatively easy to
understand. You can change data in tables, query result sets, and forms that display datasheets.
Typically, you use datasheets when you need to see many records at once.

Figure 2. 2 Datasheet view of the record

 B. Understanding data entry symbols

The following table shows some of the record selector symbols you might see when updating
data and what they mean.
Symbol Meaning
This is the current record; the record has been saved as it appears. The current record is
indicated by a change in color in the record selector.
You are editing this record; changes to the record aren't yet saved.
This record is locked by another user; you can't edit it.
This is a new record in which you can enter information.
This is the primary key field and contains a value that uniquely identifies the record.

C. Lookup data-entry list in MS Access

Perhaps the best way to make sure that data is entered correctly is to create a data-entry drop-
down list. That way, anyone entering the data in your database table can do so by choosing an
item from the list, not by typing it in. This method saves time and prevents invalid data from
being entered. Access offers two ways to create the drop-down list:
 Create the list by entering the items yourself: Go this route when you’re dealing with a
finite list of items that never change.
 Get the items from another database table: Go this route to get items from a column in
another database table. This way, you can choose from an ever-expanding list of items.
When the number of items in the other database table changes, so does the number of
items in the drop-down list because the items come from the other database table. This is
a great way to get items from a primary key field in another table.

2.2 Filed Properties Settings

The Field Properties settings safeguard data from being entered incorrectly. Following is a
description of the different properties (listed here in the order in which they appear in the Design
view window) and instructions for using them wisely. Which properties you can assign to a field
in Access depends on which data type the field was assigned.
 A. Field Size

In the Field Size box for Text fields, enter the maximum number of characters that can be
entered in the field. Suppose that the field you’re dealing with is ZIP code, and you want to enter
five-number ZIP codes. By entering 5 in the Field Size text box, only five characters can be
entered in the field. A sleepy data-entry clerk couldn’t enter a six-character ZIP code by
accident.
For Number fields, select a value for the field size from the drop-down list.

B. Format

Click the drop-down list and choose the format in which text, numbers, and dates and times are
displayed.

C. Decimal Places

For a field that holds numbers, open the Decimal Places drop-down list and choose how many
numbers can appear to the right of the decimal point. This property affects how numbers and
currency values are displayed, not their real value. Numbers are rounded to the nearest decimal
point. The Auto option displays the number of decimal places permitted by the format you chose
on the Format drop-down list.

D. Input Mask

For Text and Date field types, this feature provides a template with punctuation marks to make
entering the data easier. Telephone numbers, social security numbers, and other numbers that
typically are entered along with dashes and parentheses are ideal candidates for an input mask
(another ridiculous database term!). On the datasheet, blank spaces appear where the numbers
go, and the punctuation marks stand at the ready to receive numbers.
 Figure 2. 3 Input Mask Sample
In the Input Mask text box, enter a 0 where numbers go and enter the punctuation marks where
they go. For example, enter (000) 000-0000 or 000/000-0000 to enter an input mask for a
telephone number. You can also create input masks by clicking the three dots beside the Input
Mask text box. Doing so opens the Input Mask Wizard dialog box, where you can fashion a very
sophisticated input mask.

E. Caption

If the field you're working on has a cryptic or hard-to-understand name, enter a more descriptive
name in the Caption text box. The value in the Caption property appears as the column heading
in Datasheet view, as a label on forms, and on reports in place of the field name. People entering
data understand what to enter after reading the descriptive caption.

F. Default Value

When you know that the majority of records require a certain value, number, or abbreviation,
enter it in the Default Value text box. That way, you save yourself the trouble of entering the
value, number, or abbreviation most of the time because the default value appears already in
each record when you enter it. You can always override the default value by entering something
different.

G. Validation Rule

As long as you know your way around operators and Boolean expressions, you can establish a
rule for entering data in a field. For example, you can enter an expression that requires dates to
be entered in a certain time frame. Or you can require currency figures to be above or below a
certain value. To establish a validation rule, enter an expression in the Validation Rule text box.
To use dates in an expression, the dates must be enclosed by number signs (#).
To get help forming expressions, click the three dots beside the Validation Rule text box to open
the Expression Builder and build an expression there. Try clicking the Help button in the
Expression Builder dialog box. Doing so opens the Access Help program, where you can get
advice about building expressions.
 Figure 2. 4 Validation Rule Expression

H. Validation Text

If someone enters data that violates a validation rule that you enter in the Validation Rule text
box, Access displays a standard error message. The message reads, “One or more values are
prohibited by the validation rule set for [this field].
Enter a value that the expression for this field can accept.” If this message is too cold and
impersonal for you, you can create a message of your own for the error message dialog box.
Enter your friendly message in the Validation Text text box.

I. Required

By default, no entry has to be made in a field, but if you choose Yes instead of No in the
Required box and you fail to make an entry in the field, a message box tells you to be sure to
make an entry.