How to include variables in msgBox??

[ittechguy]

I have a small if statement I built here:

        If Nz(Me.cboEquipmentStatus, "") = "" _
        Or Nz(Me.cboEquipmentModelNum, "") = "" _
        Or Nz(Me.txtSerialNum, "") = "" Then
            MsgBox "Equipment Status, Model Number, and Serial Number cannot be empty!", vbOKOnly
                If Nz(Me.cboEquipmentStatus, "") = "" Then
                    Me.cboEquipmentStatus.SetFocus
                ElseIf Nz(Me.cboEquipmentModelNum, "") = "" Then
                    Me.cboEquipmentModelNum.SetFocus
                ElseIf Nz(Me.txtSerialNum, "") = "" Then
                    Me.txtSerialNum.SetFocus
                End If
                    Exit Sub
        Else
            If Me.cboEquipmentStatus.Value = 2 Then
                addStorage = True
            ElseIf Me.cboEquipmentStatus.Value = 1 Then
                addStorage = False
            End If
        End If

Basically it assigns a true/false value to a boolean variable based on the value of a combo box. If one of the 3 required boxes are null, it tells the user that the boxes cannot be null and then it sets focus to the box which is null.

Taking this a step further and also because I can use this knowledge in other areas of my database, I would like to know how to be able to tell the user "Equipment Status cannot be empty!" Or, "Model Number cannot be empty!" etc. etc.

...How is this done?

[ittechguy]

Thinking about this some more, I could use multiple msgboxes.

Like

if isnull(cboEquipmentStatus) Then
MsgBox "equipment status can't be blank."
ElseIf ... modelnum.. then
MsgBox "model number can't be blank."

The problem with that besides creating more code is that if 2-3 of the controls are blank then there would be 2-3 msgboxes. I only want one.

I.e. if equipment status and modelnum was blank it'd say "equipment status and model num is blank..."

[jforbes]

You could do something like this:

Dim sValidationText As String

' Build Validation Text
If Nz(Me.cboEquipmentStatus, "") = "" Then
    If Len(sValidationText) = 0 Then Me.cboEquipmentStatus.SetFocus
    sValidationText = sValidationText  & "Equipment Status needs to be selected." & vbCrLF
End If
If Nz(Me.cboEquipmentModelNum, "") = "" Then
    If Len(sValidationText) = 0 Then Me.cboEquipmentModelNum.SetFocus
    sValidationText = sValidationText  & "Equipment Model needs to be filled out." & vbCrLF
End If
If Nz(Me.txtSerialNum, "") = "" Then
    If Len(sValidationText) = 0 Then Me.txtSerialNum.SetFocus
    sValidationText = sValidationText  & "Serial Number needs to be filled out." & vbCrLF
End If

If Len(sValidationText) > 0 Then
   Call MsgBox(sValidationText)
Else
    addStorage = (Me.cboEquipmentStatus.Value = 2)
End If

[zmbd]

Here's a generic code block I use for unbound controls and forms. In bound forms I will set, at the table level, the field required property to true and let Access do the heavy lifting.

+ This makes use of the Tag property of the control by setting the value to the text you want displayed in the message.

+ There is an assumption here that only data-entry controls such as textbox, combobox, listbox, option buttons/groups, will have this information set.
++ one glitch is with the option button... it's always in the null state if unbound when the form loads. Unfortunately, this appears as a non-selected option which can confuse the user depending on use. I typically use this to force confirmation of action... ie, must select the option button allow the delete/save and then there's code to verify the state of the option control - I'll tend to use the toggle button in triple state in these cases as it's clearer to the end user that some action need be taken (I set the caption here to for user feed back :-) )

+ I have a modified version that handles control tags with multiple entries as I often use this property for other tasks. This is a modification of the hidden label method described bellow.
[{required(0/1),{CtrlName4Ms g/0},{other flags}]
Check Left(1) if not zero then parse the tag, Once again the Split() comes in handy here.

+ I typically use this method in a command button; however, I have had success using it in the form's before_update event. It really depends on the form's design.

+ I have also used a version where I use a hidden label control (typically I place this in the form footer) that has a comma-delimited list for the caption [{ControlName},{ UserText}...] that I pull into an array using the Split() and step thru. I have however, abandoned this method and as I find it in my older databases have replaced it with on of my tag property versions as several people have deleted this control off of forms by accident when adding/changing the design while I was on vacation - arrrghhhh.

+ You will see the use of the tabindex property here, the focus will be set to the first control in the tab order that has a missing entry. SO, take a few moments with the tabstop tool in the form design to logically order your data entry controls! Often the default is correct; however, adding and subtracting controls during design can really muck this order.

+ I've avoided NZ() here because I have had users go back and clear their entry; thus, the control is no-longer null.

Private Sub Command11_Click()
    Dim zCtrl As Control
    Dim zMsg As String
    Dim zTabIndex As Long
    Dim zCtrlname As String
    '
    'seed the message:
    zMsg = "The following controls are required entry: " & vbCrLf
    '
    'initialze the placeholder.
    zTabIndex = Me.Controls.Count
    For Each zCtrl In Me.Controls
        With zCtrl
            If Not "" & .Tag = "" Then
'            Debug.Print .Name, .Tag, .TabIndex, .Value
                If "" & zCtrl.Value = "" Then
                    zMsg = zMsg & .Tag & vbCrLf
                    If .TabIndex <= zTabIndex Then
                        zTabIndex = .TabIndex
                        zCtrlname = .Name
                    End If
                End If
            End If
        End With
    Next
    Debug.Print "setfocusto: " & zCtrlname
    If Not "" & zCtrlname = "" Then
        Me.Controls(zCtrlname).SetFocus
        MsgBox prompt:=zMsg, Buttons:=vbOKOnly, Title:="Missing data in required fields"
    End If
zcleanup:
Exit Sub
zerrtrap:
    MsgBox prompt:="Oh Bother, Contact the DBA and report the following information" & _
        vbCrLf & "errS: " & Err.Source & _
        vbCrLf & "errN: " & Err.Number & _
        vbCrLf & "errD: " & Err.Description, _
        Title:="Requred Dataentry Validation Failure"
    Resume zcleanup
End Sub

I've used this version dozens of times with a 90% success rate... typically the error was due to my mistake (accidentally setting the tag in a label :) ); however, I have occasionally had weird things occur that went away after the database was closed and re-opened.

[NeoPa]

I expect ZMBD's solution is a good one but my typical approach is similar to JForbes' one.

In your procedure you start with a validation string which is empty. Then you check each item in turn.

If the item is blank (TextBox controls are generally Null when empty rather than empty strings.) then :

1. If the validation string is empty then this is the first problem so you set the focus to this control.
2. Regardless of which is first you then add a reference to the control or field into the string in such a way as to form a list if there are multiple problems.

When all the items have been checked, if the validation string is empty then skip anything else.
Otherwise you can add the list between other strings to make up a message that makes sense and handles all of your problems and display that message in your (single) MsgBox().

[ittechguy]

Thanks for all the help guys! jforbes' method worked perfectly. I'll be using it for future forms as well.