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

• Microsoft Access versions 1.0, 1.1

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:

• Add a text box control to the form.
• Create a new macro group (UpdateOther) that contains two macros (SaveValue and ChgValue) to run the update query.
• Create an update query (IDChgQuery in this example).
• Change several properties to execute the macros.

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]