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!
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
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
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
470
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
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
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:

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,678
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.)
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
470
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.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
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.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
470
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:

Forum statistics

Threads
1,077,895
Messages
5,337,054
Members
399,120
Latest member
Sravankumar

Some videos you may like

This Week's Hot Topics

Top