Len() function

[tambadal]

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]

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

 

[gheyman]

Curious, how would you loop?

 

[stumac]

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.

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]

ADVERTISEMENT

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

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.

 

[gheyman]

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]

ADVERTISEMENT

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.

 

[stumac]

*No additional if statements

 

[Micron]

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]

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.