Microsoft KB Archive/95445

{|
 * width="100%"|

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

 * }

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:

<pre class="FIXEDTEXT">     macrogroupname.macroname

For example, UpdateOther.SaveValue specifies the save value macro. </li>  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

<pre class="FIXEDTEXT">     Query: IDChgQuery Tables: Orders Detail Field:    Product ID         Update To: Forms![Products]![Product ID] Criteria: Forms![Products]![Previous ID] </li></ol>

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

Issue type : kbinfo

Technology :