Len() function

tambadal

New Member
Joined
Oct 31, 2019
Messages
10
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim strMissingInfo As String
   Dim strMsg As String
    If IsNull(Me.[Debarred]) Then
       Cancel = True
       strMissingInfo = strMissingInfo & "Debarred"
        End If
    If IsNull(Me.[Restricted]) Then
       Cancel = True
       strMissingInfo = strMissingInfo & "Restricted"
    End If
    If IsNull(Me.[CommType]) Then
       Cancel = True
       strMissingInfo = strMissingInfo & "CommType"
    End If
    If IsNull(Me.[Approval_Status]) Then
       Cancel = True
       strMissingInfo = strMissingInfo & "Approval_Status"
       End If
    If Len(strMissingInfo) <> "" Then
       strMsg = "The following are required: " & strMissginInfo
       MsgBox strMsg
    End If
End Sub

I have this code to get the users to enter all required fields, and if it's not all filled out, the data cannot be saved and the message box will pop up. However, it's giving me error on the Len() function. Can you please look at the code to see where is the error in my code? Thank you!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Len returns a number. You cannot compare it to an empty string (aka zls or zero length string). Maybe you want
If Len(strMissingInfo) & "" = "" Then
or
If Len(strMissingInfo) = 0 Then
or
If Nz(strMissingInfo,"") = "" Then
If you have a lot of controls to validate, it's often done in a loop and all the names are concatenated as you are doing but yours won't be separated.
You'll get ABCD not A, B, C, D... or
- A
- B
- C
- D
 
Upvote 0
Curious, how would you loop?

One way is to make use of the tag property of the controls (in the example below this looks at controls with a tag of reqd.

Code:
For Each ctl In Me.Controls
    If ctl.Tag = "reqd" Then
        If Nz(ctl) = "" Then
            MsgBox ctl.Name & " is a required field, please complete before saving", vbOKOnly + vbInformation, "Data incomplete"
            Cancel = True
            Exit Sub
        End If
    End If
Next ctl
 
Upvote 0
Close to what I'd do, but not quite. That will prompt for each control, one at a time. So if you perform the action that would trigger this code and there a 6 missing values, you'll get 6 prompts. Also, I think it is better practice to be in the habit of always providing the valueIfNull argument when using Null rather than letting Access decide if it should be zero or "". Then there is the fact that often, control names are too cryptic for the user. If the textbox label is attached/associated then I would use its caption. To do that, consider that a control has a collection which only has one member, and that member is the label IF it is attached.

So I would Dim a string variable (Dim strMsg As String) and have
Code:
If Nz(ctl,"")="" Then strMsg = strMsg & ctl.Controls(0).Caption & vbCrLf & "  "
Next
If strMsg <> "" Then
 Msgbox "Please provide values for " & vbCrLf & strMsg
End If
You should get a message box comprised of a message beginning followed by an indented and complete list of label captions, but only if there are any required ones with missing values.
 
Last edited:
Upvote 0
Thanks. I agree with Micron on using the title (Textbox label) vs the control name in the msgbox. So would this need four If Statements inside this code to identify the Label? (four based on Tambadal is checking four fields.)
 
Upvote 0
No if it is within a loop - no if statement required.

the downside to this is if the control does not have an associated label it will error out. I tend to have meaningful control names and use in conjunction with set focus to go to the empty control - hence the exit function after finding the first null and associated message box.
 
Upvote 0
Thanks. I agree with Micron on using the title (Textbox label) vs the control name in the msgbox. So would this need four If Statements inside this code to identify the Label? (four based on Tambadal is checking four fields.)
ctl.Controls(0) IS the identifier. ctl is the control, Controls is its collection (which is 0 based), 0 is thus the first (and only) control in the collection As I noted, the only member is the label - it cannot be anything else.

I wouldn't call the fact that one either designs a form with labels attached or not a downside. Either you do or you don't, or the controls you want to include have or do not have attached labels. If not, then don't use it. That doesn't make the fact that you don't attache controls a downside of the approach. If in code you tried to reference a non existent control on a form would that mean your approach has a down side? Of course not. Code in a way that is conducive to the design. I made that distinction clear.

The very fact that you're first saying "if the control tag = something" then don't put the tag on a control that has no attached label, so no error for that reason. Quite simple. If you must do so, validate that the control has an attached label or trap the error. Regardless, the fact that I think having multiple prompts for the same thing or that using the label caption is better is just my opinion and it is what I would do regardless of whether I use the caption or control name, rather than validating one at a time. It should go without saying that you would then not do this on a control update or change event, but I'll say it anyway.
 
Upvote 0
Don't know why you seem so annoyed, to me it's a downside if not to you that's grand.

Have a nice day.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top