3 Data Entry and Modification
Access, databases, tables, queries, forms, reports, macros, textbook
Adding new data or changing existing data is one of the primary 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 a table, but this is not recommended for individuals unfamiliar with Access. Incorrect entries can easily be made, especially in tables related to other tables. The recommended method is to perform data entry through a form. Forms can be made user-friendly, provide explanatory text, and perform data validation in the background.
3.2 Table Operations
You can perform the most important data operations directly in the table, such as editing, adding, and deleting records. You can type directly into fields; standard copy and paste methods are also supported.
Editing Records
Open the table, then use the mouse or arrow keys to navigate to the field you want to change. Click in the field and make the necessary changes. Once you make a change, a pencil icon will appear on the left side of the record, 
. This indicates that the record is in edit mode. Once you leave the record, the icon disappears, and the changes are saved.
Adding Records
Navigate to the bottom of the table, to the row marked with an asterisk: 
. This row doesn’t actually exist until you begin entering data, at which point it is created, and the asterisk moves down one row. The new record is saved automatically.
Deleting Records
There are several ways to delete records. The two easiest methods are:
- Select the record, then press the Delete key.
- Right-click in the margin at the beginning of the record, then choose Delete Record. Access will ask for confirmation before deleting.
Access does not have a button or function to undo deletions.
3.3 Add a New Customer
The Registration form is designed exclusively for adding new records to the Customers table. Browse existing records is disabled in this form.
Task 3.1 File: candy365.accdb
Open the database.
Open the Registration form.
The Registration form contains blank fields for data entry. The insertion point indicates where the typed text will appear. You can move the insertion point by clicking on another field or by repeatedly pressing the TAB key.
- Enter the data as shown in Figure 3.1.
You will see the pencil icon again, indicating that the form is in edit mode and the data has not yet been saved.
The record is automatically saved when you close the form or navigate to another record.
Close the form using the close button X in the upper right corner of the document window. The records added through the Registration form are stored in the Customers table.
Open the Customers table, navigate to the last record, and verify that this is the record you just added.
Close the Customers table.
3.4 Add a New Praline
The Pralines form allows you to browse existing records and add new ones.
Task 3.2 File: candy365.accdb
Open the database.
Open the Pralines form.
In the status bar, click the New (blank) record button.
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
Close the Pralines form. The records added through the Pralines form are stored in the Pralines table.
Open the Pralines table and ensure the record has been added.
Close the Pralines table.
3.5 Add a New Box
You can add a new box using the Boxes form. This form differs from the previous two because it includes another form within it. This structure is known as a main form with a subform.
- Main Form (Boxes)
-
This form contains data about the box itself (not its contents), such as BoxName, BoxCode, BoxDescription, Weight, BoxPrice, and Stock. This data is stored in the Boxes table.
- Subform (Boxes subform)
-
This form contains data about the pralines in the box, such as PralineCode and Quantity. This data is stored in the BoxDetails table.
The content of the BoxCosts field is calculated by summing PralineCosts * Quantity for all pralines in the box. Because this value can be calculated, it is not stored in a table
Task 3.3 File: candy365.accdb
Open the database.
Open the Boxes form.
In the status bar, click the New (blank) record button
.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
Below “Content,” in the Code field, click the selection arrow and select PralineCode B02 Butterscotch Blueberry. The remaining praline data, such as PralineName, ChocolateType, NutType, FillingType, and PralineCosts, will be filled in automatically. You only need to enter the data for Quantity (Nr.).
Enter 3 for Nr..
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.
Enter 25.00 for BoxPrice and 120 for Stock.
Close the Boxes form.
Check the Boxes table to ensure the new box has been added.
3.6 Data Restriction and Validation
Access offers various options to control how 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 does not satisfy the rule, an error message will inform you of the permitted input. Another method for controlling data input is using an input mask, which defines a format for data entry in a field using characters and symbols.
You can apply these simple validation and restriction methods by setting properties for fields in tables or properties for controls on forms.
It is recommended to define data validation and restriction in the table design by setting field properties. This saves time because every time you use the field in a form, the field validation rules and other properties are also applied to the form.
If the data entered through a form control is not dependent on a table field, then you need to define properties on the form for validating and restricting data.
Task 3.4 File: candy365.accdb
A memo from Snoopy’s marketing department states that the price of the Butterscotch box should be changed to $7.75.
Open the database.
Open the Boxes form and navigate to the Butterscotch box.
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.
This message appears because a validation rule is associated with the BoxPrice field.
Click OK.
Change the price back in $27.75 and press the TAB key. Then, 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 with the data below. First, determine the best way to enter the order. Then, create this order. The OrderCode should be the next available order code.
Order placed on April 23, 2010, at 10:00 AM. Customer Rebecca Smit orders by phone: 3 boxes of CHER and 2 boxes of Marz.
A few hours later, the following change to this order is made by phone. Again, first, consider the best way to handle the change. Then, apply the change.
Order change on April 23, 2010, at 4:00 PM. The two boxes of Marz should be deleted.