How-To: Update 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
     
    Employees
  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
     

 
 
Home Copyright © 2005 FunctionX, Inc.