Details on Data Entry: Record Maintenance

Introduction

Record maintenance consists of copying a record, deleting a record, or importing records from a table in another database. Microsoft Access doesn't allow inserting (creating a new record between two existing records), moving (changing the position of a record such as from index 8 to index 4 or from index 2 to index 5) or switching records (such as putting record 2 at position 5 and putting record 5 at position 2).

Copying a Record

Imagine that, in a table, you have an existing record that already holds many values of a new record you want to create. Instead of retyping all values, you can copy the existing record to the new record and make only the few needed changes. If you display a table in Datasheet View, to copy a record, you can right-click the box on the left side of the most left field of the column and click Paste:

To apply the copied record to a new one, right-click the left box of the first empty record and click Paste. Before copying a record in a form, the form should have the Record Selectors property set to Yes. To copy a record, you can right-click the record selector and click Copy:

After copying the record, you can get to the first empty record, right-click its record selector and click Paste.

After pasting a record, you can make the necessary change. You must also manually take care of ensuring the uniqueness of values in the appropriate fields. For example, if a column is meant to hold unique values such as employee numbers or Social Security numbers, after pasting the record, to finalize it, you must make sure you change the value in the new record. Otherwise, before moving on, you would receive an error.

To programmatically copy a record, you can retrieve the value of each column and create a new record using those values.

Deleting a Record

Deleting a record consists of removing it from a table (or a form). to visually do this, after displaying a table in the Datasheet View, you can right-click the box on the left side of the record and click Delete Record:

You can also click Cut. The difference is that the Cut option would copy the record to the clipboard so you can paste it anew.

To programmatically delete a record using SQL, you combine the DELETE operator in the following primary formula:

DELETE FROM TableName

When this statement is executed, all records from the TableName table would be removed. Here is an example:

Private Sub cmdClearCustomers_Click()
    DoCmd.RunSQL "DELETE FROM Customers"
End Sub

In this case, all customer records from a table named Customers in the current database would be deleted. An alternative to the above formula is:

DELETE *
FROM TableName

In this formula, you use the * operator as the column placeholder. You can replace it with one or more names of columns but it doesn't matter because the DELETE operator signifies that the whole record will be deleted, regardless of the column name.

The TableName must be a valid name of a table in the specified or the current database. Here is an example:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * FROM Videos;"
End Sub

If you execute this type of statement, all records from the table would be deleted. We saw above that the user can specify what particular record to delete instead of all records. You also can specify what record to remove from a table. To do this, use the following formula of the DELETE operator:

DELETE *
FROM TableName
WHERE Condition

This time, the Condition factor allows you to set the condition that would be applied to locate the record. Consider the following table:

Here is an example of specifying a condition to delete a record:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE * " & _
                 "FROM Videos " & _
                 "Director = 'Adrian Lynn';"
End Sub

When this code runs, all videos directed by Adrian Lynn would be deleted from the table. Instead of deleting all records like this, you may want to remove only one particular video. To do this, you must set a condition that sets that record apart. Once again, the condition can be easily handled by the primary key.