How to check if all the textboxes in a form are null

[doma23]

I have a form with multiple textboxes and 2 command buttons (Save, Cancel).
If user clicks the CANCEL button, I would like to check if all the textboxes on that form are null and in the case they are, I wan't form to close immediately.
If all the textboxes are not null, I want to ask user if he really wants to cancel and warn him that all entered data will be lost.

When I run the code, I receive the runtime error 438.
My code is something like this:

Private Sub cmdWrite_Click()
 
Dim stDocName As String
Dim stLinkCriteria As String
Dim c As Control
Dim check As Boolean
 
stDocName = "START_M"
check = True
 
For Each c In Me.Controls
If TypeOf c Is TextBox And Not IsNull(c.value) Then check = False
Next
 
If check = False And funz_are_you_sure = "NO" Then Exit Sub 
Else
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Here comes the code of the function:

Public Function funz_are_you_sure() As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure? You will lose all inserted data..."
Style = vbYesNo + vbExclamation + vbDefaultButton2
Title = "Cancelling confirm"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
    funz_are_you_sure = "YES"
Else
    funz_are_you_sure = "NO"
End If
End Function

[patjones]

What line are you getting the error on? You can check this by setting a breakpoint at the first executable line in the code and then stepping through it using F8.

Usually, when I do checks like this in my Access forms, I do something like this:

Dim ctl As Control
Dim booFilledBox As Boolean
 
For Each ctl In Me.Controls
     If ctl.ControlType = acTextBox And Not (IsNull(ctl.Value) Or ctl.Value ="") Then
          booFilledBox = True
     End If
Next ctl 
 
'Handle case for finding a filled text box...

Pat

[doma23]

Hi, tnx for answering.
I totally forgot to check the error with debugging.
I tried your code also and I get the same error number, it's on this line:

If ctl.ControlType = acTextBox And Not (IsNull(ctl.Value) Or ctl.Value ="") Then 

acTextBox value is "109" - I guess it's standard numeric value for text box
ctl is null

And that's it, I still don't understand.

[patjones]

It's because I forgot to nest the If tests...

Dim ctl As Control
Dim booFilledBox As Boolean
 
For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
           If Not (IsNull(ctl.Value) Or ctl.Value ="") Then
                booFilledBox = True
           End If
      End If
Next ctl

The problem with the way I wrote it out the first time is that Access evaluates the WHOLE line for each control. There are some controls that cannot have a value, and so ctl.Value has no meaning. As a result, we need to first check that the control is a text box (or some other control that can have a value, like a combo box) - then make the check to see if it's empty or not.

Pat

[doma23]

Wow. It works! :)
Interesting, the thing is that, as you can see, it also crossed my mind that not all controls have value property, so I added the code to check if the control is textbox, just as you did in your first code.
But I still don't understand completely, even if Access evaluates the whole line, our code included AND, so it would set boolean variable only in case both conditions are truth. So if the Access knows that one condition is not truth for sure (if it's not textbox), it shouldn't matter if it can't determine or process the second condition, becuase the boolean is in any case false.
Obviously, it does matter, but why?

UPDATE: Ooops, I think I kind of a answered myself. It's exactly beacuse of the AND that the machine looks at it as a WHOLE like you already pointed.

Well, thank you very much for your answer!

[patjones]

Well, the problem is that an expression which errors (such as ctl.Value for a ctl which cannot have a value) cannot be assigned a boolean status. So actually, the part after the AND has no boolean status and the entire line errors out.

Pat