3  Data entry and modify

OBJECTIVES

  • Using a data entry form.
  • Editing, adding and deleting records in a table.
  • Using a main form with sub form.
  • Possibilities to enforce validation of data entry.

Adding new data or change existing data is one of the main activities within Access. In this chapter, you will learn some basic skills for adding and/or modifying data.

3.1 About data entry

Records can be edited directly in the table, but this is not a recommended method for people who are not familiar with Access. You can easily make incorrect entries, especially in tables that are related to other tables. The recommended method is to perform data entry through a form. You can make a form user-friendly, provide explanatory text, and perform controls on the data in the background.

3.2 Table operations

You can perform the most important data operations directly in the table, like editing, adding, and deleting records. You can type in the fields, but also the known ways to copy and paste are supported.

Record editing

Open the table and then use the mouse or the arrow keys to go to the field to be changed. Click in the field and make the changes. Once you make a change, an icon with a pencil appears on the left side of the record, . This is an indication that the record is in edit mode. Once you leave the record, the icon disappears and the changes have been saved.

Record adding

Go to the bottom of the table, to the row marked with an asterisk: . In reality, this row doesn’t consist but is created when you start entering data. The asterisk moves a row down. The new record is automatically saved.

Record deleting

There are several ways to delete records. The two easiest ways are:

  • Select the record, then press the key Delete.
  • Right mouse-click in the margin at the beginning of the record. Then choose Delete Record. Access asks for a confirmation of the delete operation.

Access has no button or function to undo the deletes.

3.3 Add a new customer

With the form Registration, only new records can be added to the table Customers. Browsing existing records has been disabled.

Task 3.1 File: candy365.accdb

  1. Open the database.

  2. Open the form Registration.

The form Registration contains blank fields where you enter the data. The insertion point indicates where the text appears you type. You can move the insertion point by clicking on another field or by repeatedly pressing the TAB.

  1. Enter the data as shown in Figure 3.1.
Figure 3.1: Data entry with form Registration.

You see again a pencil, indicating that the form is in edit mode and the data has not saved yet.

The record is automatically saved when you close the form or go to another record.

  1. Close the form through the close button X in the upper right corner of the document window. The records added through the form Registration are stored in the table Customers.

  2. Open table Customers, go to the last record, and check whether this is the previously added record.

  3. Close table Customers.

3.4 Add a new praline

You can browse existing records and adding new records with form Pralines.

Task 3.2 File: candy365.accdb

  1. Open the database.

  2. Open form form Pralines.

  3. Click in the status bar on the button New (blank) record.

  4. Enter the following data:

    • PralineName: Pecan Mocha Toffee
    • PralineCode: F03
    • PralineDescription: Sweat creamy mocha and pecan, surrounded with toffee
    • ChocolateType: Toffee
    • FillingType: Mocha cream
    • NutType: Pecan
    • PralineCosts: 0.25

  5. Close form Pralines. The records added through form Pralines are stored in the table Pralines.

  6. Open table form Pralines and make sure the record is added.

  7. Close table form Pralines.

3.5 Add a new box

The mainform Boxes contains also a subform. The main form with the name Boxes provides the following information: BoxName, BoxCode, BoxDescription, Weight, BoxPrice, and Stock. These data are stored in the table Boxes. Furthermore, this form has an additional field BoxCosts, by calculating the sum of PralineCosts * Quantity for all pralines in the box.

The subform with the name Boxes subform has the following editable fields: PralineCode and Quantity. These data and the BoxCode are stored in table BoxDetails. In the following steps, a new box is added through the form Boxes.

Task 3.3 File: candy365.accdb

  1. Open the database.

  2. Open form Boxes.

  3. Click in the status bar on button New (empty) record.

  4. Enter the following data:

    • BoxName: Winter Surprise
    • BoxCode: WINT
    • BoxDescription: Nuts and berries, covered with chocolate and toffee, ideal for long winter evenings by the fire.
    • Weight: 300 gram

  5. Click below Content in field Code on the selection arrow and select PralineCode B02 Butterscotch Blueberry. The remaining praline data as PralineName, ChocolateType, NutType, FillingType, and PralineCosts are filled in automatically. You only need to fill the data for Quantity (Nr.).

  6. Enter 3 at Nr..

  7. Add the following pralines to the box:

    Code PralineName Nr.
    B05 Butterscotch Raspberry 3
    P03 Cashew Perfect 3
    F01 Walnut Mocha Toffee 3
    F02 Pistachio Mocha Toffee 3
    P07 Classic Cherry 3

    The BoxCosts of $4.53 is automatically calculated.

  8. Enter at BoxPrice 25.00 and at Stock 120.

  9. Close form Boxes.

  10. Check in the table Boxes that the new box has been added.

3.6 Restriction and validation of data

You have various options in Access to control the way data is entered into the database. For example, you can limit the data that can be entered in a field by defining a validation rule for that field. If the entered data doesn’t satisfy the rule, an error message informs you what kind of input is permitted. Another method for controlling data input is using an input mask, which is a format for data entry in a field by using characters and symbols.

You can apply these simple methods of validation and restriction by setting properties for fields in tables or properties for controls on forms.

It is recommended to define validation and restriction of data in the design of the table by setting field properties. This saves time because every time you use the field in a form, the rules for field validation and other properties are also used on the form.

If the data entered through a form control, is not dependent of a table field, then you need to define properties on the form for validating and restriction of data.

Task 3.4 File: candy365.accdb

A memo from the marketing department of Snoopy reports that the price of the box Butterscotch should be changed in $7.75.

  1. Open the database.

  2. Open form Boxes and navigate to box Butterscotch.

  3. Change BoxPrice from $27.75 in $7.75 and press the TAB key. A validation message appears stating that this price is not right and how you can fix it.

    Box price too low, should be minimal 2*BoxCosts. Contact marketing department for further information.

The reason why this message appears is that there is a validation rule associated with the field BoxPrice.

  1. Click OK.

  2. Change the price back in $27.75 and press the TAB key. After that close the form.

3.7 Exercises

Exercise 3.1 Adding pralines (data001)

Add the following pralines:

Field Praline 1 Praline 2
PralineName Cashew Mocha Toffee Almond Mocha Toffee
PralineCode F04 F05
Description Sweet creamy mocha and cashew surrounded with toffee. Sweet creamy mocha and almond surrounded with toffee.
ChocolateType Toffee Toffee
FillingType Mocha cream Mocha cream
NutType Cashew Almond
Costs $ 0.24 $ 0.19

Exercise 3.2 Add new order (data002)

A customer places a new order according to the data below. First, consider the best way for entering the order. Then create this order. The OrderCode should be the next available order code.

Order on 23 April 2010, 10 o'clock. Customer Rebecca Smit orders by phone 3 boxes CHER and 2 boxes Marz.

A few hours later the following change to this order is made by phone. First, consider again the best way to handle the change. Then apply the change.

Order change 23 April 2010, 16 o'clock. The two boxes Marz should be deleted.