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

raziel3

Registered User.
Local time
Today, 11:01
Joined
Oct 5, 2017
Messages
278
Hello Everyone,
I am halfway there following this suggestion


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.

Test Form.jpg
 

June7

AWF VIP
Local time
Today, 07:01
Joined
Mar 9, 2014
Messages
5,496
Form BeforeUpdate event is customarily used to do data validation.

Provide your code or db for analysis.
 

raziel3

Registered User.
Local time
Today, 11:01
Joined
Oct 5, 2017
Messages
278
I used the same code from the link

Code:
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.
 

Attachments

  • TEST.accdb
    1.4 MB · Views: 35

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:01
Joined
May 7, 2009
Messages
19,246
here test your test.db
 

Attachments

  • TEST.accdb
    2.1 MB · Views: 36

raziel3

Registered User.
Local time
Today, 11:01
Joined
Oct 5, 2017
Messages
278
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:01
Joined
May 7, 2009
Messages
19,246
here try it again, will not close the form until the balance is zero.
i added message to user.
 

Attachments

  • TEST.accdb
    2.1 MB · Views: 32

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 28, 2001
Messages
27,328
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.


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.


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

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Feb 19, 2002
Messages
43,491
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.
 

Users who are viewing this thread

Top Bottom