Prohibit Auto Save of MS Access form Data

[ahmedjamalaboelez]

Good Day Every one
I have a form contains 2 sub forms in "data sheet"Layout , But if mistakenly update afield then leave field of couple of fields , data automatically updated in tables before i click save button ,

is there is any way to Just update if I clicked save button ? especially for data sheet sub forms

Thanks For your support

 

[theDBguy]

Hi. There are a couple of ways to prevent incomplete records from saving into the underlying table of a form.


1. You could validate the data entered in the form's BeforeUpdate event and discard any changes, or


2. Use an unbound form, so no automatic save occurs

 

[The_Doc_Man]

The usually way to do this is as theDBGuy suggested. The Form_BeforeUpdate event includes a Cancel option, which means that you can do validation in the event code and if there is a reason to not allow it, you just Cancel the update.

 

[ahmedjamalaboelez]

I tried this , But I can work in sub form before it saves ??

 

[theDBguy]

I tried this , But I can work in sub form before it saves ??

Bound Access forms auto save when you leave it. So, when you edit a main form and then go to the subform, Access will auto save your changes to the main form. If you want to cancel it, then you can use its BeforeUpdate event. But, if you want to let Access save it but cancel after you made changes to the subform, then the only way is to use unbound forms. Or, maybe it's not the only way. You can certainly let Access auto save the data on the main form and simply reverse those changes using some VBA code.

 

[Solo712]

I tried this , But I can work in sub form before it saves ??

The trick I am using is this: Create a boolean variable that is visible by the whole form, say StopUntilSaved. In the Form_Current event set it to True.
Then place this as first statements in you Form_BeforeUpdate event:

If StopUntilSaved Then
     Cancel = True
     Exit sub
End If

Finally, you place StopUntilSaved = False statement in the click event of the Save button. This will allow the checking in the BeforeUpdate and saving the record. And you will never leave an edited record by accident again. Guaranteed.

Best,
Jiri

 

[arnelgp]

use Transaction on your subform.

 

[The_Doc_Man]

The others are explaining what to do about the auto-save you mentioned. I'll tell you WHY it happens.

If the main/sub form setup is based on a parent/child table, relational integrity requires you to save the parent record first because it is ILLEGAL to save a child record that does not have a corresponding parent record. When you switch to the sub-form, you are leaving a "dirty" form behind you and per Access rules on the subject, you have to clear that dirty form by saving its content. You are moving focus to the sub-form, which means you need a place to hold the data you are about to enter. To create the new child record in the sub-form, you must already have the parent record saved.