Microsoft KB Archive/130864

= PRB: Form with One-to-Many View Does Not Update Parent Records =

Article ID: 130864

Article Last Modified on 12/1/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 3.0b for Macintosh

-



This article was previously published under Q130864



SYMPTOMS
A form with one-to-many data that is based on a view may not refresh properly when the data in a parent record changes. The correct data displays in the record where the data was changed, but other child records for the parent show the old version of the data. This behavior occurs even if a TABLEUPDATE or a ThisForm.Show command is issued.



CAUSE
This is by design. The data in the view is the result of a query that was performed when the form was initially loaded. The query creates a cursor in memory with a copy of the data from the source tables. During the form load, a thermometer bar appears demonstrating the progress of this query.

When a TABLEUPDATE command is issued, the changes are sent to the source tables. The child records in the view are not updated because there is no dynamic link between the view and the source table. These records do not display the new information until the query is run again.



RESOLUTION
Here are two possible solutions:


 * Base the form on the source tables instead of a view. This is possible only if the view is completely comprised of local tables.

-or-
 * Issue a REQUERY function after each TABLEUPDATE.

Choose the best solution for your situation by weighing the advantages of using a view with the performance hit caused by the REQUERY function.

You can place the REQUERY function in the show or refresh event of the form. This causes the query to be performed each time a Form.Show or Form.Refresh is issued, so the most recent data is always displayed. Alternately, you can place the REQUERY function in all events that use a TABLEUPDATE command, such as the InteractiveChange event of a grid or the Click event of a command button.



MORE INFORMATION
When using Visual FoxPro for the Macintosh 3.0b the expression "right- click" means pressing the Control button on the keyboard while clicking.

Steps to Reproduce Behavior
  Issue the following command to open the Tastrade database: OPEN DATABASE c:\vfp\samples\mainsamp\data\tastrade MODIFY DATABASE  Right-click the gray background of the Database Designer. Choose New Local View. Click the New View button. When the "Add Table or View" dialog box opens, select the customer Table and then click the Add button to add the table to the view. Similarly, add the Orders and Order_Line_Items tables to the view. Close the dialog box.  In the Fields tab, add the following fields to the view: Customer.Customer_id, Customer.company_name, Orders.customer_id, Orders.order_id, Orders.order_date, Order_line_items.order_id, Order_line_items.quantity.  In the Update Criteria tab, click the Send SQL Updates box. Make sure all tables are selected from the Table list box. Click in the key column to select the key fields for each table. Make sure that only the key column (Customer.customer_id, Orders.customer_id, and Order_Line_Items.order_id) fields are selected as key fields. Click the Update column, to select all other fields. Click the SQL WHERE clause "Key Fields Only" and the Update using "SQL Update" buttons. Save the view as MyView.</li> Create a new form. From the View menu, choose Data Environment. Use a right-click to select Add Table. In the "Add Table or View" dialog box, select to display Views and select the Add the view you created in step 6. Click the ADD button to add the table to the Data Environment of the Form and then close the dialog box.</li> In the Data Environment of the Form, select the view you just added. Right-click, and select Properties.</li> Set the BufferModeOverride property to 3 - Optimistic Row Buffering.</li> Click the title of the view in the data environment, and drag it onto the form. A grid should appear.</li>  Add a command button with the following code in the click event: =TABLEUPDATE </li> Save and run the form. Change the company name for one of the companies that has more than one record. Click the command button.</li>  Close the form and reopen it in the Forms Designer. Add the following line of code after the =TABLEUPDATE in the command button created in step 11: =REQUERY </li> Rerun the form and change the company name back to its original value. The updated data should show for all records after the command button is clicked.</li></ol>

<div class="references_section">