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

ID: Q95445

The information in this article applies to:

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:

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 
```

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.

Add an unbound text box control to the form, and give it the following properties:


      Control: Text box
      ------------------------
      ControlName: Previous ID
      Visible: No

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.

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]

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.

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