Updating Records

Introduction

One of the jobs of being a database developer or administrator is to update records when there is a new request. For example, imagine a company has decided to change the minimum salary of all employees and the manager gives it to you. If the company is small as having less than 10 employees, you can easily open the table or the form that holds the employees records, manually examine the employees with the lowest salary, and then change those who have a salary below the company's new set. You can also create an update query that would perform the task. The job here is to check all salaries but to make a change only to those that respond to a specific criterion. For example, if you must change the minimum salary to 8.55, your change would affect only those employees who are making less than $8.55/hr.

Imagine a company has decided to give a general raise to all employees, for example $0.35. Once again, for a small company of 10 employees or less, you can simply open the table or the form that holds employees records, get to the salary column, and add the new raise to each salary. If the company is medium to large, you cannot take the risk of doing this manually. Once again, you have various alternatives to solving this type of problem. For example, you can create an update query using a SQL statement and perform the task.

As mentioned already, if you try solving this type of problem manually, you are likely to make a mistake. Solving it with an update query may not be as friendly because, after creating the query, since you cannot just hand a query to a "casual" user, you would then have to either train a user, which could be a waste of time, or create a form, in which case, you would work twice to solve one problem. As ADO is not always as difficult as it may appear to be, these types of problems can be solved with just a few lines of code. The advantage also is that, as we will do here, you can create a simple for, make it available to the user who can then fill it up with the desired value, without your intervention. Of course, the form can also be used more than once (it can also become dangerous if given to an irresponsible user who may just decide to give a $10/hr raise to all 750 employees... Sweet).

Practical Learning: Updating Records

1. Start Microsoft Access and create a new Blank Database named Sally Super Market
2. Create a new table in Design View with the following fields:
    

   Column Name	Data Type	Other Properties
   EmployeeID	AutoNumber	Primary Key
   FirstName	Text
   LastName	Text
   EmailAddress	Text
   Salary	Currency

3. Save the table as Employees and switch it to Datasheet View  
4. Create a few records as follows:
    

   Employee ID	First Name	Last Name	Email Address	Salary
   1	Anselme	Roberts	robertsa@sallysuper.com	$14.58
   2	Justine	Keys	keysj@sallysuper.com	$5.22
   3	Edward	Ross	rosse@sallysuper.com	$22.40
   4	Tracey	Kirkland	kirklandt@sallysuper.com	$6.12
   5	Kimberly	Eisner	eisnerk@sallysuper.com	$7.54
   6	Jonathan	Adamson	adamsonj@sallysuper.com	$10.28
   7	Steve	Fox	foxs@sallysuper.com	$6.44
   8	Andrew	Boroughs	boroughsa@sallysuper.com	$8.92
   9	Randy	Ettenson	ettensonr@sallysuper.com	$12.68
   10	Rebecca	Gray	grayr@sallysuper.com	$16.54
   11	Sally	Aquino	aquinos@sallysuper.com	$5.84
   12	Grace	Captain	captaing@sallysuper.com	$15.10
   13	Henry	Thomason	thomasonh@sallysuper.com	$20.28
   14	Matt	Vaessel	vaesselm@sallysuper.com	$7.25
   15	Alexandra	Graeme	graemea@sallysuper.com	$6.16
   16	Kassa	Lafamm	lafammk@sallysuper.com	$9.26

5. Close the table. If you want, as an option, generate a form for it
    
6. Create a new (independent) form  and set its Caption to Database Maintenance
7. Save it as Database Maintenance
8. Design the form as follows:
    

   Control Name Caption Other Properties Form     Auto Center: Yes Navigation Buttons: No Min Max Buttons: Min Enabled Text Box txtNewMinSalary Set minimum salary to:   Command Button cmdNewMinSalary Submit   Text Box txtGeneralRaise Give a raise to all employees for:   Command Button cmdGeneralRaise Submit

9. Right-click the top Submit button, click Build Event..., click Code Builder and click OK
10. Implement the event as follows:
     

    Private Sub cmdNewMinSalary_Click()
        Dim rstEmployees As ADODB.Recordset
        Dim strSQL As String
    
        Set conDatabase = CurrentProject.Connection
        strSQL = "SELECT * FROM Employees WHERE Salary < " & txtNewMinSalary
        
        Set rstEmployees = New Recordset
        rstEmployees.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic
    
        With rstEmployees
            Do While Not .EOF
                !Salary = txtNewMinSalary
                .Update
                .MoveNext
            Loop
        End With
        
        MsgBox "The minimum salary of all employees has been set to " & txtNewMinSalary
        
        rstEmployees.Close
        conDatabase.Close
        Set rstEmployees = Nothing
        Set conDatabase = Nothing
    End Sub

11. Right-click the second Submit button, click Build Event... and double-click Code Builder
12. Implement the event as follows:
    

    Private Sub cmdGeneralRaise_Click()
        Dim conDatabase As ADODB.Connection
        Dim strSQL As String
    
        Set conDatabase = CurrentProject.Connection
        
        strSQL = "UPDATE Employees SET Salary = Salary + " & txtGeneralRaise
        
        conDatabase.Execute strSQL
        
        MsgBox "All employees have received a raise of " & txtGeneralRaise
        
        conDatabase.Close
        Set conDatabase = Nothing
    End Sub

13. Using the Command Wizard, add a new Command Button used to close the form. Name it cmdClose and set its Caption to Close
    

    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click
    
        DoCmd.Close
    
    Exit_cmdClose_Click:
        Exit Sub
    
    Err_cmdClose_Click:
        MsgBox Err.Description
        Resume Exit_cmdClose_Click
        
    End Sub

14. Test the application