How do I stop a form from closing from the BeforeUpdate Event?

[Seth Schrock]

I have some code in the forms BeforeUpdate event that checks if a certain checkbox is checked. If it is not, I have a message appear that says "Do you wish to cancel this transaction?" with VbYesNo buttons. I want to be able to have it so that if I click no, the BeforeUpdate event is canceled and the form's close event stops so that the form remains open. If they click yes, then I want the BeforeUpdate's Cancel = True to run and then the form can close. The reason for this is that I don't want the record saving if that field is not checked. I'm not sure if setting the checkbox to be required will work and besides, I don't know how to customize the messages so that they are more understandable for average users.

[Rabbit]

In the BeforeUpdate event, set a global variable that is checked in the BeforeClose event of the form.

[NeoPa]

When I'm doing such things Seth, I add a Private blnAllowClose As Boolean line at the top of the form and set it to True when, and only when, you want to allow the form to close. The Form_Unload(Cancel As Integer) procedure would then look something very like :

Private Sub Form_Unload(Cancel As Integer)
    If blnAllowClose Then Exit Sub
    Cancel = True
    Call MsgBox(Prompt:="Please use the 'Close' button to close this form", _
                Buttons:=vbOKOnly Or vbExclamation, _
                Title:=Me.Name)
End Sub

[Seth Schrock]

@Rabbit, I had thought of that method, but I was hoping that there was an easier way. Oh well.

@NeoPa, How would a Private variable work? Wouldn't it have to be a public variable? Do I just declare the variable at the top of the form's VBA code right under the Option Explicit instead of inside a private sub?

[NeoPa]

As you say Seth (and how I thought I'd explained it in my earlier post). What you would expect to see is something like the following :

Option Compare Database
Option Explicit
 
Private blnAllowClose As Boolean
 
...

Remember to set it to True somewhere though, or the code in post #3 will never allow the form to close ;-)

Seth:
@Rabbit, I had thought of that method, but I was hoping that there was an easier way. Oh well.

NB. What I'm suggesting, and giving details on, is essentially the same as Rabbit was. The difference seems to be in that the BeforeClose() event doesn't actually exist, but the concept is the same and I'm sure the Form_Unload() event was what he was thinking of.

[Seth Schrock]

I have never done a private variable that way before so I wasn't sure if that was what you were meaning.

I plan on setting the blnAllowClose with yes/no buttons of the message box that the BeforeUpdate event will trigger. I will have to have the variable sit on True so that it doesn't run unless the BeforeUpdate variable is triggered before that (unsaved data is in the form). I have done other things like this so I think that I can figure that part out.

Thanks to both of you for your help.

[NeoPa]

You were quite right to check if you were unsure Seth. My bad for bringing that up.

Your explanation of the logic though, seems flawed. blnAllowClose should be left as False until some code is triggered that determines, for you, when the form should be allowed to close. Your logic is unknown to me, so I offer the following code as an example and illustration only. It does what I needed in my form :

Option Compare Database
Option Explicit
 
Private blnAllowClose As Boolean
 
...
 
Private Sub cmdClose_Click()
    blnAllowClose = True
    Call DoCmd.Close
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    If blnAllowClose Then Exit Sub
    Cancel = True
    Call MsgBox(Prompt:="Please use the 'Close' button to close this form", _
                Buttons:=vbOKOnly Or vbExclamation, _
                Title:=Me.Name)
End Sub

Note that until this point in the code is reached blnAllowClose is left as False.

[Seth Schrock]

For me, the form should be allowed to close if there isn't any unsaved data on the form. My plan was based on tests that clicking the close button will trigger the BeforeUpdate event if there is unsaved data. That is when I planned on setting blnAllowClose to false, stop the closing of the form, and then set blnAllowClose to true as the last step of the On_Unload event of the form. Then, the next time the form is closed without any changes made to the data, blnAllowClose would already be set to True, BeforeUpdate wouldn't run, and the form would close.

I believe that this would work. However, it might not be the best way.

[NeoPa]

That sounds a bit convoluted for something that should probably be a lot simpler. Why don't you explain what it is you're trying to achieve. What should be allowed and what shouldn't. When it isn't allowed what should happen instead.

That way we can match the requirement better, as we would have a better idea what you're really asking.

[Seth Schrock]

My goal is to make it so that there are no records where any of the fields are blank. For some reason, if there are, it messes up my queries that I do for billing purposes. What I have noticed is that occasionally, people start to create a record and realize someone else has already created it, so they just get out. This saves the record only partially filled out and then my billing gets messed up (reports won't open because of errors). I have put a cancel button on the form to undo the record, but I also want to make it so that it will undo the record if they try to exit without having canceled the record and the record isn't complete. I'm currently testing the last field in the recordset to see if it has been populated to know whether or not the record is complete. So if the record has been changed and the last field hasn't been checked, then I want to prevent the record from being saved, hence the BeforeUpdate event. That message will ask if you want to cancel the record changes. If yes, the the form can close. If no, then the form won't close and will remain on that record.

[zmbd]

Seth,
Normally I set the field property at the table level to not allow null values and set the required to yes in cases like this... The user either has to [ESC] the record or enter a non-null. Is there any reason you have not done these same things at the table level?

[NeoPa]

Zmbd:
Normally I set the field property at the table level to not allow null values and set the required to yes in cases like this...

I believe Z is referring to zero length strings rather than Null values in his comment, as the Required property does the latter and zero length strings are most definitely not Null values. That small anomaly cleared up, Z has expressed my thinking exactly. You should find that handles everything you need.

However, in case you are still interested in the logic, I will outline it for you :

Option Compare Database
Option Explicit
 
...
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not IsNull([X]) _
    And Not IsNull([Y]) _
    And Not IsNull([Z]) Then Exit Sub
    Cancel = True
    If MsgBox(Prompt:="Invalid Data.  Continue editing", _
              Buttons:=vbOKCancel Or vbExclamation, _
              Title:=Me.Name) = vbOK Then Exit Sub
    Call Me.Undo
End Sub

Seth:
For some reason, if there are, it messes up my queries that I do for billing purposes.

That would indicate to me that Null values are not being handled in your queries. It is not valid to design queries based on the assumption that all records will always have every field populated. Certainly not for any field where the [Required] property is not set to Yes (See Z's point).

[Seth Schrock]

As I described in previous post, I don't know how to create my own custom error messages if the fields aren't all populated. To me, the error messages are self explanatory, however my users don't see them that way. Hence, I always try to create my own messages so that they are worded more clearly and don't panic when they get it. I also have a question about a checkbox field being required. Does that make it be required to be true?

I will check my queries to see if I can figure that part out. That would certainly be the easier to fix. However, I seem to remember that the first WHERE clause in the query is to pull a certain CustomerID and partially empty records that have a different CustomerID seem to cause the problem as well. I would think that those wouldn't matter, but deleting those records makes the queries work.

[NeoPa]

If a field is Required then the user cannot even leave the related control until it's either populated or the record buffer is undirtied (All changes cancelled). If you really feel that your users will find that too confusing then you'll need to take the other approach. I've dealt with users for many years and would find very little that they do and struggle with would still surprise me, but if they can use a mouse and keyboard they should be able to recognise a simple message telling them they need to enter a value before leaving a control. We are talking human life-forms here I assume. Ultimately your decision of course - but you'll certainly be creating vast amounts of work for yourself going forward.

The Required property means that the field must contain a value (IE. Not Null). Boolean values of True and False are both values (-1 and 0 respectively) so either would be allowed in a field that will not accept Nulls.

[zmbd]

NeoPa... well... not exactly in that it's both-and not either-or: That will clear up in a minute, I think :)

Seth,
In the table design itself.
Open the table in design view.
Select a field... well, any text/numeric field
IN the properties section of the table creator for the field there are two properties..."Allow Zero Length" and "Required"
IF they are set as follows:
(this may get a tad weird:

By setting the properties as indicated when the user presses [Enter], [Space Bar], or attempts "" (zero length string either via VBA or directe entry) you get the following results stored, or not, in the field:

[Required]    [AllowZeroLength]    (Stored Values)
(NO)      (NO)
   [Enter]=Null; [SpaceBar]=Null; ""=NotAllowed
 
(NO)      (Yes)
   [Enter]=Null; [SpaceBar]=Null; ""=""
 
(Yes)      (NO)
   [Enter]=NotAllowed; [SpaceBar]=NotAllowed; ""=NotAllowed
 
(Yes)      (Yes)
   [Enter]=NotAllowed; [SpaceBar]=""; ""=""

So by setting AllowZeroLength to No and Required to Yes, you prevent the record from having either a null or zerolength entry in the field and you force the user to enter something... AND/OR you can then use the validation rules on the field to help prevent out of bound entries; however, there are some limitations on the field level and table level validation rules.

[NeoPa]

I appreciate it's both Z, but your post specified the same property twice. Setting Required to Yes is the same as not allowing Null values.

I agree that both the properties should be set correctly, but I'm a little confused as to your table. How is a user supposed to specify from the interface that they are entering an empty string rather than not entering anything (a Null)? They certainly wouldn't use quote characters. Empty strings, as far as I understand it, will be entered into the field in only two was :

1. If (Required = Yes) and (Allow Zero Length = Yes) then when a user clears the contents of the control it is treated as a string value of zero length.
2. The control or field is assigned a zero length string in VBA code explicitly.

Otherwise, the control will return a Null result and that will either be allowed or not depending on Required.

All-in-all though, I believe our advice is fundamentally the same, even if we don't necessarily see eye-to-eye on all the finer details.

[zmbd]

Table was taken directly from the MS explaination for how the settings are effective once set as indicated.

Personally, I've not tried to enter a zero-length into a field via the keyboard; however, I have via VBA ;) and in which case the property settings if done right will cause a field level error.

I'd agree with that... the Devil is in the details which in this case doesn't matter - just the final result. :)