Preventing user from closing a form w/null values 3

[LeiDBug007]

Greetings Experts,

We’ve got a form that has 7 fields, and a close command button at the bottom.

We want to ensure that the user cannot leave the form when there are null values in any of the fields.

We currently have a code that works perfectly and prevents the user from creating a new record when there are null values.

Here is the code:
*******************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
For i = 0 To Controls.Count - 1
If TypeOf Controls(i) Is TextBox Or _
TypeOf Controls(i) Is ComboBox Then
If IsNull(Controls(i)) Then
MsgBox "You must enter data in each field!"
Controls(i).SetFocus
End
End If
End If
Next i
End Sub
*******************************************************

HOWEVER……. It still allows the user to click on the close button and close the form – even with null values!?

HELP!!!!! Thanx!

 

[jryan224]

Put the same code in the click event of the close button.

 

[mgolla]

Add that same code to Form_Unload Event but make a small change:

Private Sub Form_Unload(Cancel As Integer)
  Dim i As Integer
  For i = 0 To Controls.Count - 1
     If TypeOf Controls(i) Is TextBox Or _
       TypeOf Controls(i) Is ComboBox Then
          If IsNull(Controls(i)) Then
          MsgBox "You must enter data in each field!"
          Cancel = True
          End
          End If
     End If
  Next i
End Sub

HTH
Mike

[noevil]

 

[LeiDBug007]

Hi mgolla,

Tried your code but still lets me close the form?!

I loaded it in the "On Unload" procedure, and tested the form. Left a few fields empty (null)... click on the close button...message box popped...said OK to message box, then it closes the form??!

~LeiDBug

 

[mgolla]

Sorry,

Change to this:

 Dim i As Integer
  For i = 0 To Controls.Count - 1
     If TypeOf Controls(i) Is TextBox Or _
       TypeOf Controls(i) Is ComboBox Then
          If IsNull(Controls(i)) Then
          MsgBox "You must enter data in each field!"
          Cancel = True
          [COLOR=red]Exit Sub[/color] 'was End
          End If
     End If
  Next i

HTH
Mike

 

[LeiDBug007]

Hi Mike,

Sorry, still doing the same... click close button...message box... click OK on message box... closes the form?!


I copied your exact code in our forms property, On Unload procedure.

Should we change the close button On Click procedure maybe?

I dunno anymore... sorry for bothering... :-(

~Tiffany

 

[mgolla]

What is the code behind the on click of your close button?

Is it DoCmd.Close?

What version of Access are you running?

I tested this code in Access 2k and it appears to work fine. Are you sure the field is Null?

maybe try changing the code to this:

Dim i As Integer
  For i = 0 To Controls.Count - 1
     If TypeOf Controls(i) Is TextBox Or _
       TypeOf Controls(i) Is ComboBox Then
          If IsNull(Controls(i)) [COLOR=red]Or Controls(i)=""[/color] Then
          Cancel = True
          MsgBox "You must enter data in each field!"
          Exit Sub
          End If
     End If
  Next i


HTH
Mike

[noevil]

 

[LeiDBug007]

Hi Mike,

Yep, DoCmd.Close. It's basically the default code Access gives when you create a close command button. We haven't done anything to it.

Here's the code:
****************************************
Private Sub Command13_Click()
On Error GoTo Err_Command13_Click


DoCmd.Close

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub
****************************************

And yes, the field is null. For example, I fill-in data in 2 fields and leave the other 5 fields null/blank. Click on new record, code is fine & won't let me create new record. But, click on close button, I get the message box...click OK on message box... then form closes.

I know it's acting weird... maybe just missing something...?

Here's all the code in our form so far...

***********************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
For i = 0 To Controls.Count - 1
If TypeOf Controls(i) Is TextBox Or _
TypeOf Controls(i) Is ComboBox Then
If IsNull(Controls(i)) Then
MsgBox "You must enter data in each field!"
Controls(i).SetFocus
End
End If
End If
Next i
End Sub


Private Sub Command13_Click()
On Error GoTo Err_Command13_Click


DoCmd.Close

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub


Private Sub Form_Unload(Cancel As Integer)
Dim i As Integer
For i = 0 To Controls.Count - 1
If TypeOf Controls(i) Is TextBox Or _
TypeOf Controls(i) Is ComboBox Then
If IsNull(Controls(i)) Or Controls(i) = "" Then
Cancel = True
MsgBox "You must enter data in each field!"
Exit Sub
End If
End If
Next i

End Sub
***********************************************************

 

[jryan224]

Have you tried it in the click event of the Close Btn as previously suggested?

 

[psemianonymous]

I just noticed this thread. The problem is that on your Form_BeforeUpdate, you MUST set the "Cancel = True". this ensures that the user will not be able to save the record (or any changes to the record) if you have marked "Cancel = True".

One problem I ran into with the Form_Unload() event is that it happens AFTER the data has been unloaded. So don't rely on anything in the Form_Unload() event to help you with data validation, ONLY do data validation inside BeforeUpdate().


So if you put:
[tt]Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim i As Integer
For i = 0 To Controls.Count - 1
If TypeOf Controls(i) Is TextBox Or _
TypeOf Controls(i) Is ComboBox Then
If IsNull(Controls(i)) Then
MsgBox "You must enter data in each field!"

'note addition
Cancel = True

Controls(i).SetFocus

'note change
Exit Sub
End If
End If
Next i
End Sub
[/tt]


When the user tries to close the form, FOR ANY REASON, or sneakily tries to move to the next record, or WHATEVER, they are NOT going to be able to save the record unless every control has something entered into it. If they decide to close the form anyway, then they lose all changes (and yes, there is a built-in messagebox that prompts them).


Hope this helped.

Pete

 

[Edski]

...Or ensure that the Required Property for every field in the table is set to Yes.

 

[LeiDBug007]

TO EDSKI:
Thanx for the suggestion on the "required property". That's actually our initial step, however, we cannot mark any of the fields in this table as in some occasions in a different form, it is NOT a required field. Therefore, marking the fields "required-yes" in the table is not an option for us and we wanted to code it per form. Thanx anyway...'preciate it.

TO JDEMMER:
Yes, used the same code in the On Click procedure for the close button and it works. It won't let the user close the form unless the fields are filled in.

However, once the fields ARE FILLED IN -- when the user clicks on the close button, it won't close?! It doesn't do anything?!

What (and where) should we add in the code so that "else if" it's NOT NULL and the fields are filled in, the close button should close the form?

Thanx!

 

[grnzbra]

How about if you take the code and put it in its own function that gives you ar return code, let's say NullCheck().

Then set up whatever buttons you have to call it as needed. So, for example, your close button would have it's on click event be something like,

...
If NullCheck() = 1 Then
DoCmd.Close
End If

 

[psemianonymous]

Really, I think you should (all) read the help file on Form_Unload(). You shouldn't do data validation there.


Pete

 

[jryan224]

Since there have been many suggestions for code change, to clarify, what is the exact code you have in the click of the close button?

 

[psemianonymous]

Just have it try to close the form the simplest way possible:

[tt]DoCmd.Close acForm, Me.Name, acSaveNo [/tt]

and the acSaveNo refers to form DESIGN saving, not form record saving. Assuming you have your BeforeUpdate() set up where it will cancel on a failed validation (i.e. if something is left empty, the BeforeUpdate event "Cancel" parameter is set = True). Assuming this, your form won't close.

If they have entered all appropriate data, then there won't be any message popped up, or anything.

This is the benefit of really using the event model perfectly--put all your data validation code in the Form_BeforeUpdate() event, and it won't matter how the user tries to close the form, or moves to the next record, or even shuts off the computer.

Anyway.

 

[psemianonymous]

OK, let me set this out. For data validation, do all checks inside Form_BeforeUpdate().

IF anything fails validation, cancel the update by using the line "Cancel = True". This will cancel either a new record being created or the editing of an existing record.

On the Form_Unload() event, put nothing there. Form_Unload() is triggered too late to catch anything anyway, at least inside a bound form.

On any close button you program in yourself, just close the form ( [tt]DoCmd.Close acForm, Me.Name, acSaveNo[/tt] ).

 

[LeiDBug007]

TO JDEMMER:

As requested, here is the exact code for the On Click procedure for the CLOSE button...

********************************************************
Private Sub Command13_Click()
Dim i As Integer
For i = 0 To Controls.Count - 1
If TypeOf Controls(i) Is TextBox Or _
TypeOf Controls(i) Is ComboBox Then
If IsNull(Controls(i)) Then
MsgBox "You must enter data in each field!"
Controls(i).SetFocus
End
End If
End If
Next i
End Sub
********************************************************

As noted before, it does work (per your advice), however... once all fields are filled in, when you click on the button it won't close?! The button doesn't do anything.

HELP! Thanx so much for your time on this!
~LeiDBug007

 

[psemianonymous]

Private Sub Command13_Click()
 Dim i As Integer
  For i = 0 To Controls.Count - 1
     If TypeOf Controls(i) Is TextBox Or _
       TypeOf Controls(i) Is ComboBox Then
          If IsNull(Controls(i)) Then
          MsgBox "You must enter data in each field!"
          Controls(i).SetFocus
          End
          End If
     End If
  Next i

  'note this addition
  DoCmd.Close acForm, Me.Name, acSaveNo
End Sub


But here's a way to get around this check: fill in a record half-way.  Then use the record navigation button to go back to a record that's completely filled out.  Then click the 'close' button.

Or press <CTRL>-<F4> to close the form.

So the code will work...most of the time.

Anyway, good luck.


Pete

 

[LeiDBug007]

Aloha Foolio12/Pete,

Your added string of code worked! Thanx so much for all the time you spent with this. All systems go!

~This is LeiDBug007, over and out...w/this problem that is..

P.S. In'eresting handle... ;-)