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.
Before editing a record, you must locate it to indicate to the record set what record is involved. Here is an example of locating a record: Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Error
Dim curDatabase As Object
Dim rstEmployees As Object
Dim fldEmployee As Object
Set curDatabase = CurrentDb
Set rstEmployees = curDatabase.OpenRecordset("Employees")
With rstEmployees
Do Until .EOF
For Each fldEmployee In .Fields
If fldEmployee.Name = "EmployeeID" Then
If fldEmployee.Value = CInt(txtEmployeeID) Then
' The record to be edited has been located
Exit For
End If
End If
Next
.MoveNext
Loop
End With
Exit Sub
cmdUpdate_Error:
MsgBox "There was a problem when editing the record."
End Sub
To support the ability to update a record, the Recordset class is equipped with a method named Edit. Once you have located the record, call the Edit method. After calling the method, access the field or each field that needs to be edited and assign the desired value to it. After calling the Edit() method and assigning the value(s) to the appropriate field(s), you must call the Update() method of the Recordset class. This would be done as follows: Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Error
Dim curDatabase As Object
Dim rstEmployees As Object
Dim fldEmployee As Object
Set curDatabase = CurrentDb
Set rstEmployees = curDatabase.OpenRecordset("Employees")
With rstEmployees
Do Until .EOF
For Each fldEmployee In .Fields
If fldEmployee.Name = "EmployeeID" Then
If fldEmployee.Value = CInt(txtEmployeeID) Then
' The record to be edited has been located
.Edit
.Fields("DateHired").Value = txtDateHired
.Fields("FirstName").Value = txtFirstName
.Fields("LastName").Value = txtLastName
.Fields("HourlySalary").Value = txtHourlySalary
.Update
Exit For
End If
End If
Next
.MoveNext
Loop
End With
Exit Sub
cmdUpdate_Error:
MsgBox "There was a problem when editing the record."
End Sub
The approach to editing a record using ADO follows the same algorithms as reviewed for the Microsoft Access Object Library or DAO except that, with ADO, you don't call the Edit() method. Here is an example: Private Sub cmdMovePosition_Click()
Dim rstVideos As ADODB.Recordset
Dim fldEnumerator As ADODB.Field
Set rstVideos = New ADODB.Recordset
rstVideos.Open "Videos", CurrentProject.Connection, _
adOpenForwardOnly, adLockOptimistic, adCmdTable
' Scan the records from beginning to each
While Not rstVideos.EOF
' Check the current column
For Each fldEnumerator In rstVideos.Fields
' If the column is named Title
If fldEnumerator.Name = "Title" Then
' If the title of the current record is "Congo"
If fldEnumerator.Value = "Congo" Then
' then change its value
rstVideos("Director").Value = "Frank Marshall"
rstVideos.Update
End If
End If
Next
' Move to the next record and continue the same approach
rstVideos.MoveNext
Wend
rstVideos.Close
Set rstVideos = Nothing
End Sub
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 Videos " & _
"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. |
|
||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||
and click the Replace tab
Practical
Learning: Introducing Record Maintenance
