Prevent Update of a table related to a form based on condition

[raju]

Hai,

I have a for called "Item" that has its control source set to "Item" Table.
I have a subform called "Distribution" with its control source set to
"Distribution" table embedded within the "Item" Form.What is observed is, if
all the fields in the "Item Form" is filled without filling the Distribution
Subform and the form closed the table "Item" is updated. I want to prevent
this update and warn the user that if the form is closed without filling the
Distribution Data the Item data would not be saved.

I experimented with several controls but did not get the desired results. I
tried writing macro from the form, but am not able to select the controls on
the subform. There would be a error saying that such a control does not exist
on the form.

 

[John W. Vinson]

Hai,

I have a for called "Item" that has its control source set to "Item" Table.
I have a subform called "Distribution" with its control source set to
"Distribution" table embedded within the "Item" Form.What is observed is, if
all the fields in the "Item Form" is filled without filling the Distribution
Subform and the form closed the table "Item" is updated. I want to prevent
this update and warn the user that if the form is closed without filling the
Distribution Data the Item data would not be saved.

Difficult to do. The main form record is actually saved the instant you set
focus to any control on the subform. This is required, since otherwise there
would be no record in the Item table to provide a Primary Key value (via the
master/child link field) to fill in the foreign key field in the Distribution
table.

It's a chicken or egg problem: you are saying that you must not save the Item
record until all of the Distribution records are filled, but you can't create
any distribution records *UNTIL* the Item record exists!

You may need to put code in the main form's Close event to check for the
number of records matching the item ID in the Distribution table; if there are
none, issue a warning and cancel the Close. The mainform's BeforeUpdate event
will NOT work (because it fires the instant the subform is selected).