By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,231 Members | 1,707 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,231 IT Pros & Developers. It's quick & easy.

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

doma23
100+
P: 107
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdWrite_Click()
  2.  
  3. Dim stDocName As String
  4. Dim stLinkCriteria As String
  5. Dim c As Control
  6. Dim check As Boolean
  7.  
  8. stDocName = "START_M"
  9. check = True
  10.  
  11. For Each c In Me.Controls
  12. If TypeOf c Is TextBox And Not IsNull(c.value) Then check = False
  13. Next
  14.  
  15. If check = False And funz_are_you_sure = "NO" Then Exit Sub 
  16. Else
  17. DoCmd.Close
  18. DoCmd.OpenForm stDocName, , , stLinkCriteria
Here comes the code of the function:

Expand|Select|Wrap|Line Numbers
  1. Public Function funz_are_you_sure() As String
  2. Dim Msg, Style, Title, Help, Ctxt, Response, MyString
  3. Msg = "Are you sure? You will lose all inserted data..."
  4. Style = vbYesNo + vbExclamation + vbDefaultButton2
  5. Title = "Cancelling confirm"
  6. Response = MsgBox(Msg, Style, Title)
  7. If Response = vbYes Then
  8.     funz_are_you_sure = "YES"
  9. Else
  10.     funz_are_you_sure = "NO"
  11. End If
  12. End Function
May 28 '10 #1

✓ answered by patjones

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

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. Dim booFilledBox As Boolean
  3.  
  4. For Each ctl In Me.Controls
  5.       If ctl.ControlType = acTextBox Then
  6.            If Not (IsNull(ctl.Value) Or ctl.Value ="") Then
  7.                 booFilledBox = True
  8.            End If
  9.       End If
  10. 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

Share this Question
Share on Google+
5 Replies


patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. Dim booFilledBox As Boolean
  3.  
  4. For Each ctl In Me.Controls
  5.      If ctl.ControlType = acTextBox And Not (IsNull(ctl.Value) Or ctl.Value ="") Then
  6.           booFilledBox = True
  7.      End If
  8. Next ctl 
  9.  
  10. 'Handle case for finding a filled text box...

Pat
May 28 '10 #2

doma23
100+
P: 107
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
May 28 '10 #3

patjones
Expert 100+
P: 931
It's because I forgot to nest the If tests...

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. Dim booFilledBox As Boolean
  3.  
  4. For Each ctl In Me.Controls
  5.       If ctl.ControlType = acTextBox Then
  6.            If Not (IsNull(ctl.Value) Or ctl.Value ="") Then
  7.                 booFilledBox = True
  8.            End If
  9.       End If
  10. 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
May 28 '10 #4

doma23
100+
P: 107
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!
May 28 '10 #5

patjones
Expert 100+
P: 931
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
May 28 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.