MS Access 2010 : Prevent Save in Main Form On Move to Subform

Question

A bound main Form has some Subforms, which allow users to choose data that is then entered for them in the relevant field(s) in the main Form.

However, when the focus shifts to the subform, or back to the main form, the main Form's Before and After Update events fire. This is a problem, as the BeforeUpdate has validation code to check that required fields have been entered. At this point, users are still 'selecting' data from the subforms to enter into the main Form's record, so it's too early to try to validate it.

Is there any way to prevent the main Form from trying to Save?

Krish Krish · Accepted Answer · 2015-04-21T08:09:03

it's a tricky part in ACCESS. Generally speaking, if you move away from a dirty form all uncommitted changes will be committed(bounded form). But before that the forms_OnbeforeUpdate event will fire. In your case, one way would be to put a command button and save it only via the command button. Or use any validation checks in that onBeforeUpdate event.

Idea is to use the onBeforeUpdate_event to check if the save was intentional or automatic. You can perform the checks either:

if your validations are failed don't save (cancel = true)
if the save was intentional by looking at any variable.

insert a command button and a form level/friendly variable called

Dim iCanSave as boolean

use the command button to set the iCanSave variable to True and perform

iCanSave = True
Docmd.runCommand accmdSAveRecord

in your Form_onBeforeUpdate event check if iCanSave is true if yes the save was intentional so allow saving.. if not discard the save. something like:

form_onBeforeUpdate(cancel....)
    if not (iCanSAve) then
        cancel = true
    End if

MS Access 2010 : Prevent Save in Main Form On Move to Subform

2 Answers