Prevent Record Cycle or Form Close unless condition is met. (1 Viewer)

[raziel3]

Hello Everyone,
I am halfway there following this suggestion

Prevent moving to new record if conditions are not met - Microsoft: Access Forms

We want to prevent a user from moving to the next record during data entry if a certain field does not equal zero. This is a calculated field based on entries within the form and is used to see if the record will balance.

www.tek-tips.com

I have an unbounded textbox that calculated the totals between the Parent Form and it's Subform. My form has no buttons to cycle or save, I am using Access's navigation controls.

I want to be able to prevent the form from closing or cancel the data entry as well as prevent moving out of the record if unbound text box value is not 0. The suggestion from the link above works once the data changes in the Amount field (see pic) but will still save the record with the unbalanced values.

 

[June7]

Form BeforeUpdate event is customarily used to do data validation.

Provide your code or db for analysis.

 

[raziel3]

I used the same code from the link

IF YourTextBoxName <> 0 Then
       Cancel = True
       enter here a messagebox if you like
      End IF

See DB attached. The form "deReceipts" is the form with the problem.

 

[arnelgp]

here test your test.db

 

[raziel3]

I am still able to close the form and the code works for existing records. If I enter a new record and it does not reconcile I am still able to move out of the record, but if I cycle back to it I cannot move out of it.

If the form closes I want to either cancel/undo the record entry or remain on the form until the user fixes the amounts.

 

[arnelgp]

here try it again, will not close the form until the balance is zero.
i added message to user.

 

[The_Doc_Man]

The trick is to understand why it does what you don't want it to do. You have a bunch of suggested code so I won't offer yet another pile of VBA.

A form updates a bound record when you are closing the form, navigating away from the record, changing focus in some cases involving forms and subforms, or (if you have one) click on a control to save the record. ANY of these actions have the same effect - the form tries to do an update. However, there is an event called Form_BeforeUpdate that intercepts ALL of those actions.

Form.BeforeUpdate event (Access)

Office VBA reference topic

learn.microsoft.com

In the BeforeUpdate event code, you can do your validity checks. If you realize that the required conditions are not met, you can set the Cancel parameter to non-zero. (Most people set it to either 1 or -1 or vbTrue.) That stops the update and therefore stops the action that triggered the update. This means you can stop a form from closing while it is still "dirty." You can stop the navigation to another record. You can stop the focus change (in cases where the focus is to another form). You can prevent the SAVE action from taking effect. The unfortunate part of this is that you can't easily tell which of those user actions led to Access wanting to do the SAVE action, though for a couple of them you could leave behind traces as a separate programming act.

A little "gotcha" issue is that IF the record happens to not be dirty when you Close, navigate, or change focus then, an Update won't occur so a BeforeUpdate event won't occur either. In the case where the form will close but you don't want it to, there is one last line of defense called Form_Unload, which is an event preceding Form_Close.

Form.Unload event (Access)

Office VBA reference topic

learn.microsoft.com

Again, you can put your safeguards in the Unload event code and, because it ALSO has a Cancel parameter, you can stop the form from closing and put up an appropriate message.

Note, though, that a persistent user can get past this by telling Windows to shut down and then when Access balks, the user can tell Windows to shut down anyway. This, you cannot stop because in any dispute about what to do next between Windows and Access, Windows always wins. A user could also use Task Manager to kill Access and again, no recourse. However, when your users get a message box that says you can't close until you do something specific, most of the time they will take the hint.

 

[Pat Hartman]

Just remember --- as long as you have used the correct event - the form's beforeupdate event (or in certain cases a control's beforeupdate event) the bad data will not be saved under any condition. If you make the mistake of using the lost focus event, the bad data WILL be saved if you force windows to close Access. If you power off, nothing gets saved regardless of the state of a form.