ACC1x: Updating a Separate Table When a Value Changes on a Form

ID: Q95445


The information in this article applies to:


SUMMARY

This article shows by example how to have Microsoft Access automatically update a field in a table when you use a form to change a related field in a different table. In other words, you can have Microsoft Access post your changes to a separate table that is not bound to the form.

To set this up, you need to:


MORE INFORMATION

This example uses the Products form in the sample database NWIND.MDB. Here is the step-by-step procedure:

  1. Open the NWIND.MDB database, and make a few modifications to set it up for this example. First, delete the relationship between the Products and Order Detail tables. Then, because you cannot update fields that have a Counter data type, change the following properties of the Product ID field in the Products table:
    
          Table: Products
          --------------------------
          Field Name: Product ID
             DataType: Number
             FieldSize: Long Integer 


  2. Open the Products form in Design view, and display the property sheet. Set the following form and control properties:
    
          Form: Products
          ---------------------------------
          OnCurrent: UpdateOther.SaveValue
          AfterUpdate: UpdateOther.ChgValue
    
          Field: Product ID
          -----------------
          Locked: No 

    These actions activate the macros, which in turn run the update query.


  3. Add an unbound text box control to the form, and give it the following properties:
    
          Control: Text box
          ------------------------
          ControlName: Previous ID
          Visible: No 


  4. Create a new macro group (UpdateOther) to hold two macros (SaveValue and ChgValue) by first choosing to create a new macro. Next, choose Macro Names from the View menu or click the Macro Names button on the toolbar. Microsoft Access displays the Macro Name column.


  5. Enter the two macro names along with their actions and action arguments. Each macro in the macro group begins on the line that contains that macro's name. Use the following table as a guide:
    
          Macro Name   Action and Action Arguments
          ----------------------------------------
          SaveValue    SetValue
                          Item:        [Previous ID]
                          Expression:  [Product ID]
    
          ChgValue     SetWarnings
                          Warnings On: No
                       OpenQuery
                          Query Name:  IDChgQuery
                          View:        Datasheet
                          Data Mode:   Edit
                       SetValue
                          Item:        [Previous ID]
                          Expression:  [Product ID] 


  6. Save the macro group, and name it UpdateOther. Now UpdateOther appears in the list of macros in the Database window. You can use the following syntax to specify each macro in the macro group:
    
          macrogroupname.macroname 

    For example, UpdateOther.SaveValue specifies the save value macro.


  7. Create a new update query called IDChgQuery. Add the table Order Details to the query. In the Field cell, enter Product ID. In the Update To cell, enter Forms![Products]![Product ID]. In the Criteria cell, enter Forms![Products]![Previous ID]. Here is a SUMMARY
    
          Query: IDChgQuery
          --------------------------------------------
          Tables: Orders Detail
             Field:     Product ID
             Update To: Forms![Products]![Product ID]
             Criteria:  Forms![Products]![Previous ID] 


Now, when using the Products form, which is bound to the Products table, if you enter a new value in the Product ID field, Microsoft Access updates the Product ID column in the Orders Detail table automatically. It does it by running the UpdateOther.SaveValue macro to save the previous value to use as criteria and then the UpdateOther.ChgValue macro to run the IDChgQuery update query. The IDChgQuery query updates the Product ID column in the Orders Detail table overwriting the previous old value with the new value.


Keywords          : kbusage FmsEvnt 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: March 19, 1999