Details on Data Entry: Value Maintenance

Introduction

Consider the records in the following table named Videos and that is part of a database named Video Collection:

Many pieces of information are missing from this table and some others display an error. Like the regular objects of a database, records must be visited, changed, updated, and maintained. Microsoft Access provides the means of visually performing these operations. Some other times, you can perform them programmatically. The way you proceed may depend on your intended result, the sought behavior, and the library you want to use.

One of the primary actions you can perform on the values of a table or a form consists of looking for a value. This can be a prerequisite to many other operations. After opening a table in a Datasheet View or a form in Form View, to look for a value, on the toolbar, you can click the Find button . This would open the Find and Replace dialog box:

The Find What combo box allows you to type the value to search for. Normally, it is a combo box but, by default, it is empty. If you had performed a search before, the previous values would be in the Find What combo box so you can use them.

When the Find and Replace dialog box comes up, the field that was clicked prior to calling this window would display in the Look In combo box. If the Find What combo box contains the value you want to look for, then you can keep it. If you want to look for the value in the entire table, then click the arrow of the Look In combo box and select Table option.

The Match combo box allows you to specify if you want to consider the complete string of the field or only a section of it. The default value is Whole Field. To change it, you can click the arrow of its combo box and select another option.

If you are using Microsoft Access 2000 and if the default options are not enough, you can click the More button to expand the dialog box:

Once you are ready to look for the value, click Find Next.

Value edition consists of changing the value held in a field with another value. To do this, a user usually examines the records to locate the value first. To do this, the user can use the Replace dialog box. To open it, after displaying a table or a form, on the toolbar, the user can click the Find button and click the Replace tab. As an alternative, on the main menu, the user can click Edit -> Replace:

The Replace dialog box presents some similar options as the Find dialog box. These include the Find What, Look In and Match combo boxes, also the Find Next, the Cancel and the More buttons. The Replace With combo box allows you to specify the value that will replace the existing value. Here is an example:

After specifying the values, you can click Replace. If you want to replace more than one occurrence of the undesired string, you can click Find Next to locate them one by one and replace only those you want. If you want to replace all occurrences of a value, you can click Replace All.

To edit a value using SQL, you start with the UPDATE keyword and follow this formula:

UPDATE TableName
SET ColumnName = NewValue

In our formula, the TableName factor must be the name of a table in the selected or current database. The ColumnName must be a valid column in the TableName table. The NewValue factor of our formula is the new value that will replace the old one. If you use this formula, all records of the ColumnName would be given the NewValue. In some cases, you may want to change only one or more particular value. To do this, you must specify a condition that would be used to identify a record. The formula to use is:

UPDATE TableName
SET ColumnName = NewValue
WHERE Condition

The Condition factor specifies how the value will be located.

From the above table, imagine that you want to replace Peter Sagal with Peter Segal. The TableName is Videos. The column that holds the value is named Director. The NewValue is Peter Segal. The Condition can be that the shelf number is CM-8842. Based on this, to edit the value, you can use the following code:

UPDATE 	Videos
SET 	Director = "Peter Segal"
WHERE 	ShelfNumber = "CM-8842"

This can be done in code as follows:

Private Sub cmdMaintenance_Click()
    Dim strDataUpdate As String
    
    strDataUpdate = "UPDATE Videos " & _
                    "SET Director = 'Peter Segal' " & _
                    "WHERE ShelfNumber = 'CM-8842';"
    
    DoCmd.RunSQL strDataUpdate
End Sub

In the same way, in the list of videos, notice that the videos titled "Indecent Proposal" and "Fatal Attraction" have a name of director that looks the same. After checking the videos, you find out that they were actually directed by the same person. So you decide to prgrammatically edit it. You can write the SQL statement as follows:

UPDATE 	Videos
SET 	Director = "Adrian Lynn"
WHERE 	ShelfNumber = "DM-7426"

Before executing the statement, you check the table again but find out that, either by mistake or something else, another video is using the same shelf number. This means that if you execute the statement, any video that uses the same shelf number would have its director replaced, including those videos that don't have the same error. The solution to this is to make sure that you always have a way to distinguish each video from another. This is why the primary key is important. We saw in previous lessons that, when you use the AutoNumber data type on a column, Microsoft Access would take on the responsibility to assign each record a unique identifier. This value is furthermore required if the column is a primary key. Based on this, you can safely use the value of this record when doing data maintenance, such as updating a value. Therefore, the above change can be performed by using the value of the VideoID corresponding to the video whose director you want to update:

UPDATE 	Videos
SET 	Director = "Adrian Lynn"
WHERE 	VideoID = 5

This would be done programmatically as follows:

Private Sub cmdMaintenance_Click()
    Dim strDataUpdate As String
    
    strDataUpdate = "UPDATE Videos1 " & _
                    "SET Director = 'Adrian Lynn' " & _
                    "WHERE VideoID = 5;"
    
    DoCmd.RunSQL strDataUpdate
End Sub

This time, because we know that only one video has a VideoID equal to 5, only its record will be changed.