Modifying Table Data Using DAO Code
So far, you have learned how to loop through and work with Recordset objects. Now you will learn how to change the data contained in a recordset.
Changing Record Data One Record at a Time
Often, you want to loop through a recordset, modifying all the records that meet a specific set of criteria. Listing 14.65 shows the code required to accomplish this task.
Listing 14.65 Updating Records That Meet a Set of Criteria
Sub IncreaseEstimate()
Dim db As dao.Database
Dim rstProjectst As dao.Recordset
Dim sSQL As String
Dim intUpdated As Integer
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a dynaset-type recordset based on tblProjectsChange
Set rstProjectst = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)
'Locate the first project that meets the designated criteria
sSQL = "ProjectTotalEstimate < 30000"
intUpdated = 0
rstProjectst.FindFirst sSQL
'Loop as long as records meet the designated criteria,
'increasing the ProjectTotalEstimate by 10%
Do Until rstProjectst.NoMatch
intUpdated = intUpdated + 1
rstProjectst.Edit
rstProjectst("ProjectTotalEstimate") = rstProjectst("ProjectTotalEstimate") * 1.1
rstProjectst.Update
rstProjectst.FindNext sSQL
Loop
'Display the number of rows that were updated
Debug.Print intUpdated & " Records Updated"
rstProjectst.Close
End Sub
This code finds the first record with a ProjectTotalEstimate of less than 30,000. It uses the Edit method to prepare the current record in the dynaset for editing. It replaces the ProjectTotalEstimate with the ProjectTotalEstimate multiplied by 1.1. It then issues the Update method to write the changes to disk. Finally, it uses the FindNext method to locate the next occurrence of the criteria.
Making Bulk Changes
You can use an Update query to accomplish many of the tasks that you can perform by looping through a recordset. Executing an Update query is often more efficient than the process of looping through a recordset. If nothing else, it takes much less code. Therefore, it is important to understand how to execute an Update query through code.
Suppose you have a query called qryChangeTotalEstimate that increases the ProjectTotalEstimate for all projects where the ProjectTotalEstimate is less than 30,000. The query is an Update query. The code in Listing 14.66 executes the stored query definition.
Listing 14.66 Making Bulk Changes Using a Predefined Update Query
Sub RunUpdateQuery()
Dim db As dao.Database
Dim qdf As dao.QueryDef
'Point the Database object at a reference to the
'current database
Set db = CurrentDb
'Point the QueryDef object at qryIncreaseTotalEstimate
Set qdf = db.QueryDefs("qryIncreaseTotalEstimate")
'Use the Execute method of the QueryDef object to
'execute the update query
qdf.Execute
End Sub
Notice that the Execute method operates on the query definition, executing the Update query.
Deleting an Existing Record
The Delete method enables you to programmatically delete records from a recordset, as shown in Listing 14.67.
Listing 14.67 Deleting Records with the Delete Method
Sub DeleteCusts(lngProjEst As Long)
Dim db As dao.Database
Dim rstProjects As dao.Recordset
Dim intCounter As Integer
'Point the Database object at a reference to the
'current database
Set db = CurrentDb
'Open a dynaset-type recordset based on tblProjectsChange
Set rstProjects = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)
'Loop through the entire table, deleting all projects where
'the ProjectTotalEstimate is less than a designated amount
intCounter = 0
Do Until rstProjects.EOF
If rstProjects("ProjectTotalEstimate") < lngProjEst Then
rstProjects.Delete
intCounter = intCounter + 1
End If
rstProjects.MoveNext
Loop
'Print the number of affected rows
Debug.Print intCounter & " Customers Deleted"
End Sub
This code loops through the rstProjects recordset. If the ProjectTotalEstimate amount is less than the value passed in as a parameter, the code deletes the record. You can also accomplish this task with a Delete query.
Adding a New Record
The AddNew method enables you to programmatically add records to a recordset, as shown in Listing 14.68.
Listing 14.68 Adding Records to a Recordset
Private Sub cmdAddDAO_Click()
Dim db As Database
Dim rstProject As Recordset
'Ensure that the project name and ClientID are entered
If IsNull(Me.txtProjectName) Or _
IsNull(Me.cboClientID) Then
MsgBox "The Project Name and Client Must be Filled In"
Else
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a dynaset-type recordset based on tblProjectsChange
Set rstProject = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)
'Add a new row to the recordset, populating its values with
'the controls on the form
With rstProject
.AddNew
!ProjectName = Me.txtProjectName
!ProjectDescription = Me.txtProjectDescription
!ClientID = Me.cboClientID
.Update
End With
'Populate the txtProjectID text box with the
'autonumber value assigned to the new row
Me!txtProjectID = rstProject!ProjectID
End If
End Sub
This code is used on an Unbound form called frmUnbound. The code issues an AddNew method, which creates a buffer ready to accept data. Each field in the recordset then is populated with the values from the controls on the form. The Update method writes the data to disk. If you forget to include the Update method, the record is never written to disk.
The last line of code does not work. The ProjectID field is an AutoNumber field, so Access will assign its value during the update. The offending line is supposed to copy the newly created ProjectID value into a text field on the form. The line is there to illustrate a problem: When an AddNew method is issued, the record pointer is not moved within the dynaset. Even after the Update method is issued, the record pointer remains at the record it was on prior to the AddNew method.
Therefore, this code will add a record, but it will place the ProjectID value of the previously existing record into the txtProjectID text box on the form. To get around this, you must explicitly move to the new record before populating the text box. You can easily accomplish this by using the LastModified property.
Using the LastModified Property
The LastModified property contains a bookmark of the most recently added or modified record. By setting the bookmark of the recordset to the LastModified property, the record pointer is moved to the most recently added record. Listing 14.69 is a modified version of Listing 14.68, using the LastModified property to fix the problem described previously.
Listing 14.69 Using the LastModified Property After AddNew
Private Sub cmdLastModified_Click()
Dim db As Database
Dim rstProject As Recordset
'Point the Database object at a reference to the
'current database
Set db = CurrentDb()
'Open a dynaset-type recordset based on tblProjectsChange
Set rstProject = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)
'Add a new row to the recordset, populating its values with
'the controls on the form
With rstProject
.AddNew
!ProjectName = Me.txtProjectName
!ProjectDescription = Me.txtProjectDescription
!ClientID = Me.cboClientID
.Update
'Move to the row you just added
.Bookmark = .LastModified
End With
'Populate the txtProjectID text box with the
'autonumber value assigned to the new row
Me!txtProjectID = rstProject!ProjectID
End Sub
Notice that the bookmark of the recordset is set to the LastModified property of the recordset.